CODEWITHSUNDEEP
pythonpandasdataframe
Vaibhav Rathi
Vaibhav Rathi

Reputation: 350

How to transform rows into column in Pandas Dataframe

I have a dataframe in the format below:

DF1: Shape of Original dataframe is (1200, 9)

This is Sample dataframe

    x1      x2      x3     Group

    1.0     0.0     0.0     A
    0.0     0.0     0.0     A
    0.0     3.0     11.0    A
    0.0     0.0     0.0     A
    0.0     1.0     0.0     A
    0.0     0.0     0.0     E
    0.0     0.0     0.0     E
    0.0     0.0     0.0     E
    0.0     0.0     6.0     E
    0.0     0.0     0.0     E

I want the output in the following format:

DF_Res:

Group    A    E
   x1  1.0  0.0
   x2  0.0  0.0
   x3  0.0  0.0

   x1  0.0  0.0
   x2  0.0  0.0
   x3  0.0  0.0

   x1  0.0  0.0
   x2  3.0  0.0
   x3  11.0  0.0

   x1  0.0  0.0
   x2  0.0  0.0
   x3  0.0  6.0

   x1  0.0  0.0
   x2  1.0  0.0
   x3  0.0  0.0

I want to transform my columns so that they become group and Group become column header.

Please help.

Thanks

Upvotes: 1

Views: 125

Answers (2)

jezrael
jezrael

Reputation: 862611

If want aggregate values, e.g. by sum per groups:

df1 = df.groupby('Group').sum().T.rename_axis(None, axis=1).rename_axis('Group').reset_index()
print (df1)
  Group    A    E
0    x1  0.0  0.0
1    x2  0.0  0.0
2    x3  0.0  0.0
3    x4  0.0  0.0
4    x5  0.0  0.0
5    x6  0.0  0.0
6    x7  0.0  0.0
7    x8  0.0  0.0

EDIT:

df2 = df.set_index('Group').T.rename_axis(None, axis=1).rename_axis('Group').reset_index()
print (df2)
  Group    A    A    E    E    A
0    x1  0.0  0.0  0.0  0.0  0.0
1    x2  0.0  0.0  0.0  0.0  0.0
2    x3  0.0  0.0  0.0  0.0  0.0
3    x4  0.0  0.0  0.0  0.0  0.0
4    x5  0.0  0.0  0.0  0.0  0.0
5    x6  0.0  0.0  0.0  0.0  0.0
6    x7  0.0  0.0  0.0  0.0  0.0
7    x8  0.0  0.0  0.0  0.0  0.0

EDIT1:

df = (df.set_index('Group')
        .groupby(level=0)
        .apply(lambda x: x.stack().reset_index(level=0, drop=True))
        .rename_axis(None)
        .rename_axis('Group', axis=1)
        .T
        .reset_index())

print (df)
   Group     A    E
0     x1   1.0  0.0
1     x2   0.0  0.0
2     x3   0.0  0.0
3     x1   0.0  0.0
4     x2   0.0  0.0
5     x3   0.0  0.0
6     x1   0.0  0.0
7     x2   3.0  0.0
8     x3  11.0  0.0
9     x1   0.0  0.0
10    x2   0.0  0.0
11    x3   0.0  6.0
12    x1   0.0  0.0
13    x2   1.0  0.0
14    x3   0.0  0.0

Upvotes: 4

busybear
busybear

Reputation: 10590

This is bit "hacky", but you need to create a separate index to differentiate your values. For example, multiple values correspond to A and x1. Here's what I'm talking about:

df_new = df.set_index('Group')
df_new = df_new.groupby(df_new.index, as_index=False).apply(lambda x: x.stack().reset_index())
df_new.columns = ['Group', 'x', 'value']
df_new = df_new.droplevel(axis=0, level=0).set_index(['Group', 'x'], append=True).unstack('Group').droplevel(axis=1, level=0)

Result:

Group     A    E
x               
x1      1.0  0.0
x2      0.0  0.0
x3      0.0  0.0
x1      0.0  0.0
x2      0.0  0.0
x3      0.0  0.0
x1      0.0  0.0
x2      3.0  0.0
x3     11.0  0.0
x1      0.0  0.0
x2      0.0  0.0
x3      0.0  6.0
x1      0.0  0.0
x2      1.0  0.0
x3      0.0  0.0

Upvotes: 1

Related Questions