Splitting columns in multiple rows and adding number column to indicate each value which is split

user_4795837 0
Viewed 34

How do I split the url column data into multiple rows and add a number column along with it. I have a df as given below. url column has distinct urls.

|----|----------|------------------|---------|
| id | source   |    url           | name    |
---------------------------------------------|
| 1  | Android  | url1, url2, url3 | Person1 |
| 2  | iPhone   | url1             | Person2 |
| 3  | PC       | url1, url2       | Person3 |
|--------------------------------------------|

I was able to split it using the split().apply(Series, 1).stack() method but I even need to add another column.

temp_df = df.url.str.split(',').apply(pd.Series, 1).stack()
temp_df.index = temp_df.index.droplevel(-1)
temp_df.name = 'url'

df.drop('url', axis=1, inplace=True)
df = df.join(temp_df)

|----|----------|------------------|---------|
| id | source   |    url           | name    |
---------------------------------------------|
| 1  | Android  | url1             | Person1 |
| 1  | Android  | url2             | Person1 |
| 1  | Android  | url3             | Person1 |
| 2  | iPhone   | url1             | Person2 |
| 3  | PC       | url1             | Person3 |
| 3  | PC       | url2             | Person3 |
|--------------------------------------------|

How do I split it so that for each url which is split, I can add a respectively number column to it as given in the below format?

|----|----------|------|------------------|---------|
| id | source   | num  |    url           | name    |
----------------------------------------------------|
| 1  | Android  |  1   | url1             | Person1 |
| 1  | Android  |  2   | url2             | Person1 |
| 1  | Android  |  3   | url3             | Person1 |
| 2  | iPhone   |  1   | url1             | Person2 |
| 3  | PC       |  1   | url1             | Person3 |
| 3  | PC       |  2   | url2             | Person3 |
|---------------------------------------------------|
pandas dataframe numpy python
1 Answers

You can use str.split() to separate the URLs, then explode and finally groupby().cumcount():

(df.assign(url=df.url.str.split(',\s*'))
   .explode('url')
   .assign(num=lambda x: x.groupby('id').cumcount()+1)
)

Output:

   id   source   url     name  num
0   1  Android  url1  Person1    1
0   1  Android  url2  Person1    2
0   1  Android  url3  Person1    3
1   2   iPhone  url1  Person2    1
2   3       PC  url1  Person3    1
2   3       PC  url2  Person3    2
user_42384080
Related