Rearrange python pandas dataframe (partially transposing)

Question

I have the following pandas dataframe (index(es) ignored):

Account	Currency	Side	Quote1	Broker1	Quote2	Broker2	Quote3	Broker3
Acc1	EUR/USD	BUY	1.218	Brk3	1.216	Brk6	NaN	NaN
Acc2	GBP/USD	SELL	1.345	Brk6	1.330	Brk5	1.341	Brk3

python representation of dataframe:

data = {
"Account":["Acc1","Acc2"],"Currency":["EUR/USD","GBP/USD"],"Side":["BUY","SELL"],
"Quote1":[1.218,1.345],"Broker1":["Brk3","Brk6"],
"Quote2":[1.216,1.330],"Broker2":["Brk6","Brk5"],
"Quote3":[np.nan,1.341],"Broker3":[np.nan,"Brk3"]
}

I want to rearrange the above dataframe to obtain the following dataframe:

| Account | Currency | Side | Quote | Broker | 
|---------|----------|------|-------|--------|
|Acc1     |EUR/USD   |BUY   |1.218  |Brk3    |
|Acc1     |EUR/USD   |BUY   |1.216  |Brk6    |
|Acc2     |GBP/USD   |SELL  |1.345  |Brk6    |
|Acc2     |GBP/USD   |SELL  |1.330  |Brk5    |
|Acc2     |GBP/USD   |SELL  |1.341  |Brk3    |

It is important to note, that the columns which are to be transposed (Quote1, Broker1, Quote2, Broker2, Quote3, Broker3), should be transposed only if they do not contain NaNs.

I am fairly new to pandas so a tried a bit with multi indexes and pivots using other threads I found here, but I kept failing at the point where empty cells (like Quote3 and Broker3 of line 1 in dataframe 1) are to be ignored. It would be great, if you guys could help!

Cheers

PeterLustig20

Welcome to Stackoverflow. Great first question and good example of how to ask well formed pandas question. — Shubham Sharma, Dec 22 '20 at 15:36

score 3 · Accepted Answer · answered Dec 22 '20 at 15:23

You can use wide_to_long:

pd.wide_to_long(df, ['Quote', 'Broker'],
                ['Account', 'Currency', 'Side'], 'j', suffix=r'\d+')\
  .dropna(how='all').droplevel(-1).reset_index()

Result:

  Account Currency  Side  Quote Broker
0    Acc1  EUR/USD   BUY  1.218   Brk3
1    Acc1  EUR/USD   BUY  1.216   Brk6
2    Acc2  GBP/USD  SELL  1.345   Brk6
3    Acc2  GBP/USD  SELL  1.330   Brk5
4    Acc2  GBP/USD  SELL  1.341   Brk3

Rearrange python pandas dataframe (partially transposing)

1 Answers1