CODEWITHSUNDEEP
pythonpandaspivottranspose
amongo
amongo

Reputation: 61

Transpose or Pivot multiple columns in Pandas

I would like to transpose multiple columns in a dataframe. I have looked through most of the transpose and pivot pandas posts but could not get it to work.

Here is what my dataframe looks like.

df = pd.DataFrame()
df['L0'] = ['fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'vegetable', 'vegetable', 'vegetable', 'vegetable', 'vegetable', 'vegetable']
df['L1'] = ['apple', 'apple', 'apple', 'banana', 'banana', 'banana', 'tomato', 'tomato', 'tomato', 'lettuce', 'lettuce', 'lettuce']
df['Type'] = ['X', 'Y', 'Z', 'X', 'Y', 'Z', 'X', 'Y', 'Z', 'X', 'Y', 'Z']
df['A'] = [3, 0, 4, 3, 1, 3, 2, 2, 2, 4, 2, 4]
df['B'] = [3, 1, 0, 4, 1, 4, 4, 4, 2, 1, 2, 1]
df['C'] = [0, 4, 1, 0, 2, 4, 1, 1, 2, 3, 2, 3]

I would like to transpose/pivot columns A, B and C and replace them with values from column "Type". Resulting dataframe should look like this.

df2 = pd.DataFrame()
df2['L0'] = ['fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'fruit', 'vegetable', 'vegetable', 'vegetable', 'vegetable', 'vegetable', 'vegetable']
df2['L1'] = ['apple', 'apple', 'apple', 'banana', 'banana', 'banana', 'tomato', 'tomato', 'tomato', 'lettuce', 'lettuce', 'lettuce']
df2['Type2'] = ['A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'C']
df2['X'] = [3, 3, 0, 3, 4, 0, 2, 4, 1, 4, 1, 3]
df2['Y'] = [0, 1, 4, 1, 1, 2, 2, 4, 1, 2, 2, 2]
df2['Z'] = [4, 0, 1, 3, 4, 4, 2, 2, 2, 4, 1, 3]

The best I could do was this

df.groupby(['L0', 'L1', 'Type'])['A', 'B', 'C'].sum().unstack('Type')

But this is not really what I want. Thank you!

Upvotes: 2

Views: 793

Answers (1)

jezrael
jezrael

Reputation: 862511

Add stack before unstack:

df = (df.groupby(['L0', 'L1', 'Type'])['A', 'B', 'C']
        .sum()
        .stack()
        .unstack('Type')
        .reset_index()
        .rename_axis(None, axis=1)
        .rename(columns={'level_2':'Type2'}))
print (df)
           L0       L1 Type2  X  Y  Z
0       fruit    apple     A  3  0  4
1       fruit    apple     B  3  1  0
2       fruit    apple     C  0  4  1
3       fruit   banana     A  3  1  3
4       fruit   banana     B  4  1  4
5       fruit   banana     C  0  2  4
6   vegetable  lettuce     A  4  2  4
7   vegetable  lettuce     B  1  2  1
8   vegetable  lettuce     C  3  2  3
9   vegetable   tomato     A  2  2  2
10  vegetable   tomato     B  4  4  2
11  vegetable   tomato     C  1  1  2

Upvotes: 2

Related Questions