muni muni - 3 months ago 6
Python Question

Convert the delimited data in a string to values in a single column

I have a dataframe like this:

Var_1
201601_abc
201603_tbc;201608_sdf;201508_dsf
201601_abc;201508_dsf
...


I want a single column that contains the distinct values in Var1(values delimited by ";" are considered different

So Final dataframe will be like:

Var_2
201601_abc
201603_tbc
201608_sdf
201508_dsf

Answer

IIUC the following should work:

In [160]:
df2 = pd.DataFrame(df['Var_1'].str.split(';',expand=True).stack().unique(), columns=['Var_2'])
df2

Out[160]:
        Var_2
0  201601_abc
1  201603_tbc
2  201608_sdf
3  201508_dsf

This splits the values on the delimiter, then stacks and returns the unique values, we can then construct a new df based on the returned array

splitting the above steps:

In [161]:
df['Var_1'].str.split(';',expand=True)

Out[161]:
            0           1           2
0  201601_abc        None        None
1  201603_tbc  201608_sdf  201508_dsf
2  201601_abc  201508_dsf        None

In [162]:    
df['Var_1'].str.split(';',expand=True).stack()

Out[162]:
0  0    201601_abc
1  0    201603_tbc
   1    201608_sdf
   2    201508_dsf
2  0    201601_abc
   1    201508_dsf
dtype: object

In [163]:
df['Var_1'].str.split(';',expand=True).stack().unique()

Out[163]:
array(['201601_abc', '201603_tbc', '201608_sdf', '201508_dsf'], dtype=object)
Comments