CODEWITHSUNDEEP
python-3.xpandasdataframe
Lilly
Lilly

Reputation: 988

pandas transform one row into multiple rows

I have a dataframe as below.

My dataframe as below.

ID      list
1       a, b, c
2       a, s
3       NA
5       f, j, l

I need to break each items in the list column(String) into independent row as below: 

ID      item
1       a
1       b
1       c
2       a
2       s
3       NA
5       f
5       j
5       l

Thanks.

Upvotes: 6

Views: 6496

Answers (2)

Pulkit Jha
Pulkit Jha

Reputation: 1729

A beginners approach : Just another way of doing the same thing using pd.DataFrame.stack

df['list'] = df['list'].map(lambda x : str(x).split(','))
dfOut = pd.DataFrame(df['list'].values.tolist())
dfOut.index = df['ID']
dfOut = dfOut.stack().reset_index()
del dfOut['level_1']
dfOut.rename(columns = {0 : 'list'}, inplace = True)

Output:

   ID list
0   1    a
1   1    b
2   1    c
3   2    a
4   2    s
5   3  nan
6   5    f
7   5    j
8   5    l

Upvotes: 0

Henry Yik
Henry Yik

Reputation: 22503

Use str.split to separate your items then explode:

print (df.assign(list=df["list"].str.split(", ")).explode("list"))

   ID list
0   1    a
0   1    b
0   1    c
1   2    a
1   2    s
2   3  NaN
3   5    f
3   5    j
3   5    l

Upvotes: 4

Related Questions