How to transform rows into column in Pandas Dataframe

Question

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

That won't work. Shape of dataframe is 1200,9. Transpose gives me 1200 columns. — Vaibhav Rathi
– Vaibhav Rathi, Commented Oct 17, 2019 at 11:55
Original values are different. Here, 0 is for a sample dataframe. Original dataframe is made of different float values. — Vaibhav Rathi
– Vaibhav Rathi, Commented Oct 17, 2019 at 11:57
How are you handling the multiple rows with that have Group == 'A'? Not very clear from your post. They need to aggregated in some way, but sounds like you don't want to aggregate them? — busybear
– busybear, Commented Oct 17, 2019 at 12:08
@busybear, I don't want to aggregate them, i want to run a combination of functions on each group (x1, x2, x3... x8). So, when i have achieved the desired dataframe. I wish to use groupby apply on the dataframe — Vaibhav Rathi
– Vaibhav Rathi, Commented Oct 17, 2019 at 12:13

jezrael · Accepted Answer · 2019-10-17 12:58:27Z

4

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

edited Oct 17, 2019 at 12:58

answered Oct 17, 2019 at 11:57

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

6 Comments

Vaibhav Rathi Over a year ago

Thanks @jezrael, But i don't want aggregate values. I want to transform the dataframe.

jezrael Over a year ago

@VaibhavRathi - hmmm, so do you need only transpose like in second solution?

Vaibhav Rathi Over a year ago

i have made an edit to the question. Please refer that. I am sorry for the confusion.

jezrael Over a year ago

@VaibhavRathi - I still not understand, mainly because only 0 values. I suggest change sample data - only 2-3 x columns and add some non 0 values with expcted output.

Vaibhav Rathi Over a year ago

I have made changes as suggested by you. Kindly have a look now.

|

busybear · Accepted Answer · 2019-10-17 12:46:49Z

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

Collectives™ on Stack Overflow

How to transform rows into column in Pandas Dataframe

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related