How to unstack dataframe

Question

I have a pandas df with the columns "Item","Year","Value" which has 708 rows. I want to reshape this into a df with 59 rows and 12 columns. This is what it looks like now (simplified):

Item, Year, Value
Apple, 2001, 5
Apple, 2002, 4
Apple, 2003, 7
Apple, 2004, 2
Orange, 2001, 1
Orange, 2002, 5
Orange, 2003, 2
Orange, 2004, 3

And the result I need is:

  Year, Apple, Orange
    2001, 5, 1
    2002, 4, 5
    2003, 7, 2
    2004, 2, 3

How can I go about achieving this? Thank you!

score 1 · Accepted Answer · answered May 24 '21 at 17:10

You just need to set Year and Item to be the index first and then it's pretty easy from there:

In [331]: df.set_index(["Year", "Item"]).unstack()['Value']
Out[331]:
Item  Apple  Orange
Year
2001      5       1
2002      4       5
2003      7       2
2004      2       3

score 1 · Answer 2 · answered May 24 '21 at 17:14

print(df.pivot(index="Year", columns="Item", values="Value"))

Prints:

Item  Apple  Orange
Year               
2001      5       1
2002      4       5
2003      7       2
2004      2       3

To save as CSV:

x = df.pivot(index="Year", columns="Item", values="Value").reset_index()
x.to_csv("data.csv", index=False)

Saves data.csv:

Year,Apple,Orange
2001,5,1
2002,4,5
2003,7,2
2004,2,3

How to unstack dataframe

2 Answers2