CODEWITHSUNDEEP
python-3.xpandas
om9595
om9595

Reputation: 53

fill up NaN using columns of other dataframe

I notice that most of the areas are not present in area column and they are present in city column so infornt of city I want to fillup pincodes in df1

I have two dataframes 

Df1 = 
City          area          Pincode
Pune        Bibwewadi       159963
Mumbai      Bandra(W)       123456
Bibwewadi           
Bandra(E)
Badlapur     Badlapur       752147
Bhiwandi     Bhiwandi       784512

Df2 = 
Area        Pincode
Bibwewadi   159963
Badlapur    752147
Parvati     784596
Baner       411007
Bandra(E)   326598

As in df1 some areas are in city column I want to fillup NaN values of pincode column in df1 withe help of df2 using pandas

Expected output 

df1=
City          area          Pincode
Pune        Bibwewadi       159963
Mumbai      Bandra(W)       123456
Bibwewadi                   159963
Bandra(E)                   326598
Badlapur     Badlapur       752147
Bhiwandi     Bhiwandi       784512

Upvotes: 1

Views: 59

Answers (1)

ansev
ansev

Reputation: 30920

You can use pandas.Series.map, This option may be useful if the values ​​are not NaN values.:

c=df1['Pincode'].isnull()|df1['Pincode'].eq('')
df1=df1.replace('Bandra – E','Bandra(E)')
df1.loc[c,'Pincode']=df1.loc[c,'City'].map(df2.set_index('Area')['Pincode'])
print(df1)


        City       area   Pincode
0       Pune  Bibwewadi  159963.0
1     Mumbai  Bandra(W)  123456.0
2  Bibwewadi       None  159963.0
3  Bandra(E)       None  326598.0
4   Badlapur   Badlapur  752147.0
5   Bhiwandi   Bhiwandi  784512.0

or Series.fillna

df1=df1.replace('Bandra – E','Bandra(E)').set_index('City')
df1['Pincode']=df1['Pincode'].fillna(df2.set_index('Area')['Pincode'])
df1.reset_index(inplace=True)

Note:

Check well what type of missing values ​​you have in the dataframe, as well as the column labels and the replaced value:'Bandra – E'

Upvotes: 1

Related Questions