reshape pandas wide column to long

Question

I have a pandas df like below

tno,tdate,buyprice,sellprice,qty,t1,t2
1,2017,10,20,5,teamA,teamB
2,2017,5,10,5,teamB,teamA

Expected op:

tno,tdate,buyprice,sellprice,qty,t1,t2
1,2017,10,20,5,teamA,NaN
1,2017,10,20,5,NaN,teamB
2,2017,5,10,5,teamB,NaN
2,2017,5,10,5,NaN,TeamA

What is happening is I am separating an internal transaction between 2 different team into in 2 different transaction.

I tried using df.unstack() and also read this answer I am missing the way to tell pandas the way I want to unstack it.

Update1:

The larger context of the problem is:

tno,tdate,buyprice,sellprice,qty,Buyerteam,Sellerteam
1,2017,10,20,5,teamA,teamB
2,2017,5,10,5,teamB,teamA

There are 2 types of trades

Type1. where Buyerteam=NaN or Sellerteam=NaN (never both) and I calculate qty*(buyprice or sell price) to calculate expenditure of team. if buyTeam is NaN, I do qty*sellprice, if sellTeam=NaN I do qty*buyprice.
Type2. where both t1 and t2 are not NaN (the case in this question) I amtrying to convert type2 data into type1 data. But if I dont introduce NaN, my condition of qty*(buyprice or sellprice) cannot be applied

I hope my intent of introducing NaN is clear.

Sorry, I was offline. But I would like help you although better is create new question in this way. Can you change data sample with desired output for better explanation and better verifying my or another solutions? — jezrael, Jul 12 '17 at 11:03
The question was updated by me. Just that NaN is important for me. So expected o/p needs to be just as I have shown in the question — pythonRcpp, Jul 12 '17 at 11:23
Maybe we dont understand well. I think if is possible create sample data by 2 types of trades with desired output. — jezrael, Jul 12 '17 at 11:32
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/148997/discussion-between-pythonrcpp-and-jezrael). — pythonRcpp, Jul 12 '17 at 11:35

jezrael · Accepted Answer · 2017-07-12T12:58:47.280

If possible one output column for teams use lreshape:

df = pd.lreshape(df, {'t':['t1','t2']})
print (df)
   buyprice  sellprice  tdate  tno      t
0        10         20   2017    1  teamA
1         5         10   2017    2  teamB
2        10         20   2017    1  teamB
3         5         10   2017    2  teamA

EDIT: If only 2 teams is possible use concat with drop, last for same order of columns use reindex_axis and last sort_values:

df = pd.concat([df.drop('t2', axis=1), df.drop('t1', axis=1)], ignore_index=True)
       .reindex_axis(df.columns, 1)
       .sort_values(['tno','tdate'])
print (df)
   tno  tdate  buyprice  sellprice     t1     t2
0    1   2017        10         20  teamA    NaN
2    1   2017        10         20    NaN  teamB
1    2   2017         5         10  teamB    NaN
3    2   2017         5         10    NaN  teamA

reshape pandas wide column to long

1 Answers1