[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

[ad_1]

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

[ad_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