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 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)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
>>> 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 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)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 (P#) then picks the full set of keys, inserting a null where a combination is missing:
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
>>> 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!:
q)0!wide
k xx yy zz
----------
1 10
2 40 20
3 50 30
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)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
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)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)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
>>> 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.
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:
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)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
>>> 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)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
>>> 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 work, including the
p!vandP#operations that the pivot uses. - Use functional qSQL to build a reusable pivot whose key, pivot, and value column names are arguments.
- Work through the pivot table exercise in Q for Mortals, which generalizes the pivot to multiple keys, non-symbol pivot values, filters, and custom aggregations.