I have two tables one with details (Date & Value) & the other with sub totals (Date, SubTotalValue & Referance).
I'm searching for a way to match these two tables by going backward from sub total to detail.
Daily totals values of two tables are equal.
Task is to, select for each day the rows from detail table where the total amount is match with the Amount field of each row in subtotal table.
And then insert the relevent referance field from sub-total table into detail table.
I'm not sure this is possible or not.
My tables are as follows
I'm searching for a way to match these two tables by going backward from sub total to detail.
Daily totals values of two tables are equal.
Task is to, select for each day the rows from detail table where the total amount is match with the Amount field of each row in subtotal table.
And then insert the relevent referance field from sub-total table into detail table.
I'm not sure this is possible or not.
My tables are as follows
import pandas as pd import datetime as dt subtotal = pd.DataFrame(data = {'Date':['01-01-17','01-01-17','02-01-17','02-01-17','03-01-17'], 'Amount':[10000,15000,1000,3000,1000], 'Ref':[1,2,4,3,5]},columns=['Date', 'Amount', 'Ref']) detail = pd.DataFrame(data = {'Date':['01-01-17','01-01-17','01-01-17','01-01-17','02-01-17','02-01-17','02-01-17','02-01-17','03-01-17'], 'Amount':[4000,6000,7000,8000,1500,1500,600,400,1000]}, columns=['Date', 'Amount']) subtotal['Date'] = pd.to_datetime(subtotal['Date']) detail['Date'] = pd.to_datetime(detail['Date'])I'm expecting a result similar to this table.
Output:Date Amount Ref
2017-01-01 4000 1
2017-01-01 6000 1
2017-01-01 7000 2
2017-01-01 8000 2
2017-01-02 1500 3
2017-01-02 1500 3
2017-01-02 600 4
2017-01-02 400 4
2017-01-03 1000 5
Highly appreciate if someone could help me to achive this.