how to pivot columns to index and rows to column-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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