Divide cell with delimiter given empty cell

user_16592214 0
Viewed 85

I have to clean a data frame with empty rows by separating the values from one column. My dataframe currently looks like this:

     id_number              section 
0    00000 & A                  NaN
1    01000                        B
2    01001                        C
3    01003 & D                  NaN
4    01005 & E                  NaN

and i need the end result to look like this:

     id_number              section 
0    00000                        A
1    01000                        B
2    01001                        C
3    01003                        D
4    01005                        E

my code so far is

import pandas   
df[['id_number','section']] = df.id_number.str.split(" & ",expand=True,)

but it returns "None" for the the cells with sections already:

     id_number              section 
0    00000                        A
1    01000                     None
2    01001                     None
3    01003                        D
4    01005                        E

Would anyone know how to fix this? Thank you!

pandas dataframe python
3 Answers

What you have done is also fine, just assign the section after splitting to a new column temporarily, then just backfill the previous section and new section columns, then assign it to the section.

df[['id_number','NewSection']]= df.id_number.str.split(" & ",expand=True)
df['section'] = df[['section', 'NewSection']].bfill(axis=1)['section']

OUTPUT:

  id_number section
0     00000       A
1     01000       B
2     01001       C
3     01003       D
4     01005       E

EDIT: Steps break out: The given dataframe (df):

   id_number section
0  00000 & A     NaN
1      01000       B
2      01001       C
3  01003 & D     NaN
4  01005 & E     NaN

Do the same thing that you are doing, i.e. split on & with expand=True, and assign them to id_number, and a new column NewSection.

df[['id_number','NewSection']]= df.id_number.str.split(" & ",expand=True)

  id_number section NewSection
0     00000     NaN          A
1     01000       B       None
2     01001       C       None
3     01003     NaN          D
4     01005     NaN          E

Now take the columns section, and NewSection and backfill them on axis=1, so that the NaN values in section column get replaced by the value at NewSection at the given index. Then take the section column and assign it to the section of the dataframe

df[['section', 'NewSection']].bfill(axis=1)
  section NewSection
0       A          A
1       B       None
2       C       None
3       D          D
4       E          E

df['section'] = df[['section', 'NewSection']].bfill(axis=1)['section']

Now, section will have the values with NaN replaced from NewSection for corresponding index:

  id_number section NewSection
0     00000       A          A
1     01000       B       None
2     01001       C       None
3     01003       D          D
4     01005       E          E

Now, take only the id_number, and section columns.

user_91363480

Here is my suggestion:

df.loc[df.id_number.str.contains(' & '),'section'] = df.loc[df.id_number.str.contains(' & '),'id_number'].apply(lambda x: x.split(' & ')[1])

df.loc[df.id_number.str.contains(' & '),'id_number'] = df.loc[df.id_number.str.contains(' & '),'id_number'].apply(lambda x: x.split(' & ')[0])

>>> print(df)
  id_number section
0     00000       A
1     01000       B
2     01001       C
3     01003       D
4     01005       E
user_82285580

Heres my suggestion:

import re

df['id_number'] = df['id_number'].astype(str)
df.loc[df['section'].isna(), 'section'] = df['id_number'].apply(lambda x: re.sub('\d+\s\&', '', x))
df['id_number'] = df['id_number'].str.replace('\s\&\D+', '')
user_125228810
Related