0

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  value   State
2001-1      36984   Alabama
2001-1      12927   Alaska
2001-2      28384   Alabama
2001-2      11677   Alaska
2002-1      36559   Alabama
2002-1      12414   Alaska
2002-2      33467   Alabama
2002-2      11258   Alaska
2003-1      9731    Alaska
2003-2      8407    Alaska

How can I do this? Thanks!

I wonder how to get a simliar dataframe 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
Whisht
  • 681
  • 2
  • 6
  • 20

1 Answers1

1

Here you go.

code:

df1 = pd.melt(df, id_vars=['year','State'])
df1['year-month'] = df1['year'].astype(str) + '-' + df1['variable']
df1 = df1[['year-month','value','State']].sort_values(by='year-month')
print(df1)

Prints:

  year-month  value    State
0     2001-1  36984  Alabama
2     2001-1  12927   Alaska
5     2001-2  28384  Alabama
7     2001-2  11677   Alaska
1     2002-1  36559  Alabama
3     2002-1  12414   Alaska
6     2002-2  33467  Alabama
8     2002-2  11258   Alaska
4     2003-1   9731   Alaska
9     2003-2   8407   Alaska
sharathnatraj
  • 1,614
  • 5
  • 14