Fill empty rows

Leonardo Urbiola 0
Viewed 73

I have a large dataset where some of the rows are not placed in the right column. For example

data:

#DataFrame
data = {'Status': ['Active', 'Active', 'Active', 'Active','Active', 'Active'],
        'Name': ['Tom', ' ', 'krish', ' ',  'Jack', 'Lisa'],
        'Email': ['test@gmail.com', ' ',  'test@gmail.com', ' ', 'test@gmail.com', 'test@gmail.com'],
        'Name2': [' ', 'John', ' ', 'Tim', ' ', ' '],
        'Email2':[' ', 'test@gmail.com', ' ', 'test@gmail.com', ' ', ' ']}

#Print DataFrame

df = pd.DataFrame(data)
df

In this case, the column 'Name' and 'email' has some empty spaces because they were placed in the a new column called 'name2' and 'email2'

I would like to see if I can fill the empty spaces with the actual name and email that were misplaced in different columns.

I was trying to do some research but I did not find any significant information or I do not know if this is possible.

actual dataset:

Status  Name  Email          Name2      Email2 
Active  Tom   t@gmail.com  
Active                       Tim        t@gmail.com
Active  Tom   t@gmail.com 
Active                       Tim        t@gmail.com

Expected result

Name2   Name  Email2 
Active  Tom   t@gmail.com  
Active  Tim   t@gmail.com
Active  Tom   t@gmail.com 
Active  Tom   t@gmail.com
dataframe pandas python
4 Answers

Try this.

df["Name"] = (df["Name"] + df["Name2"]).str.strip() # the strip(), to get rid of any white spaces around values.
df["Email"] = (df["Email"] + df["Email2"]).str.strip()
df.drop(["Name2", "Email2"], axis=1,inplace= True)

Hope it helped.

userSwSh10
  • This option is faster than all other current answers, as per the timeit comparison at the bottom.
  • Select the appropriate columns into separate dataframes with Boolean indexing, combine the dataframes with .concat, and sort the index.
    • Boolean indexing is vectorized and much faster than using .apply.
  • Empty cells are specified in the OP with ' ', which is used in df.Name.ne(' ') to skip rows with that value.
    • If the empty spaces are np.nan, then do ~df.Name.isna() instead of df.Name.ne(' ').
    • Use df = df.replace('\\s+', '', regex=True) if the empty strings are of different or indeterminate length, and change .ne(' ') to .ne('').
  • .copy() can be dropped if it doesn't result in SettingwithCopyWarning, which it didn't during testing.
# select Status and the columns without numbers
df1 = df.loc[df.Name.ne(' '), ['Status', 'Name', 'Email']].copy()

# select Status and the columns with 2; rename the columns with 2
df2 = df.loc[df.Name2.ne(' '), ['Status', 'Name2', 'Email2']].copy().rename({'Name2': 'Name', 'Email2': 'Email'}, axis=1)

df = pd.concat([df1, df2]).sort_index()

# display(df)
   Status   Name           Email
0  Active    Tom  test@gmail.com
1  Active   John  test@gmail.com
2  Active  krish  test@gmail.com
3  Active    Tim  test@gmail.com
4  Active   Jack  test@gmail.com
5  Active   Lisa  test@gmail.com

One-liner

# as a single line without creating separate objects for each set of columns
df = pd.concat([df.loc[df.Name.ne(' '), ['Status', 'Name', 'Email']],
                (df.loc[df.Name2.ne(' '), ['Status', 'Name2', 'Email2']])
                .rename({'Name2': 'Name', 'Email2': 'Email'}, axis=1)]).sort_index()

Comparison

# given df in the op, create a large dataset
df = pd.concat([df]*100000).reset_index(drop=True)


def trenton(df):
    return pd.concat([df.loc[df.Name.ne(' '), ['Status', 'Name', 'Email']],
                      (df.loc[df.Name2.ne(' '), ['Status', 'Name2', 'Email2']])
                      .rename({'Name2': 'Name', 'Email2': 'Email'}, axis=1)]).sort_index()


def echo(df):
    df["Name"] = (df["Name"] + df["Name2"]).str.strip()
    df["Email"] = (df["Email"] + df["Email2"]).str.strip()
    df = df.drop(["Name2", "Email2"], axis=1)
    return df


def sierra(df):
    df['Name'] = df.apply(lambda x: x[3] if x[1] == ' ' else x[1], axis=1)
    df['Email'] = df.apply(lambda x: x[4] if x[2] == ' ' else x[2], axis=1)
    df = df.drop(labels=['Name2', 'Email2'], axis=1)
    return df


def BeRT2me(df):
    df = df.applymap(str.strip)
    df = df.replace('', np.nan)
    df.Name = df.Name.fillna(df.Name2)
    df.Email = df.Email.fillna(df.Email2)
    df = df.drop(['Name2', 'Email2'], axis=1)
    return df

timeit

%timeit trenton(df)
[out]:
101 ms ± 604 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

%timeit echo(dftest)
[out]:
390 ms ± 13.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit sierra(df)
[out]:
6.11 s ± 64.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit BeRT2me(df)
[out]:
258 ms ± 4.28 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
Trenton McKinney0

Try:

df['Name'] = df.apply(lambda x: x[3] if x[1] == ' ' else x[1], axis=1)
df['Email'] = df.apply(lambda x: x[4] if x[2] == ' ' else x[2], axis=1)
df = df.drop(labels=['Name2', 'Email2'], axis=1)

result:

   Status   Name           Email
0  Active    Tom  test@gmail.com
1  Active   John  test@gmail.com
2  Active  krish  test@gmail.com
3  Active    Tim  test@gmail.com
4  Active   Jack  test@gmail.com
5  Active   Lisa  test@gmail.com
sierra_papa0

Given:

   Status   Name           Email Name2          Email2
0  Active    Tom  test@gmail.com
1  Active                         John  test@gmail.com
2  Active  krish  test@gmail.com
3  Active                          Tim  test@gmail.com
4  Active   Jack  test@gmail.com
5  Active   Lisa  test@gmail.com

Doing:

# Fix empty strings to be proper nan values:
df = df.applymap(str.strip)
df = df.replace('', np.nan)

# Fill Missing Values:
df.Name = df.Name.fillna(df.Name2)
df.Email = df.Email.fillna(df.Email2)

# Drop Excess columns:
df = df.drop(['Name2', 'Email2'], axis=1)
print(df)

Output:

   Status   Name           Email
0  Active    Tom  test@gmail.com
1  Active   John  test@gmail.com
2  Active  krish  test@gmail.com
3  Active    Tim  test@gmail.com
4  Active   Jack  test@gmail.com
5  Active   Lisa  test@gmail.com
BeRT2me0
Related