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
>Solution :
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