Pivot Table in Pandas DataFrame - Make rows to columns

Question

So I have a dataframe where the state is the US state codes and the year columns have values for that particular state, the data frame has values for all the states, is there a way I can pivot so that there is an ""Year"" column for all the years and values are in just one column.

My current dataframe :

STATE YEAR2021 YEAR2022 YEAR2023 ....
XX        XX       XX       XX

Desired DataFrame:

STATE YEAR VALUE
XX    2021  XX
XX    2022  XX
XX    2023  XX

How do I achieve this in python?

mozway · Accepted Answer · 2021-08-10 19:55:19Z

1

You can use the following:

df2 = (df.set_index('STATE')
         .stack()
         .reset_index()
         .rename(columns={'level_1': 'YEAR',
                          0: 'VALUE'})
      )
df2['YEAR'] = df2['YEAR'].str[4:]

input:

  STATE YEAR2021 YEAR2022 YEAR2023
0    AA       XX       YY       ZZ

output:

  STATE  YEAR VALUE
0    AA  2021    XX
1    AA  2022    YY
2    AA  2023    ZZ

edited Aug 10, 2021 at 19:55

answered Aug 10, 2021 at 19:49

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suyash Over a year ago

works like a charm, I would dissect it later but if you can you explain it would be great.

mozway Over a year ago

I suggest you run each step in the pipeline one by one to see the progression. To summarize the key step is stack that moves columns to rows, the rest is accessory to it.

Collectives™ on Stack Overflow

Pivot Table in Pandas DataFrame - Make rows to columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related