Skip to content
Advertisement

Replace values in one dataframe with values in second dataframe in Python

I have a large dataframe (DF1) that contains a variable containing UK postcode data. Inevitably there are some typos in the data. However, after some work with regular expressions, I have created a second database that contains corrected versions of the postcode data (but only for those rows where the original postcode was incorrect) – DF2. (N.B. the index values are not necessarily consecutive.)

    id   postcode                     remark
0    1      L93AP                     Normal
2    2     LD38AH                     Normal
4    3    SO224ER                     Normal
6    4       SO21                  Too short
8    5    DN379HJ                     Normal
10   6     M21ORH  Zero replaced with O (oh)
12   7     NP745G          S replaced with 5
14   8    SE136R2          Z replaced with 2
16   9  BN251ESBN                   Too long
18  10    TD152EH                     Normal

The dataframe containing the corrected data is:

       0  1  2  3 pcCorrected
10   M21  0  R  H      M210RH
12   NP7  4  S  G      NP74SG
14  SE13  6  R  Z     SE136RZ

I want to combine the 2 databases such that the new values in the pcCorrected column in DF2 replace the old postcode values in the DF1 dataframe but, for other cells, the existing postcode values remain in tact. The final database should look like:

    id   postcode                     remark
0    1      L93AP                     Normal
2    2     LD38AH                     Normal
4    3    SO224ER                     Normal
6    4       SO21                  Too short
8    5    DN379HJ                     Normal
10   6     M210RH                     Normal
12   7     NP74SG                     Normal
14   8    SE136RZ                     Normal
16   9  BN251ESBN                   Too long
18  10    TD152EH                     Normal

The databases are quite large (>1 million rows). Does this action have a name and what is the most efficient way to do this?

Advertisement

Answer

You can try merge by indexes , create mask by notnull and add new values by loc:

df = pd.merge(df1, df2, left_index=True, right_index=True, how='left')
mask = pd.notnull(df['pcCorrected'])
print mask
0     False
2     False
4     False
6     False
8     False
10     True
12     True
14     True
16    False
18    False
Name: pcCorrected, dtype: bool

df.loc[mask, 'remark'] = 'Normal'
df.loc[mask, 'postcode'] = df['pcCorrected']

print df[['id','postcode','remark']]
    id   postcode     remark
0    1      L93AP     Normal
2    2     LD38AH     Normal
4    3    SO224ER     Normal
6    4       SO21  Too short
8    5    DN379HJ     Normal
10   6     M210RH     Normal
12   7     NP74SG     Normal
14   8    SE136RZ     Normal
16   9  BN251ESBN   Too long
18  10    TD152EH     Normal
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement