CODEWITHSUNDEEP
pythonpandas
yogz123
yogz123

Reputation: 723

Split list in Pandas dataframe column into multiple columns

I am working with movie data and have a dataframe column for movie genre. Currently the column contains a list of movie genres for each movie (as most movies are assigned to multiple genres), but for the purpose of this analysis, I would like to parse the list and create a new dataframe column for each genre. So instead of having genre=['Drama','Thriller'] for a given movie, I would have two columns, something like genre1='Drama' and genre2='Thriller'.

Here is a snippet of my data:

{'color': {0: [u'Color::(Technicolor)'],
  1: [u'Color::(Technicolor)'],
  2: [u'Color::(Technicolor)'],
  3: [u'Color::(Technicolor)'],
  4: [u'Black and White']},
 'country': {0: [u'USA'],
  1: [u'USA'],
  2: [u'USA'],
  3: [u'USA', u'UK'],
  4: [u'USA']},
 'genre': {0: [u'Crime', u'Drama'],
  1: [u'Crime', u'Drama'],
  2: [u'Crime', u'Drama'],
  3: [u'Action', u'Crime', u'Drama', u'Thriller'],
  4: [u'Crime', u'Drama']},
 'language': {0: [u'English'],
  1: [u'English', u'Italian', u'Latin'],
  2: [u'English', u'Italian', u'Spanish', u'Latin', u'Sicilian'],
  3: [u'English', u'Mandarin'],
  4: [u'English']},
 'rating': {0: 9.3, 1: 9.2, 2: 9.0, 3: 9.0, 4: 8.9},
 'runtime': {0: [u'142'],
  1: [u'175'],
  2: [u'202', u'220::(The Godfather Trilogy 1901-1980 VHS Special Edition)'],
  3: [u'152'],
  4: [u'96']},
 'title': {0: u'The Shawshank Redemption',
  1: u'The Godfather',
  2: u'The Godfather: Part II',
  3: u'The Dark Knight',
  4: u'12 Angry Men'},
 'votes': {0: 1793199, 1: 1224249, 2: 842044, 3: 1774083, 4: 484061},
 'year': {0: 1994, 1: 1972, 2: 1974, 3: 2008, 4: 1957}}

Any help would be greatly appreciated! Thanks!

Upvotes: 1

Views: 908

Answers (2)

jezrael
jezrael

Reputation: 862581

I think you need DataFrame constructor with add_prefix and last concat to original:

df1 = pd.DataFrame(df.genre.values.tolist()).add_prefix('genre_')
df = pd.concat([df.drop('genre',axis=1), df1], axis=1)

Timings:

df = pd.DataFrame(d)
print (df)
#5000 rows 
df = pd.concat([df]*1000).reset_index(drop=True)

In [394]: %timeit (pd.concat([df.drop('genre',axis=1), pd.DataFrame(df.genre.values.tolist()).add_prefix('genre_')], axis=1))
100 loops, best of 3: 3.4 ms per loop

In [395]: %timeit (pd.concat([df.drop(['genre'],axis=1),df['genre'].apply(pd.Series).rename(columns={0:'genre_0',1:'genre_1',2:'genre_2',3:'genre_3'})],axis=1))
1 loop, best of 3: 757 ms per loop

Upvotes: 2

gold_cy
gold_cy

Reputation: 14216

This should work for you:

pd.concat([df.drop(['genre'],axis=1),df['genre'].apply(pd.Series).rename(columns={0:'genre_0',1:'genre_1',2:'genre_2',3:'genre_3'})],axis=1)

Upvotes: 0

Related Questions