How to fill a nan value in a column with value of column which has same values for other columns

user_14145264 0
Viewed 180

enter image description hereThis is a problem from KPMG virtual internship

My question is how to fill nan values job_industry column with values of job_industry of columns having a same job title

for example:

job_title               job_industry

Quality Engineer        Financial Services
Quality Engineer        Nan

I want the nan value for job_industry to be filled with Financial Services

like if a nan value is present at job_industry who job_title is General manager ,then fill it with Manufacturing

pandas python
3 Answers

Use groupby.ffill and groupby.bfill to automatically fill each job_title's missing job_industry :

g = df.groupby('job_title')['job_industry']
df['job_industry'] = g.ffill()
df['job_industry'] = g.bfill()

#           job_title        job_industry
# 0  Quality Engineer  Financial Services
# 1  Quality Engineer  Financial Services

Note that bfill is technically not needed for the simplified 2-row example but is needed for real data.

user_131383640

I would start by creating a mapping (Python dict) between job_industry and job_title and then assigning the mapping of the column job_industry to the NaN values of job_title.

Here is the code:

df = pd.DataFrame(
    columns=["job_title", "job_industry"],
    data=[["Quality Engineer", "Financial Services"], ["Quality Engineer", None]]
)

# May be there is a faster way
title_industry_mapping = df.dropna(["job_industry"]).set_index("job_title")["job_industry"].drop_duplicates().to_dict()

isna = df["job_industry"].isna()
df.loc[isna, "job_industry"] = df.loc[isna, "job_title"].replace(title_industry_mapping)

Result:

job_title job_industry
0 Quality Engineer Financial Services
1 Quality Engineer Financial Services
user_85003570 
import pandas as pd
import numpy as np
df = pd.DataFrame([
     ['Quality Engineer','Financial Services'],
     ['Progammer',np.nan],
     ['Quality Engineer',np.nan],
     ['Progammer',"IT"],
     ['General manager',np.nan]],
    columns=['job_title','job_industry'])
with pd.option_context('mode.use_inf_as_null', True):
    df = df.sort_values('job_industry', ascending=False, na_position='last')
df["job_industry"].loc[(df['job_title'] == "General manager") & (df['job_industry'].isnull())] = "Manufacturing"
df['job_industry'] = df.groupby('job_title')['job_industry'].fillna(method="ffill")

df['job_industry'].isnull(), this will verify job_industry column is null or not.

The following code will sort by null value in descending order of by the column job_industry,because if nan value appears before, the initial value of nan will not replace.

with pd.option_context('mode.use_inf_as_null', True):
    df = df.sort_values('job_industry', ascending=False, na_position='last')

if your prefer ordering to the output, you can try,df.sort_index()

O/P

+----+------------------+-------------------------------------------------------+
|    | job_title        | job_industry                                          |
|----+------------------+-------------------------------------------------------|
|  0 | Quality Engineer | Financial Services                                    |
|  1 | Progammer        | IT                                                    |
|  2 | Quality Engineer | Financial Services                                    |
|  3 | Progammer        | IT                                                    |
|  4 | General manager  | Manufacturing                                         |
+----+------------------+-------------------------------------------------------+
user_115432530
Related