CODEWITHSUNDEEP
pythonpython-3.xpandas
Homesand
Homesand

Reputation: 423

How to join two tables with special conditions

I have two DataFrames with the same features:

 customer_id    Class    value  
 1              A        13  
 2              A        14   
 3              B        15

 customer id    Class    value
 1              A        16
 2              B        17
 3              B        18
 4              A        20

Now I want 1 DataFrame, one including customers with changing their class from A to B

 customer_id    Class    value
 2              B        17

How can I do that?

Upvotes: 2

Views: 62

Answers (2)

jpp
jpp

Reputation: 164753

You can perform a mapping before filtering your second dataframe:

df2['Class_original'] = df2['customer_id'].map(df1.set_index('customer_id')['Class'])

res = df2[(df2['Class_original'] == 'A') & (df2['Class'] == 'B')]

print(res)

   customer_id Class  value Class_original
1            2     B     17              A

Upvotes: 2

Ami Tavory
Ami Tavory

Reputation: 76336

Assuming your DataFrames are df1 and df2, you can merge them on the customers:

before_after = pd.merge(df1, df2, on='customer', how='left', suffixes=('_before', '_after'))

Then check which changed from 'A' to 'B':

before_after[(before_after.id_before == 'A') & (before_after.id_after == 'B')]

Upvotes: 2

Related Questions