CODEWITHSUNDEEP
pythonpython-3.xpandassklearn-pandas
foy
foy

Reputation: 427

Dictionary mapping return Nan

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

Upvotes: 1

Views: 874

Answers (1)

MISISTIK
MISISTIK

Reputation: 26

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

Upvotes: 1

Related Questions