CODEWITHSUNDEEP
python
still_learning
still_learning

Reputation: 806

Merging/Joining datasets

I have two datasets (csv files), A and B for simplicity. A contains all the products; B contains only unique values of products (no duplicates). A sample of A is: 

Date          All_products
01/07/2020          tv
01/07/2020        radio
04/07/2020         pen
04/07/2020         pen
04/07/2020       notebook
04/08/2020         pen
...                ...
04/10/2020        radio

I have approx. 29000 rows

A sample of B is: 

Unique_products     Col1    Col2 ...

tv                  300      24
radio               120      12
pen                 1000      7
notebook            60        2
...                   ....

I have only unique value from A and same other columns (Col1, Col2,...Col 5). Number of rows approx 2000.

I would like to merge the datasets in order to have for each product the values that come from dataset B (csv file): 

 Date          All_products         Col1        Col2
01/07/2020          tv               300         24
01/07/2020        radio              120         12
04/07/2020         pen              1000          7
04/07/2020         pen              1000          7 
04/07/2020       notebook             60          2
04/08/2020         pen              1000          7
...                ...                ...        ...
04/10/2020        radio              120         12

Could you please tell me how to generate the output above? Should I merge/join tables? If yes, how could I do it?

Thank you for your help

Upvotes: 0

Views: 51

Answers (1)

Neo
Neo

Reputation: 492

a = pd.read_csv('a.txt', sep=" ")

enter image description here

b = pd.read_csv('b.txt', sep=" ")
b.rename(columns={'Unique_products':'All_products'}, inplace=True)

enter image description here

ab = pd.merge(a, b, on='All_products', how='left')

enter image description here

Upvotes: 1

Related Questions