How to convert one row two columns dataframe into multiple rows two columns dataframe

Question

I am new to Python.

I have a dataframe with two columns. One is ID column and the other is the year and count information related to the ID.

I want to convert this format into multiple rows with the same ID.

The current dataframe looks like:

ID    information
1     2014:Total:0, 2015:Total:1, 2016:Total:2
2     2017:Total:3, 2018:Total:1, 2019:Total:2

I expect the converted dataframe should like this:

ID    Year   Value
1     2014    0
1     2015    1
1     2016    2
2     2017    3
2     2018    1
2     2019    2

I tried to use the str.split method of pandas dataframe, but no luck.

Any suggestions would be appreciated.

BENY · Accepted Answer · 2019-07-24 02:39:05Z

1

Let us using explode :-) (New in pandas 0.25.0)

df.information=df.information.str.split(', ')
Yourdf=df[['ID']].join(df.information.explode().str.split(':',expand=True).drop(1,axis=1))
Yourdf
   ID     0  2
0   1  2014  0
0   1  2015  1
0   1  2016  2
1   2  2017  3
1   2  2018  1
1   2  2019  2

answered Jul 24, 2019 at 2:39

BENY

324k22 gold badges176 silver badges250 bronze badges

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

1 Comment

LR2019 Over a year ago

Thank you, both! It is good to know this new method in Pandas!

U13-Forward · Accepted Answer · 2019-07-24 02:55:10Z

0

Try using the below code, unlike @WenYoBen's answer this works for much lower versions as well:

df2 = pd.DataFrame(df['information'].str.split(', ', expand=True).apply(lambda x: x.str.split(':')).T.values.flatten().tolist(), columns=['Year', '', 'Value']).iloc[:, [0, 2]]
print(pd.DataFrame(sorted(df['ID'].tolist() * (len(df2) // 2)), columns=['ID']).join(df2))

Output:

   ID  Year Value
0   1  2014     0
1   1  2017     3
2   1  2015     1
3   2  2018     1
4   2  2016     2
5   2  2019     2

edited Jul 24, 2019 at 2:55

answered Jul 24, 2019 at 2:49

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Collectives™ on Stack Overflow

How to convert one row two columns dataframe into multiple rows two columns dataframe

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related