Search code examples
pandasdataframetranspose

Transpose based on Company ID and Year

I have a dataset like this ``

df = pd.DataFrame({'ID': [1, 1, 1,1, 2,2, 2,2],
                   'Year': [2011, 2011, 2012,2012,2011,2011,2012,2012],
                   'Type': ["a","b","a","b","c/d","e","c/d","d"],
                   'Value': [10,11,12,11,13,14,14,15]})

   ID  Year Type  Value
0   1  2011    a     10
1   1  2011    b     11
2   1  2012    a     12
3   1  2012    b     11
4   2  2011  c/d     13
5   2  2011    e     14
6   2  2012  c/d     14
7   2  2012    d     15

Does there has any way to transform this data frame like this:

enter image description here

I tried transpose and reset index, but they did not generate what I want. Could some help in this? Thank you in advance!

Solution

  • You can use pivot_table:

    # or fill_value=''
>>> df.pivot_table(index=[df.index, 'Year'], columns='Type', values='Value', fill_value=0)

Type     a   b  c/d   d   e
  Year                     
0 2011  10   0    0   0   0
1 2011   0  11    0   0   0
2 2012  12   0    0   0   0
3 2012   0  11    0   0   0
4 2011   0   0   13   0   0
5 2011   0   0    0   0  14
6 2012   0   0   14   0   0
7 2012   0   0    0  15   0