df.loc with two conditions across two different data source

user_16223060 0
Viewed 88

I have two excel sheets which I am trying to do data processing.

Sheet A

SN    Date(M/D/Y) Values
001   1/1/2021    A
002   1/1/2021    B
003   1/1/2021    C
004   1/1/2021    C

Sheet B

SN    Date
001   2/1/2021
002   12/1/2020
003   1/20/2021

The intent is to filter the results of Sheet A using the values from Sheet B. Specifically there are two conditions:

  1. The SN from Sheet A must exist in Sheet B
  2. The Date on sheet A must be later then Sheet B for that corresponding SN

therefore the expected results should look like this:

SN    Date(M/D/Y) Values
002   1/1/2021    B

Right now I have tried to use this to find it:

value = sheetA.loc[(sheetA['SN'].isin(sheetB['SN'])) & sheetA['Date'] > sheetB['Date']]

However, I am getting ValueError: Can only compare identically-labeled Series objects. How should I go about doing this?

pandas dataframe excel
3 Answers

I think you can merge them and compare the date-like columns. Then retain only rows that satsify the condition (and also drop the Date coming from other one at the end):

merged = sheetA.merge(sheetB)

condition = merged["Date(M/D/Y)"] > merged["Date"]

result = merged[condition].drop(columns="Date")

to get

>>> result

   SN Date(M/D/Y) Values
1   2  2021-01-01      B
user_93321870

Finally solved it thanks to Mustafa Aydin tip on using merge.

merged = sheetA.merge(sheetB, left_on='SN', right_on='SN')
merged = merged.drop(merged[merged['Date(M/D/Y)'] < merged['Date']].index)

The first line merges the two sheets together using the SN as the index. The second line determines the drop condition and drops off the rows where the condition is met.

user_162230600

If you had broken down the code to isolate the problem you would get to:

sheetA['Date'] > sheetB['Date']

being the culprit of the error.

Apparently, it seems that you are trying to compare two Series item-wise and they have different length so pandas does not know how to really compare them.

Ensure the length of both Series is the same and it will work.

user_65739020
Related