CODEWITHSUNDEEP
pythonpandas
NBC
NBC

Reputation: 1698

How to pivot dataframe in a specific way

I've pivoted data many times in pandas, but never quite like this. Here's an example of the data I have:

id|a_1|b_1|a_2|b_2
x | 0 | 2 | 3 | 1
y | 1 | 0 | 1 | 4
z | 4 | 2 | 2 | 3

data = [['x',0,2,3,1],['y',1,0,1,4],['z',4,2,2,3]]
df = pd.DataFrame(data,columns=['id','a_1','b_1','a_2','b_2'])

I want to pivot such that the columns sync up, like so:

id|col|1|2|
x | a |0|3|
x | b |2|1|
y | a |1|1|
y | b |0|4|
z | a |4|2|
z | b |2|3|

Is this even possible? I was thinking of pivoting a_1,b_1 separately from a_2,b_2 and then merging them, but even then I'm not 100% how to do this

Upvotes: 2

Views: 32

Answers (1)

BENY
BENY

Reputation: 323326

After modify your columns , then the problem become to wide_to_long

df.columns=df.columns.str.split('_').map(lambda x : '_'.join([x[-1],x[0]]))
Yourdf=pd.wide_to_long(df,['1','2'],i='id_id',j='col',sep='_',suffix='\w+').reset_index()
Yourdf
Out[52]: 
  id_id col  1  2
0     x   a  0  3
1     y   a  1  1
2     z   a  4  2
3     x   b  2  1
4     y   b  0  4
5     z   b  2  3

Upvotes: 2

Related Questions