Python pandas concat to multi index groupby

Question

I'm new to pandas and I need help. I have two following reports which are quite simple.

$ cat test_report1
ID;TYPE;VAL
1;USD;5
2;EUR;10
3;PLN;3
$ cat test_report2
ID;TYPE;VAL
1;USD;5
2;EUR;10
3;PLN;1

Then I'm using concat to connect two reports with unique index:

A=pd.read_csv('test_report1', delimiter=';', index_col=False)
B=pd.read_csv('test_report2', delimiter=';', index_col=False)
C=pd.concat([A.set_index('ID'), B.set_index('ID')], axis=1, keys=['PRE','POST'])
print(C)

Which gives me following output:

          PRE     POST
         TYPE VAL TYPE VAL
ID
1         USD   5  USD   5
2         EUR  10  EUR  10
3         PLN   3  PLN   1

I find this pretty good but actually I would like rather to have:

     STATE TYPE VAL
ID
1         PRE  USD  5  
          POST USD  5
2         PRE  EUR  10
          POST EUR  10
3         PRE  PLN  3  
          POST PLN  1

Then it would be perfect with diff like:

         STATE TYPE VAL
ID
1         PRE  Nan  Nan
          POST Nan  Nan
2         PRE  Nan  Nan
          POST Nan  Nan
3         PRE  PLN  3  
          POST PLN  1

I know that this is doable but I'm stuck digging 3rd day to find a solution.

This might fix your problem: pandas.pydata.org/docs/reference/api/… — Swier
– Swier, Commented Feb 21, 2022 at 9:55

jezrael · Accepted Answer · 2022-02-21 09:56:03Z

1

Use DataFrame.rename_axis with DataFrame.stack and then sorting levels of MultiIndex:

df = (df.rename_axis(['STATE',None], axis=1)
        .stack(0)
        .sort_index(level=[0,1], ascending=[True, False])
        )
print (df)
         TYPE  VAL
ID STATE          
1  PRE    USD    5
   POST   USD    5
2  PRE    EUR   10
   POST   EUR   10
3  PRE    PLN    3
   POST   PLN    1

answered Feb 21, 2022 at 9:56

jezrael

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

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

Collectives™ on Stack Overflow

Python pandas concat to multi index groupby

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related