R Question
pandas equivalent for R dcast
I have some data like this:
import pandas as pd
df = pd.DataFrame(index = range(1,13), columns=['school', 'year', 'metric', 'values'], )
df['school'] = ['id1']*6 + ['id2']*6
df['year'] = (['2015']*3 + ['2016']*3)*2
df['metric'] = ['tuition', 'admitsize', 'avgfinaid'] * 4
df['values'] = range(1,13)
df
school year metric values
1 id1 2015 tuition 1
2 id1 2015 admitsize 2
3 id1 2015 avgfinaid 3
4 id1 2016 tuition 4
5 id1 2016 admitsize 5
6 id1 2016 avgfinaid 6
7 id2 2015 tuition 7
8 id2 2015 admitsize 8
9 id2 2015 avgfinaid 9
10 id2 2016 tuition 10
11 id2 2016 admitsize 11
12 id2 2016 avgfinaid 12
I would like to pivot the metric & values columns to wide format. That is, I want:
school year tuition admitsize avgfinaid
id1 2015 1 2 3
id1 2016 4 5 6
id2 2015 7 8 9
id2 2016 10 11 12
if this were R, I would do something like:
df2 <- dcast(df, id + year ~ metric, value.var = "values")
How do I do this in pandas? I have read this (otherwise very helpful) SO answer and this (also otherwise excellent) example in the pandas docs, but did not grok how to apply it to my needs. I do not need a one-liner like dcast, just an example of how to get the result in a standard DataFrame (not a groupby, multi-index, or other fancy object).
Answer
you can use pivot_table():
In [23]: df2 = (df.pivot_table(index=['school', 'year'], columns='metric',
....: values='values')
....: .reset_index()
....: )
In [24]:
In [24]: df2
Out[24]:
metric school year admitsize avgfinaid tuition
0 id1 2015 2 3 1
1 id1 2016 5 6 4
2 id2 2015 8 9 7
3 id2 2016 11 12 10
Source (Stackoverflow)