how to pivot columns to index and rows to column

Question

I have the following dfs

a = {"A":[123],"B":[19],"C":[18],"J":[220],'name':['group_1']}
b = {"A":[123],"B":[80],"C":[10],"D":[3],'name':['group_2']}
df1 = pd.DataFrame(a)
df2 = pd.DataFrame(b)

when appending them I get:

df = df1.append(df2).fillna(0).reset_index(drop=True)

      A B   C   D       J       name
0   123 19  18  0.00    220.00  group_1
1   123 80  10  3.00    0.00    group_2

I wish to have this:

      group_1    group_2
A     123        123
B     19         80
C     18         10
D     0          3
J     220        0

I was hoping either melt or pivot_table will do the trick but the results are not as expected. Exmaple: df.pivot_table(index=['A','B','C','D','J'], columns=['name'], aggfunc='count') will not show the 'name' (group_1/2) as columns.

BENY · Accepted Answer · 2021-09-23 13:53:00Z

1

You can try

df1.append(df2).fillna(0).reset_index(drop=True).set_index('name').T
Out[284]: 
name  group_1  group_2
A       123.0    123.0
B        19.0     80.0
C        18.0     10.0
J       220.0      0.0
D         0.0      3.0

answered Sep 23, 2021 at 13:53

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

ProcolHarum Over a year ago

Thanks - I'm amazed how you make it so simple ;-)

BENY Over a year ago

@ProcolHarum Glad I can help ~ good day ~

mozway · Accepted Answer · 2021-09-23 13:57:47Z

1

I would have done it similarly to @BENY, but using pandas.concat to be a bit more generic, and filling the values after setting the index to benefit from the type downcasting to int:

pd.concat([df1, df2]).set_index('name').fillna(0, downcast='infer').T

output:

name  group_1  group_2
A         123      123
B          19       80
C          18       10
J         220        0
D           0        3

answered Sep 23, 2021 at 13:57

mozway

267k13 gold badges56 silver badges106 bronze badges

2 Comments

ProcolHarum Over a year ago

thanks! I learned another good trick!

mozway Over a year ago

But please consider accepting @BENY's answer if it fits your needs, got here first ;)

Collectives™ on Stack Overflow

how to pivot columns to index and rows to column

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related