[Solved] I want a pandas script to line up values from one excel sheet to another based on the values in the first spreadsheet

by


Commented for explanation of approach. Have found two addresses where ID from sheet2 comes back onto sheet1

import io
sheeta = pd.read_csv(io.StringIO("""   house_number        street      suburb
0            43  Smith Street   Frewville
1            45  Smith Street   Frewville
2            47  Smith Street   Frewville
3            49  Smith Street   Frewville
4            51  Smith Street   Frewville
5            53  Smith Street   Frewville
6             1   Flinders St  Kensington
7             3   Flinders St  Kensington
8             5   Flinders St  Kensington
9             7   Flinders St  Kensington"""), sep="\s\s+", engine="python")
sheetb = pd.read_csv(io.StringIO("""ID  house_number           street      suburb
0    5509            43     Smith Street   Frewville
1    5120            26    Taylor Avenue    Glenside
2    4731            34  Brussels Street   Frewville
3    4342            12  Brussels Street   Frewville
4    3953             1      Roger Court     Clifton
5   12098             4     Elizabeth St     Clifton
6    2024             7      Flinders St  Kensington
7   28388            10        Queens Rd  Kensington
8   36533            13        Queens Rd  Kensington
9    4478           346   Jefcott Street    Glenside
10  52823            19   Jefcott Street    Glenside"""), sep="\s\s+", engine="python")

sheet1 = sheeta.join(
(sheeta.reset_index()  # make a column of pandas index so join can work
     .merge(sheetb, on=["house_number","street","suburb"], how="inner") # find fullmatches
     .set_index("index") # make index same as original sheeta
     .loc[:,["ID"]] # only want ID column to go back into join
     .astype("Int64") # force the types that support int NaN
))

print(sheet1.to_string())

output

   house_number        street      suburb    ID
0            43  Smith Street   Frewville  5509
1            45  Smith Street   Frewville  <NA>
2            47  Smith Street   Frewville  <NA>
3            49  Smith Street   Frewville  <NA>
4            51  Smith Street   Frewville  <NA>
5            53  Smith Street   Frewville  <NA>
6             1   Flinders St  Kensington  <NA>
7             3   Flinders St  Kensington  <NA>
8             5   Flinders St  Kensington  <NA>
9             7   Flinders St  Kensington  2024

2

solved I want a pandas script to line up values from one excel sheet to another based on the values in the first spreadsheet