Split Dataframe column on delimiter when number of strings to split is not definite

user_3578056 0
Viewed 76

I have a dataframe as below :

    A              B
0   33590104       3359017;3359011;3359031
1   53340311       5334012
2   160750035      16075131;16075132;16075135;16075046
3   10510044       1051012;1051097;1051010;1051051;1051089;105106...
4   51540061       5154036

I want to have rows for each value in A with every value in B that is separated by ';' like below

   A              B
   33590104       3359017
   33590104       3359011
   33590104       3359031
   53340311       5334012
   160750035      16075131
   160750035      16075132
   160750035      16075135
   160750035      16075046

and so on....

My idea is to first convert the string in column B into a list. for example :

        A              B
   0    33590104       [3359017,3359011,3359031]
   1    53340311       [5334012]
   2    160750035      [16075131,16075132,16075135,16075046]

And then use the explode function. But I don't know how I can convert string with delimiter ';' to a list. Also I don't know exactly how many strings are separated by ';' in each row. It varies for each row as you can see in the example above.

pandas explode
2 Answers

You can covert your string to list with string .split() inside .map() method:

df['B'] = df['B'].map(lambda x: x.split(';'))

And then use .explode():

df.explode('B').reset_index(drop=True)
user_115285090

You can use methods split and explode:

df['B'] = df['B'].str.split(';')
df.explode('B', ignore_index=True)

or

df.assign(B=df['B'].str.split(';')).explode('B', ignore_index=True)

Output:

            A          B
0    33590104    3359017
1    33590104    3359011
2    33590104    3359031
3    53340311    5334012
4   160750035   16075131
5   160750035   16075132
6   160750035   16075135
7   160750035   16075046
8    10510044    1051012
9    10510044    1051097
10   10510044    1051010
11   10510044    1051051
12   10510044    1051089
13   10510044  105106...
14   51540061    5154036
user_89736200
Related