r/pythonhelp • • May 09 '22

Spreadsheet Row Matching in Python

Hello! I would be very grateful if someone could take a look at this question and provide help. https://stackoverflow.com/q/72153238/8387181

3 Upvotes

2 comments sorted by

View all comments

1

u/kaleidist May 09 '22

It's impossible to answer your question exactly, because we don't know what the column names are or how the columns are otherwise ordered in your two dataframes.

But, let's say for example that you have the columns "Date", "Color" match in both dataframes, and "Name" dataframe A matches "Fst Name" in dataframe B. That's sort of like your example, except you also have "Animal" and "Number" which don't seem to correspond. I don't know if that's a mistake in your example, but I'll just ignore it for now.

And then you have the "Result" column in both dataframes, but they don't match. You want to change the Result column of dataframe A to be the same as the Result column of Dataframe B, each row corresponding to the rows that match all the relevant corresponding columns.

Because you said that all of the rows in each dataframes correspond to all of the rows in the other, a straightforward way of doing what you want is just to sort the rows each dataframe, reset the index, and then assign the values for the Result column in dataframe A as needed. This assumes you already modified the Date columns to match for both dataframes, as you suggested you've done.

dfa = dfa.sort_values(['Date', 'Name', 'Color']).reset_index()
dfb = dfb.sort_values(['Date', 'Fst Name', 'Color']).reset_index()
dfa['Result'] = dfb['Result']

The list of column names given to the sort method should be the names of whatever minimum set of columns you need to disambiguate the rows within a dataframe, but excluding all of the columns that do not match between the two dataframes, not just the three I used here for example.

A more general solution (this will handle the case where the rows of each dataframe don't all match; and this will run faster as well if you have a large number of columns) would be:

dfb = dfb.rename(columns={'Fst Name': 'Name'})
dfa = dfa.drop('Result', axis=1)
dfa = pd.merge(dfa, dfb, on=['Date', 'Color', 'Name'], how="left")

So we're renaming all the relevant columns in dataframe B so that their names match the names of the corresponding columns in dataframe A. Then we drop the Result column from dataframe A. And then we just do a merge on the dataframes on the matching columns. This still assumes you already modified the Date columns to match for both dataframes, as above.

1

u/Huge-Technician-1516 May 11 '22

I appreciate your reply, thank you so much it is very helpful!