Updating Nan Values in CSV file using pandas with if else condition on other column of CSV

user_6918891 0
Viewed 235

I want to update the Nan values in Age column based on class of the person.The csv file contain Age and class of person.
If the person is of 1st class his age is 35.
If the person is of 2nd class his age is 30.
If the person is of 3rd class his age is 25. 

Csv File
Age   Class
32     2
26     3
Nan    1
36     1
24     3
Nan    2
Nan    3


Update Csv File
Age   Class
32     2
26     3
35     1
36     1
24     3
30     2
25     3

I have tried some pandas function but it did not worked.

df[ (df.Age.isnull()) & (df.Pclass==1) ]['Age'] = 35
df[ (df.Age.isnull()) & (df.Pclass==2) ]['Age'] = 30
df[ (df.Age.isnull()) & (df.Pclass==3) ]['Age'] = 25

Please provide a solution....

pandas python
4 Answers

You can use map:

age_dict={1:35, 2:30, 3:25}

# read csv
df = pd.read_csv('in_file.csv')

# update the missing ages
df['Age'] = df['Age'].fillna(df['Class'].map(age_dict))

# save csv
df.to_csv('out_file.csv')
user_42384080

Enjoy:

df['Age'] = np.where((df.Age.isnull() == True) & (df.Pclass==1), 35, df['Age])
df['Age'] = np.where((df.Age.isnull() == True) & (df.Pclass==2), 30, df['Age])
df['Age'] = np.where((df.Age.isnull() == True) & (df.Pclass==3), 25, df['Age])
user_108363090

I guess something like this should work,


df['Age'][ df['Age'].isnull() & df['lass'] == 1] = 35
df['Age'][ df['Age'].isnull() & df['lass'] == 2] = 30
df['Age'][ df['Age'].isnull() & df['lass'] == 3] = 25
user_98147100

First Convert the csv file as shown below before importing to pandas:

Age,Class
32,2
26,3
,1
36,1
24,3
,2
,3

Now import the csv file and assign values to Nan.

import pandas as pd
df = pd.read_csv(YOUR_CSV_FILE)
df.loc[ (df.Age.isna()) & (df.Class==1) ] = 35,1
df.loc[ (df.Age.isna()) & (df.Class==2) ] = 30,1
df.loc[ (df.Age.isna()) & (df.Class==3) ] = 25,1
user_48170340
Related