Transpose dataframe in Python pandas

Question

I have a dataframe like this

Input

>>> df

 sent      cite 
 a         1,2,3
 b         2,4
 c         5

I want to transpose the dataframe that each row contains the values of cite and sent combined. Notice that the cite and the sent can be repeated (e.g, cite 2)

Expected Output

>>> df

 cite      sent 
 1         a
 2         a
 2         b
 3         a
 4         b
 5         c

I have tried with this but it did not work

df = df.pivot( columns='cite', values='sent')

what is the dtype that is holding the values in the DataFrame? list? — Z Li
– Z Li, Commented Dec 8, 2020 at 14:53

Mayank Porwal · Accepted Answer · 2020-12-08 15:08:41Z

1

Use Series.str.split with df.explode and df.sort_values:

In [141]: df = df.assign(cite=df['cite'].str.split(',')).explode('cite').sort_values('cite')

In [142]: df
Out[142]: 
  sent cite
0    a    1
0    a    2
1    b    2
0    a    3
1    b    4
2    c    5

edited Dec 8, 2020 at 15:08

answered Dec 8, 2020 at 14:56

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

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

Comments

BENY · Accepted Answer · 2020-12-08 14:54:06Z

0

Try split then explode

df = df.assign(cite=df['cite'].str.split(',')).explode('cite')

answered Dec 8, 2020 at 14:54

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Transpose dataframe in Python pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related