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

I have the following dataset

df=pd.read_csv('https://raw.githubusercontent.com/michalis0/DataMining_and_MachineLearning/master/data/sales.csv')

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()
Churn

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.

CodePudding user response：

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"])
len(set_lost_cust)

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)
Churn.value_counts()

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

CodePudding user response：

I would use python sets:

c2017 = set(Customer_2017['Customer ID'])
c2018 = set(Customer_2018['Customer ID'])
print(f'lost customers between 2017 and 2018: {len(c2017 - c2018)}')
print(f'customers from 2017 remaining in 2018: {len(c2017 & c2018)}')
print(f'new customers in 2018: {len(c2018 - c2017)}')

output:

lost customers between 2017 and 2018: 83
customers from 2017 remaining in 2018: 552
new customers in 2018: 138

CodePudding user response：

To go further in the analysis, you can use pd.crosstab:

out = pd.crosstab(df['Customer ID'], df['OrderYear'])

At this point your dataframe looks like:

>>> out
OrderYear    2015  2016  2017  2018
Customer ID                        
AA-10315        4     1     4     2
AA-10375        2     4     4     5
AA-10480        1     0    10     1
AA-10645        6     3     8     1
AB-10015        4     0     2     0  # <- lost customer
...           ...   ...   ...   ...
XP-21865       10     3     9     6
YC-21895        3     1     3     1
YS-21880        0     5     0     7
ZC-21910        5     9     9     8
ZD-21925        3     0     5     1

Now it's easy to get "lost customers":

>>> sum((out[2017] != 0) & (out[2018] == 0))