Compare two dataframes column values. Find which values are in one df and not the other

I have the following dataset

df["OrderYear"] = pd.DatetimeIndex(df['Order Date']).year

I want to compare the customers in 2017 and 2018 and see if the store has lost customers.

I did two subsets corresponding to 2017 and 2018 :

Customer_2018 = df.loc[(df.OrderYear == 2018)]
Customer_2017 = df.loc[(df.OrderYear == 2017)]

I then tried to do this to compare the two :

Churn = Customer_2017['Customer ID'].isin(Customer_2018['Customer ID']).value_counts()

And i get the following output :

True     2206
False     324
Name: Customer ID, dtype: int64

The problem is some customers may appear several times in the dataset since they made several orders.
I would like to get only unique customers (Customer ID is the only unique attribute) and then compare the two dataframes to see how many customers the store lost between 2017 and 2018.

>Solution :

You could just use normal sets to get unique customer ids for each year and then subtract them appropriately:

set_lost_cust = set(Customer_2017["Customer ID"]) - set(Customer_2018["Customer ID"])

Out: 83

For your original approach to work you would need to drop the duplicates from the DataFrames, to make sure each customer appears only a single time:

Customer_2018 = df.loc[(df.OrderYear == 2018), ​"Customer ID"].drop_duplicates()
Customer_2017 = df.loc[(df.OrderYear == 2017), ​"Customer ID"].drop_duplicates()

Churn = Customer_2017.isin(Customer_2018)

True     552
False     83
Name: Customer ID, dtype: int64

Leave a Reply