CODEWITHSUNDEEP
pandasdataframe
Whisht
Whisht

Reputation: 753

Pandas reshape Dataframe based on column value

I have a Dataframe df = pd.Dataframe({'year':[2001,2002,2001,2002,2003],'1':[36984,36559,12927,12414,9731],'2':[28384，33467，11677，11258，8407],'State':["Alabama","Alabama","Alaska","Alaska","Alaska"]}) like this:

year    1         2      State
2001    36984   28384   Alabama
2002    36559   33467   Alabama
2001    12927   11677   Alaska
2002    12414   11258   Alaska
2003    9731    8407    Alaska

. Now I want to organize this df to one column group by State, like this:

year-month  Alabama Alaska
2001-1      36984   12927
2001-2      28384   11677
2002-1      36559   12414
2002-2      33467   11258   
2003-1      NaN     9371
2003-2      NaN     8407

How to achieve this? Thanks.

Upvotes: 2

Views: 226

Answers (2)

Anurag Dabas
Anurag Dabas

Reputation: 24314

Another way:

out=(df.groupby(['State','year'])
       .first()
        .unstack(1)
        .swaplevel(axis=1)
        .T
        .rename_axis(columns='year-month'))
out.index=out.index.map(lambda x:'-'.join(map(str,x)))

output of out:

year-month  Alabama     Alaska
2001-1      36984.0     12927.0
2002-1      36559.0     12414.0
2003-1      NaN         9731.0
2001-2      28384.0     11677.0
2002-2      33467.0     11258.0
2003-2      NaN         8407.0

Upvotes: 3

jezrael
jezrael

Reputation: 862511

Use DataFrame.melt with join columns and then DataFrame.pivot:

df1 = df.melt(['year','State'])
df1['year-month'] = df1['year'].astype(str) + '-' + df1['variable'].astype(str)

df1 = df1.pivot('year-month','State','value')
print (df1)
State       Alabama   Alaska
year-month                  
2001-1      36984.0  12927.0
2001-2      28384.0  11677.0
2002-1      36559.0  12414.0
2002-2      33467.0  11258.0
2003-1          NaN   9731.0
2003-2          NaN   8407.0

Upvotes: 3

Related Questions