converting list like column values into multiple rows using Pandas DataFrame

user_7937815 0
Viewed 900

CSV file: (sample1.csv)

Location_City, Location_State, Name, hobbies
Los Angeles,   CA,             John, "['Music', 'Running']"
Texas,         TX,             Jack, "['Swimming', 'Trekking']"

I want to convert hobbies column of CSV into following output

Location_City, Location_State, Name, hobbies
Los Angeles,   CA,             John, Music
Los Angeles,   CA,             John, Running
Texas,         TX,             Jack, Swimming
Texas,         TX,             Jack, Trekking

I have read csv into dataframe but I don't know how to convert it?

 data = pd.read_csv("sample1.csv") 
 df=pd.DataFrame(data)
 df
pandas dataframe python
2 Answers

You can use findall or extractall for get lists from hobbies colum, then flatten with chain.from_iterable and repeat another columns:

a = df['hobbies'].str.findall("'(.*?)'").astype(np.object)
lens = a.str.len()

from itertools import chain

df1 = pd.DataFrame({
    'Location_City' : df['Location_City'].values.repeat(lens),
    'Location_State' : df['Location_State'].values.repeat(lens),
    'Name' : df['Name'].values.repeat(lens),
    'hobbies' : list(chain.from_iterable(a.tolist())), 
})

Or create Series, remove first level and join to original DataFrame:

df1 = (df.join(df.pop('hobbies').str.extractall("'(.*?)'")[0]
               .reset_index(level=1, drop=True)
               .rename('hobbies'))
         .reset_index(drop=True))

print (df1)

  Location_City Location_State  Name   hobbies
0   Los Angeles             CA  John     Music
1   Los Angeles             CA  John   Running
2         Texas             TX  Jack  Swimming
3         Texas             TX  Jack  Trekking
user_29010020

We can solve this using pandas.DataFrame.explode function which was introduced in version 0.25.0 if you have same or higher version, you can use below code.
explode function reference: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.explode.html

import pandas as pd
import ast

data = {
    'Location_City': ['Los Angeles','Texas'],
    'Location_State': ['CA','TX'],
    'Name': ['John','Jack'],
    'hobbies': ["['Music', 'Running']", "['Swimming', 'Trekking']"]
}
df = pd.DataFrame(data)

# Converting a string representation of a list into an actual list object

list_eval = lambda x: ast.literal_eval(x)
df['hobbies'] = df['hobbies'].apply(list_eval)

# Exploding the list
df = df.explode('hobbies')

print(df)

  Location_City Location_State  Name   hobbies
0   Los Angeles             CA  John     Music
0   Los Angeles             CA  John   Running
1         Texas             TX  Jack  Swimming
1         Texas             TX  Jack  Trekking
user_79378150
Related