Relate two tables using python and pandas

Question

Hi| I have a sales table that has the cod of the produt and the quantity sold. Then I have another table with the item sold and the price.I would like to create the item and price column on the sales table mathing the item sold. I have tried merge and concatenate but no sucess, problably an apply funtion but I am not getting there.Some help please. Thanks. Sales Table

Try using a mapper similar to the answer here: [Faster way to use linkage dataframes with other dataframes - Python](https://stackoverflow.com/questions/73052433/faster-way-to-use-linkage-dataframes-with-other-dataframes-python) — Michael S., Aug 20 '22 at 02:12

score 3 · Answer 1 · answered Aug 20 '22 at 02:11

3

Try using pd.Series.map:

df1['name_Art price'] = df1['cod_art'].map(df2.set_index(name_art)['price'])

answered Aug 20 '22 at 02:11

Scott Boston

147,308
15
139
187

I understand that this works, but is there a reason why you used a map instead of a join? – Avi Thaker Aug 20 '22 at 02:20
1

@AviThaker Map is much much faster than merge or join when you only want to return one column based on one column for larger dataframes you'll really see the difference. – Scott Boston Aug 20 '22 at 03:02

score 2 · Accepted Answer · answered Aug 20 '22 at 02:23

You need DataFrame.merge.

import pandas as pd

d1 = [
[ 'XT003','15-03-2015',2 ],
[ 'XT001','16-03-2015',3 ],
[ 'AZ005','17-03-2015',1 ],
[ 'ZA006','18-03-2015',1 ],
[ 'XT001','19-03-2015',5 ],
[ 'XT001','20-03-2015',2 ],
[ 'AZ005','21-03-2015',3 ],
[ 'AZ005','22-03-2015',4 ],
[ 'BB008','23-03-2015',5 ],
[ 'BB008','24-03-2015',7 ]
]

d2 = [
[ 'XT001', '120.00' ],
[ 'XT003', '35.00' ],
[ 'AZ005', '20.00' ],
[ 'AZ006', '15.00' ],
[ 'BB008', '230.00' ],
]

df1 = pd.DataFrame( d1, columns=['cod_art','date','QTD'] )
df2 = pd.DataFrame( d2, columns=['name_art','price'] )
print(df1)
print(df2)

df3 = df1.merge( df2, left_on='cod_art', right_on='name_art')
print(df3)

Output:

  cod_art        date  QTD
0   XT003  15-03-2015    2
1   XT001  16-03-2015    3
2   AZ005  17-03-2015    1
3   ZA006  18-03-2015    1
4   XT001  19-03-2015    5
5   XT001  20-03-2015    2
6   AZ005  21-03-2015    3
7   AZ005  22-03-2015    4
8   BB008  23-03-2015    5
9   BB008  24-03-2015    7
  name_art   price
0    XT001  120.00
1    XT003   35.00
2    AZ005   20.00
3    AZ006   15.00
4    BB008  230.00
  cod_art        date  QTD name_art   price
0   XT003  15-03-2015    2    XT003   35.00
1   XT001  16-03-2015    3    XT001  120.00
2   XT001  19-03-2015    5    XT001  120.00
3   XT001  20-03-2015    2    XT001  120.00
4   AZ005  17-03-2015    1    AZ005   20.00
5   AZ005  21-03-2015    3    AZ005   20.00
6   AZ005  22-03-2015    4    AZ005   20.00
7   BB008  23-03-2015    5    BB008  230.00
8   BB008  24-03-2015    7    BB008  230.00

Relate two tables using python and pandas

2 Answers2