Take column of string data in pandas dataframe and split into separate columns

Question

I have a pandas dataframe from data I read from a CSV. One column is for the name of a group, while the other column contains a string (that looks like a list), like the following:

Group      |  Followers
------------------------------------------
biebers    |  u'user1', u'user2', u'user3'
catladies  |  u'user4', u'user5'
bkworms    |  u'user6', u'user7'

I'd like to try to split up the strings in the "Followers" column and make a separate dataframe where each row is for a user, as well as a column showing which group they're in. So for this example I'd like to get the following:

User       |     Group
--------------------------------
user1      |     biebers
user2      |     biebers
user3      |     biebers
user4      |     catladies
user5      |     catladies
user6      |     bkworms
user7      |     bkworms

Anyone have suggestions for the best way to approach this? Here's a screenshot of what it looks like:

What do you mean a "string that looks like a list?" Does it look like a list of multiple unicode strings? How did that get in there? — juanpa.arrivillaga
– juanpa.arrivillaga, Commented Sep 15, 2016 at 6:10
Yes the entries are all strings (that happen to look like a list of unicode strings). The data was read from a CSV, which returned strings for all the entries... — Imu
– Imu, Commented Sep 15, 2016 at 6:16

piRSquared · Accepted Answer · 2016-09-15 14:47:31Z

2

df.Followers = df.Followers.str.replace(r"u'([^']*)'", r'\1')

df.set_index('Group').Followers.str.split(r',\s*', expand=True) \
  .stack().rename('User').reset_index('Group').set_index('User')

To keep User as a column.

df.Followers = df.Followers.str.replace(r"u'([^']*)'", r'\1')

df.set_index('Group').Followers.str.split(r',\s*', expand=True) \
  .stack().rename('User').reset_index('Group') \
  .reset_index(drop=True)[['User', 'Group']]

edited Sep 15, 2016 at 14:47

answered Sep 15, 2016 at 6:35

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

5 Comments

juanpa.arrivillaga Over a year ago

Oh wow, never knew about expand=True that will come in handy.

Imu Over a year ago

That was great! I'm wondering though how to make the df so the users are not the index, but just another column... Sorry, I wasn't clear on the exact output I needed...

piRSquared Over a year ago

@Imu easy, that was a choice of mine. I'll update my post.

Imu Over a year ago

For the second option I get a: KeyError: ('User', 'Group'). Any idea what might be going on there?

piRSquared Over a year ago

@Imu fixed typo

Collectives™ on Stack Overflow

Take column of string data in pandas dataframe and split into separate columns

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related