I have two dataframes, one of which is source(src), and the other one is the destination(dest)
dest.tail()
Out[166]:
Item AJ AM AO AR BA BO BR BU BY CA ... TJ TK TR
time ...
2020-06-26 3.5 4.5 5.5 7.5 4.5 7.5 7 NaN 7.0 5.5 ... 7 7.5 3.5
2020-06-29 3.5 4.5 5.5 7.5 4.5 7.5 7 NaN 7.0 5.5 ... 7 7.5 3.5
2020-06-30 3.5 4.5 5.5 7.5 4.5 7.5 7 NaN 7.0 5.5 ... 7 7.5 3.5
2020-07-01 3.5 4.5 5.5 1.5 4.5 7.5 7 NaN 2.5 5.5 ... 7 7.5 3.5
2020-07-02 3.5 4.5 5.5 1.5 4.5 7.5 7 NaN 2.5 5.5 ... 7 7.5 3.5
src.tail()
Out[167]:
1.00 1.25 1.50 1.75 ... 10.00 10.25
time
2020-06-29 0.153556 0.159041 0.162370 0.164580 ... 0.643962 0.658646
2020-06-30 0.156180 0.159280 0.161534 0.163746 ... 0.660171 0.675189
2020-07-01 0.156947 0.163433 0.168326 0.171734 ... 0.687046 0.701364
2020-07-02 0.152465 0.153910 0.154862 0.155750 ... 0.676183 0.690475
2020-07-03 0.154169 0.153923 0.154868 0.155751 ... 0.676537 0.690816
For each value in dest, i want to replace it with a value in the src table, which has same index, and same column name as itself.
e.g. Value for AJ on '2020-06-26' in the dest table right now is 3.5. I want to replace it with value in src table corresponding to index '2020-06-26' and column = 3.5
I was thinking of using applymap, but it doesnt seem to have a concept of index.
dest.applymap(lambda x: src.loc[x.index][x]).tail()
AttributeError: ("'numpy.float64' object has no attribute 'index'", u'occurred at index AJ')
I then tried using apply and it worked like this:
dest1 = dest.replace(0,np.nan).fillna(1) # 0 and nan are not in src.columns
df= dest1.apply(lambda x: [src[col].loc[row] for row, col in zip(x.index,x)], axis=0).tail()
2 questions on this:
- Is there a better solution to this instead of doing a list comprehension within apply?
- Is there a better way of handling values in dest that are not in src.columns (like 0 and nan) so the output is nan when that's the case?
