# How to pivot and unpivot a table

_This page shows you how to reshape a q table from long form to wide form and back again, and how to handle missing and duplicate values._

A _pivot_ turns the distinct values in one column into new columns. An _unpivot_ turns those columns back into rows. q has no `pivot` keyword, but [dictionaries](../basics/data_structures/dictionaries.md) and qSQL make both operations concise.

## Example data

The table has a row key `k`, a pivot column `p`, and a value column `v`:


**q**
```q
q)show t:([]k:1 2 3 2 3; p:`xx`yy`zz`xx`yy; v:10 20 30 40 50)
k p  v
-------
1 xx 10
2 yy 20
3 zz 30
2 xx 40
3 yy 50
```


**pandas**
```python
>>> t = pd.DataFrame({'k': [1, 2, 3, 2, 3], 'p': ['xx', 'yy', 'zz', 'xx', 'yy'], 'v': [10, 20, 30, 40, 50]})
```


The pair `k,p` is unique. This matters because each cell in the pivoted result can hold only one value. See [Aggregate duplicate cells](#aggregate-duplicate-cells) if the pair isn't unique.

## Pivot to wide form

First get the distinct values of `p`. They must be symbols because they become column names. Sorting them makes the output column order deterministic:
```q
q)P:asc exec distinct p from t
q)P
`s#`xx`yy`zz
```

For each value of `k`, `p!v` creates a dictionary from the pivot values present in that group to their values. [Take](../../ref/take.md#dictionary) (`P#`) then picks the full set of keys, inserting a null where a combination is missing:


**q**
```q
q)show wide:exec P#(p!v) by k:k from t
k| xx yy zz
-| --------
1| 10
2| 40 20
3|    50 30
```


**pandas**
```python
>>> wide = t.pivot(index='k', columns='p', values='v')
>>> wide
p    xx    yy    zz
k
1  10.0   NaN   NaN
2  40.0  20.0   NaN
3   NaN  50.0  30.0
```


The new columns keep the type of `v`, here `long`, and a missing cell holds the long null `0N`. pandas has no integer null by default, so it converts the columns to floats to hold `NaN`.

The result is keyed by `k`. To make it an unkeyed table, [unkey it with `0!`](../../ref/enkey.md#unkey):
```q
q)0!wide
k xx yy zz
----------
1 10
2 40 20
3    50 30
```

> **Note — Missing values and nulls**
>
>
> A null in the wide result can mean either that the `k,p` combination was absent or that its original `v` was null. A pivot can't preserve that distinction without an additional presence indicator.
>
### Aggregate duplicate cells

If several rows have the same key and pivot value, aggregate them before pivoting. For example, add a second value for the pair `` k=2,p=`xx ``:
```q
q)show d:t,([]k:enlist 2; p:enlist `xx; v:enlist 5)
k p  v
-------
1 xx 10
2 yy 20
3 zz 30
2 xx 40
3 yy 50
2 xx 5
```

> **Warning — A duplicate cell is dropped without an error**
>
>
> A dictionary lookup returns the value of the first matching key, so the pivot keeps `40` for `` k=2,p=`xx `` and silently ignores `5`:
>
> ```q
> q)exec P#(p!v) by k:k from d
> k| xx yy zz
> -| --------
> 1| 10
> 2| 40 20
> 3|    50 30
> ```
>
> pandas `pivot` raises `ValueError: Index contains duplicate entries` instead. Check that `k,p` is unique, or aggregate first.
>
Aggregate each `k,p` group with a `select … by` query, then pivot its result:


**q**
```q
q)exec P#(p!v) by k:k from select sum v by k,p from d
k| xx yy zz
-| --------
1| 10
2| 45 20
3|    50 30
```


**pandas**
```python
>>> d.pivot_table(index='k', columns='p', values='v', aggfunc='sum')
p    xx    yy    zz
k
1  10.0   NaN   NaN
2  45.0  20.0   NaN
3   NaN  50.0  30.0
```


Use an aggregation that fits the data, such as `sum`, `avg`, `first`, or `last`. For more on grouping, see [Group rows](select.md#group-rows).

## Unpivot to long form

The following function keeps `baseCols` and turns every column in `pivotCols` into a pair of columns named `nameCol` and `valueCol`. The definition spans several lines, so it belongs in a script; see [multiline expressions](../basics/use-scripts.md#multiline-expressions):
```q
unpivot:{[tab;baseCols;pivotCols;nameCol;valueCol]
 baseCols:(),baseCols;
 pivotCols:(),pivotCols;
 base:?[tab;();0b;baseCols!baseCols];
 newCols:{[n;v;t;p]flip(n;v)!(count[t]#p;t p)}[nameCol;valueCol;tab]each pivotCols;
 baseCols xasc raze {[b;n]b,'n}[base]each newCols
 }
```

Unkey `wide` and pass the pivot column names in the order you want them to appear:


**q**
```q
q)show long:unpivot[0!wide;`k;P;`p;`v]
k p  v
-------
1 xx 10
1 yy
1 zz
2 xx 40
2 yy 20
2 zz
3 xx
3 yy 50
3 zz 30
```


**pandas**
```python
>>> long = (wide.reset_index()
...             .melt(id_vars='k', var_name='p', value_name='v')
...             .sort_values(['k', 'p'], ignore_index=True))
>>> long
   k   p     v
0  1  xx  10.0
1  1  yy   NaN
2  1  zz   NaN
3  2  xx  40.0
4  2  yy  20.0
5  2  zz   NaN
6  3  xx   NaN
7  3  yy  50.0
8  3  zz  30.0
```


The pivot created rows for combinations that weren't in `t`, with null values. If a null means that the combination was absent from your data, delete those rows:


**q**
```q
q)show restored:`k`p xasc delete from long where null v
k p  v
-------
1 xx 10
2 xx 40
2 yy 20
3 yy 50
3 zz 30
q)restored~`k`p xasc t
1b
```


**pandas**
```python
>>> long.dropna(subset='v').reset_index(drop=True)
   k   p     v
0  1  xx  10.0
1  2  xx  40.0
2  2  yy  20.0
3  3  yy  50.0
4  3  zz  30.0
```


Keep the null rows instead if they represent meaningful cells in the wide table.

## Next steps

- Learn how [dictionaries](../basics/data_structures/dictionaries.md) work, including the `p!v` and `P#` operations that the pivot uses.
- Use [functional qSQL](../../ref/funsql.md) to build a reusable pivot whose key, pivot, and value column names are arguments.
