How to merge two pandas dataframes with repeated fields. e.g. customers and sales

user_13492688 0
Viewed 56

I have two dataframes: one for sales other for customers.

I need to create a third dataframe, but using merge doesn't work:

df1 = pd.read_csv('sales.csv', sep=';')
df2 = pd.read_csv('cust.csv', sep=';')

df1.shape = (423413, 21)
df2.shape = (231286, 12)

of course, some customers made more than one purchase. But when I use merge, the dataframe is bigger than the sum of the two previous dataframes and it doesn't matter which method: inner, outer, right, left. Always give a value greater than the sum of the two

data_sum = df1.merge(df2, on='Id_Customer', how='left')
data_sum.shape = (745711, 32)

I've been trying to merge, join, concat and nothing works

Some customers made more than one purchase and customers who never purchased anything. How could I create a new dataframe that looks up the sales table ID, finds the reference in the customers table, and presents the sales and customer data in this?

pandas dataframe python
1 Answers

The number might be the result of processing of the DataFrames, sales without a customer_id and vice versa, etc. As suggested in the comments it is difficult to say without data, but below an example demonstrating that the merge function works as expected.


import pandas as pd

cust = pd.DataFrame({
    'adress': [1, 2, 3, 4, 5],
    'id': [0, 1, 2, 3, 4]
})

sales = pd.DataFrame({
    'nr': [10, 12, 13, 14, 15, 20, 1],
    'id': [0, 1, 2, 3, 4, 0, 10]
})

merge = pd.merge(cust, sales, how='outer')
print(merge)

merge2 = pd.merge(cust, sales, how='inner')
print(merge2)

merge3 = pd.merge(cust, sales, how='left')
print(merge3)

merge4 = pd.merge(cust, sales, how='right')
print(merge4)
user_130957670
Related