CODEWITHSUNDEEP
pythonpandas
Stacey
Stacey

Reputation: 5107

Transpose column data from one dataframe to another

I have a dataframe df1 where the head looks like (the actual dataframe is bigger):

     Quarter    Body    Total requests  Requests Processed  
  0  Q3 2019       A                93                  92  
  1  Q3 2019       B               228                 210  
  2  Q3 2019       C               180                 178  
  3  Q3 2019       D                31                  31  
  4  Q3 2019       E               555                 483  
  5  Q2 2019       A                50                  50  
  6  Q2 2019       B               191                 177  
  7  Q2 2019       C               186                 185  
  8  Q2 2019       D                35                  35  
  9  Q2 2019       E               344                 297

I have another dataframe df2 which has is currently set up as follows:

 Body   Q2 2019 Q3 2019
0   A   
1   B   
2   C   
3   D   
4   E

I am trying to find a way to transpose the column Total requests from df1 into df2 so my desired output looks like:

  Body  Q2 2019 Q3 2019
0    A       50      93
1    B      191     228
2    C      186     180
3    D       35      31
4    E      344     555

I have had a good look around but can't find without success.

Upvotes: 1

Views: 34

Answers (1)

anky
anky

Reputation: 75100

You can unstack the df1 post setting Body and Quarter as index , then merge with df2 (if using df2 is important else it works without the merge to):

df2.merge(df1.set_index(['Body','Quarter'])['Total requests'].unstack(),
      left_on='Body',right_index=True,how='left',
      suffixes=('_x','')).reindex(df2.columns,axis=1)

  Body  Q2 2019  Q3 2019
0    A       50       93
1    B      191      228
2    C      186      180
3    D       35       31
4    E      344      555

Upvotes: 3

Related Questions