pandas equivalent for R dcast

Question

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 1

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