i have two data-set and i want to create a dictionary from two columns of Table A, then create a new column in Table B that perform like excel vloopup
Table A
Date Wk of Year ...Other columns
2020-1-1 1
2020-1-2 1
2020-1-10 2
2020-1-11 2
Table B
Shop Date Sales ...Other columns
A 2020-1-1 100
B 2020-1-1 100
C 2020-1-1 100
A 2020-1-10 100
Expected Result
Shop Date Sales Wk of Year
A 2020-1-1 100 1
B 2020-1-1 100 1
C 2020-1-1 100 1
A 2020-1-10 100 2
Code i create the dictionary from Table A
name = pd.to_datetime(Table A['date'])
wk = Table A['Wk of Year']
dict= dict(zip(name,wk))
Table B['wk'] = pd.to_datetime(Table B ['Date'].map(dict)
Actual Result :
Shop Date Sales Wk of Year
A 2020-1-1 100 NaT
B 2020-1-1 100 NaT
C 2020-1-1 100 Nat
A 2020-1-10 100 Nat
Try the pandas merge function and pass the on arg - it is the column on what you want to join your datasets.
Table_merged = pd.merge(Table_B, Table_A['Date', 'Wk_of_Year'], on='Date')
It will create a dataset you expect:
Date Sales Shop Wk_of_Year
0 2020-01-01 100 A 1
1 2020-01-01 100 B 1
2 2020-01-01 100 C 1
3 2020-01-10 100 D 2
But if you still want to use your strategy - use pandas insert function:
date_wk_dct = {key: value for key, value in Table_A[['Date', 'Wk_of_Year']].get_values()}
Table_B.insert(3, "Wk_of_Year", [date_wk_dct[v] for v in iter(Table_B['Date'].get_values())], True)
This will insert the new column in your existing Table_B dataset and the result will be the same:
Date Sales Shop Wk_of_Year
0 2020-01-01 100 A 1
1 2020-01-01 100 B 1
2 2020-01-01 100 C 1
3 2020-01-10 100 D 2