Pandas - convert list to string & Wide to Long with one column

Question

My dataframe df looks like this, the ID column is a list.

Name     Title     ID
ED       HHH     [11111, 11112, 11113, 11114]
AD       BBB     [11119, 11133]

I want to first convert column ID to a string, since I'm assuming that is how I would want the column to move on to the next step. Next step would be to basically opposite of a pivot table the dataframe df to look like below.

I would like my dataframe df to look like this

   Name     Title     ID
    ED       HHH     11111
    ED       HHH     11112
    ED       HHH     11113
    ED       HHH     11114
    AD       BBB     11119
    AD       BBB     11133

I've tried

df['ID'] = df['ID'].str.split(', ')
df['ID'] = df['ID'].str.join(map(str,df['ID']))
df['ID'] = str(df['ID']).strip(', ')
df['ID'] = ''.join(str(x) for x in df['ID'])

and it does not give me what I'm looking for or it uses up too much RAM and locks my computer.

Any help or tips? Do I even have to convert the list to a string first to put it in my desired output?

Just to double-check, what do you get for type(df['ID'].values[0]) ? — measureallthethings, Sep 29 '15 at 19:53

score 3 · Accepted Answer · edited May 23 '17 at 11:52

Try following this answer pandas: How do I split text in a column into multiple rows?

In your case there would be a slight modification as noted below:

import numpy as np
import pandas as pd
from pandas import Series, DataFrame

# since you have a column of lists skip any type of string munging
# instead, go straight to converting the lists to a Series
s = df['ID'].apply(Series,1).stack()
s.index = s.index.droplevel(-1)
s.name = 'ID'
del df['ID']
df = df.join(s)

Please note this example completely ignores the index of your DataFrame; you may need to update or reset_index().

Pandas - convert list to string & Wide to Long with one column

1 Answers1