how to map two dataframes with pandas

Question

I have two excel files

+ File one contains specific data about different customer (like: Sex, Age, Name...) and 
+ File two contains different transactions for each customer

I want to create a new Column in File2 containing specific data to each Costumer from File1

Both the dataframes have a common row, correct? Like customer id — Ninad Gaikwad, Jul 04 '19 at 16:11
```pandas.DataFrame.join``` (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html) — Victor Ruiz, Jul 04 '19 at 16:23
Yes, join or pandas.DataFrame.merge (which is more powerfull, but actually the same if you don't have to deal with indexes while joining). — jottbe, Jul 04 '19 at 16:58
@jottbe I tried **join, **merge, **Append, **concat .... they are not working, because I need to allocate to each Costumer his SEX and AGE every time he appears in the File 2 — Shahine Greene, Jul 04 '19 at 17:09
@ShahineGreene that's what merge and join usually do. But without your data, everything is just plain guessing... It's not even clear, what column[s] can be used to combine the two data frames — jottbe, Jul 04 '19 at 17:39
@politicalscientist I really want to share a sample of the data — Shahine Greene, Jul 04 '19 at 17:40
@ShahineGreene, ok so read your files as dataframes and use `.to_clipboard()` method to update your original post with. — help-ukraine-now, Jul 04 '19 at 17:42

score 1 · Answer 1 · answered Jul 04 '19 at 17:07

file1.csv

customer_id,sex,age,name
af4wf3,m,12,mike
z20ask,f,15,sam

file2.csv

transaction_id,customer_id,amount
12h2j4hk,af4wf3,123.20
12h2j4h1,af4wf3,5.22
12h2j4h2,z20ask,13.20
12h2j4h3,af4wf3,1.20
12h2j4h4,z20ask,2341.12
12h2j4h5,z20ask,235.96
12h2j4h6,af4wf3,999.30

Load and join the dataframes

import pandas as pd

df1 = pd.read_csv('file1.csv')
df2 = pd.read_csv('file2.csv')

df1.set_index('customer_id', inplace=True)
df2.set_index('transaction_id', inplace=True)

output = df2.join(df1, on='customer_id')
output.to_csv('file2_updated.csv')

file2_updated.csv

transaction_id,customer_id,amount,sex,age,name
12h2j4hk,af4wf3,123.2,m,12,mike
12h2j4h1,af4wf3,5.22,m,12,mike
12h2j4h2,z20ask,13.2,f,15,sam
12h2j4h3,af4wf3,1.2,m,12,mike
12h2j4h4,z20ask,2341.12,f,15,sam
12h2j4h5,z20ask,235.96,f,15,sam
12h2j4h6,af4wf3,999.3,m,12,mike

thank you for this code , I tried to re_use it : this message appears : "You are trying to merge on object and int64 columns. If you wish to proceed you should use pd.concat" — Shahine Greene, Jul 04 '19 at 17:27
when I set both index to Costumer_id : a message appears : olumns overlap but no suffix specified — Shahine Greene, Jul 04 '19 at 17:43

help-ukraine-now · Accepted Answer · 2019-07-06T22:10:12.743

0

the same as @jc416 but using pd.merge:

file2.merge(file1, on='customer_id')

    transaction_id  customer_id  amount     sex  age    name
0   12h2j4hk        af4wf3       123.2      m    12     mike
1   12h2j4h1        af4wf3       5.22       m    12     mike
2   12h2j4h3        af4wf3       1.2        m    12     mike
3   12h2j4h6        af4wf3       999.3      m    12     mike
4   12h2j4h2        z20ask       13.2       f    15     sam
5   12h2j4h4        z20ask       2341.12    f    15     sam
6   12h2j4h5        z20ask       235.96     f    15     sam

You definitely should read Pandas merging 101

edited Jul 06 '19 at 22:10

answered Jul 04 '19 at 17:40

help-ukraine-now

3,850
4
19
36

Thank you so Much it works with merge Now. I don't know why it does not before – Shahine Greene Jul 04 '19 at 17:47
@ShahineGreene, glad I could help. Next time you have a question, read this [guide](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) before posting. – help-ukraine-now Jul 04 '19 at 17:49

how to map two dataframes with pandas

2 Answers2