How to transpose row and columns using pandas?

Question

I am new to pandas, I am getting a result in reverse order of my expected result.

What I have tried is:

o_rg,o_gg,a_rg,a_gg are arrays

    df1=pd.DataFrame({'RED':o_rg,'GREEN':o_gg})
df2=pd.DataFrame({'RED':a_rg,'RED':a_gg})
df=df1-(df2)
print(df)
pop_complete = pd.concat([df.T,
                          df1.T,
                          df2.T],
                          keys=["O-A", "O", "A"])
df = pop_complete.swaplevel()
df.sort_index(inplace=True)

print(df)
df.to_csv("OUT.CSV")

What I get the output as:

             0      1       2
RED        A        14.0    12.0    15.0
           O        14.0    12.0    15.0
           O-A      0.00    0.00    0.00
GREEN      A        12.0    10.0    12.0
           O        14.0    9.0     12.0
           O-A      -2.0    1.0     0.0

What I actually want is:

                    RED     GREEN       

        A1 O        14.0     14.0
           A        14.0     12.0
           O-A      0.0      2.0

        A3 O        12.0     9.0
           A        12.0     10.0
           O-A      0.0      -1.0

        A8 O        15.0     12.0
           A        15.0     12.0
           O-A      0.0      0.0

 where 'A1','A3','A8' ... can be stored in array cases=[]

How to get the actual output?

I think last edit is really complicated, please create new question. — jezrael
– jezrael, Commented Aug 26, 2019 at 12:54
@jezrael . Created new. How to get my expected output stackoverflow.com/questions/57667106/… — codeuser
– codeuser, Commented Aug 27, 2019 at 3:06

jezrael · Accepted Answer · 2019-08-22 12:34:47Z

2

You can simplify your solution with concat without transpose and axis=1, rename index values and then reshape by DataFrame.stack:

o_rg = [14,12,15]
o_gg = [14,9,12]

a_rg = [14,12,15] 
a_gg = [14,10,15]

df1=pd.DataFrame({'RED':o_rg,'GREEN':o_gg})
df2=pd.DataFrame({'RED':a_rg,'GREEN':a_gg})
df=df1-(df2)
print(df)
   RED  GREEN
0    0      0
1    0     -1
2    0     -3

pop_complete = pd.concat([df, df1, df2], keys=["O-A", "O", "A"], axis=1)
pop_complete.index = ['A1','A3','A8']
print(pop_complete)
   O-A         O         A      
   RED GREEN RED GREEN RED GREEN
A1   0     0  14    14  14    14
A3   0    -1  12     9  12    10
A8   0    -3  15    12  15    15

df1 = pop_complete.stack(0)[['RED','GREEN']].reindex(["O", "A", "O-A"], axis=0, level=1)
print (df1)
        RED  GREEN
A1 O     14     14
   A     14     14
   O-A    0      0
A3 O     12      9
   A     12     10
   O-A    0     -1
A8 O     15     12
   A     15     15
   O-A    0     -3

If need create file with no repeating first level of MultiIndex (not recommended) use this answer.

edited Aug 22, 2019 at 12:34

answered Aug 22, 2019 at 6:31

jezrael

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

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

22 Comments

codeuser Over a year ago

Thank you for the code. How can I fetch columns in 'RED' , 'GREEN' order?

jezrael Over a year ago

@itsbinz - use df1 = df1[['RED','GREEN']]

codeuser Over a year ago

How can I name index from a list ,the list may contain [A1,A3,A8],not in series order

jezrael Over a year ago

@itsbinz - remove .rename(lambda x: f'A{x+1}')) and use pop_complete.index = [A1,A3,A8]

jezrael Over a year ago

@itsbinz - you are right, added .reindex(["O", "A", "O-A"], axis=0, level=1) to last row of code.

|

Collectives™ on Stack Overflow

How to transpose row and columns using pandas?

1 Answer 1

22 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

22 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related