-1

i have 2 columns area and pincode

Area Pincode 
ABC - 1234 
XYZ - 4118 
qwe - 1023 
rty - 1234 
XYZ - ? 
rty - ? 
qwe - ? 
ABC - ?

so i have multiple areas and want to fillup pincode column based on area as I see area and pin codes are available but i notice that some pin codes are missing though the area is same Thanks!

df4.loc[df4.pins.isnull(),'pins'] = df4.loc[df4.pins.isnull(),'Area'].map(df4.loc[df4.pins.notnull()].set_index('Area')['pins'])

but this is not working

herrbischoff
  • 3,294
  • 1
  • 29
  • 50
om9595
  • 53
  • 4
  • Please add an example of your data (textual not image) along with the code you have tried. – Umar.H Oct 03 '19 at 08:05
  • lets say i have 2 columns area and pincode Area Pincode ABC 1234 XYZ 4118 qwe 1023 rty 1234 XYZ qwe ABC so i have multiple areas and want to fillup pincode column based on area as I see area and pin codes are available but i notice that no pin code is given through area is same Thanks! – om9595 Oct 03 '19 at 10:04
  • Thanks! df4.loc[df4.pins.isnull(),'pins'] = df4.loc[df4.pins.isnull(),'Area'].map(df4.loc[df4.pins.notnull()].set_index('Area')['pins']) but this is not working Thanks – om9595 Oct 03 '19 at 10:12
  • show us your sample data and expected output please – Umar.H Oct 03 '19 at 10:13
  • Area Pincode ABC - 1234 XYZ - 4118 qwe - 1023 rty - 1234 XYZ - ? rty - ? qwe - ? ABC - ? this is my sample data and expected output is i want to fill up missing pincodes of areas as areas are repeating. and I have around 10,000 areas – om9595 Oct 03 '19 at 11:27

1 Answers1

0

this should work, hard to say without a proper visual of your data..

df.loc[df['pcode'].isnull()==True,'pcode'] = df['Unnnamed:']

IIUC, you can use a fillna with a groupby and transform to get your result

using your dummy data above : (I replaced your '?' with true null values)

   ABC  1234
0  XYZ   4118
1  qwe   1023
2  rty   1234
3  XYZ    NaN
4  rty    NaN
5  qwe    NaN


df['1234'] = df['1234'].fillna(df.groupby('ABC')['1234'].transform('first'))
print(df)
   ABC   1234
0  XYZ   4118
1  qwe   1023
2  rty   1234
3  XYZ   4118
4  rty   1234
5  qwe   1023
Umar.H
  • 22,559
  • 7
  • 39
  • 74
  • lets say i have 2 columns area and pincode Area Pincode ABC 1234 XYZ 4118 qwe 1023 rty 1234 XYZ qwe ABC so i have multiple areas and want to fillup pincode column based on area as I see area and pin codes are available but i notice that no pin code is given through area is same Thanks! – om9595 Oct 03 '19 at 10:17
  • @om9595 please provide a sample input and ouput please read https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples – Umar.H Oct 03 '19 at 10:20
  • df['1234'] = df['1234'].fillna(df.groupby('ABC')['1234'].transform('first')) this is working thanks a lot! now my next step is to verify these multiple areas and pin codes with Google's data set ( I have downloaded data set ) but suppose my area Aundh - 411007 and want to verify with Google's data set which says Aaoundh - 411007 which is same but spelling is different so how verify ? – om9595 Oct 03 '19 at 13:10
  • @om9595 this is a new question, can you accept this one if it answered your query. Ask a new question but read the guide above. – Umar.H Oct 03 '19 at 13:13
  • Sure! Thanks a lot! – om9595 Oct 03 '19 at 13:30