CODEWITHSUNDEEP
pythonpandasdataframe
Joe Emmens
Joe Emmens

Reputation: 147

Set "Year" column to individual columns to create a panel

I am trying to reshape the following dataframe such that it is in panel data form by moving the "Year" column such that each year is an individual column.

Out[34]: 
         Award Year    0
State                   
Alabama        2003   89
Alabama        2004   92
Alabama        2005  108
Alabama        2006   81
Alabama        2007   71
             ...  ...
Wyoming        2011    4
Wyoming        2012    2
Wyoming        2013    1
Wyoming        2014    4
Wyoming        2015    3
[648 rows x 2 columns]

I want the years to each be individual columns, this is an example,

Out[48]: 
        State  2003  2004  2005  2006
0     NewYork    10    10    10    10
1     Alabama    15    15    15    15
2  Washington    20    20    20    20

I have read up on stack/unstack but I don't think I want a multilevel index as a result. I have been looking through the documentation at to_frame etc. but I can't see what I am looking for.

If anyone can help that would be great!

Upvotes: 1

Views: 140

Answers (2)

AtanuCSE
AtanuCSE

Reputation: 8940

Pivot Table can help.

df2 = pd.pivot_table(df,values='0', columns='AwardYear', index=['State'])
df2

Result:

AwardYear   2003    2004    2005    2006    2007    2011    2012    2013    2014    2015
State                                       
Alabama     89.0    92.0    108.0   81.0    71.0    NaN      NaN     NaN     NaN    NaN
Wyoming     NaN      NaN    NaN      NaN    NaN     4.0     2.0     1.0      4.0    3.0

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71687

Use set_index with append=True then select the column 0 and use unstack to reshape:

df = df.set_index('Award Year', append=True)['0'].unstack()

Result:

Award Year  2003  2004   2005  2006  2007  2011  2012  2013  2014  2015
State                                                                  
Alabama     89.0  92.0  108.0  81.0  71.0   NaN   NaN   NaN   NaN   NaN
Wyoming      NaN   NaN    NaN   NaN   NaN   4.0   2.0   1.0   4.0   3.0

Upvotes: 2

Related Questions