Match a table with sub-total values to its detail value table - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: Match a table with sub-total values to its detail value table (/thread-3308.html) |
Match a table with sub-total values to its detail value table - klllmmm - May-13-2017 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 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. Highly appreciate if someone could help me to achive this.
RE: Match a table with sub-total values to its detail value table - zivoni - May-15-2017 If your subtotals were made as "running sums" (sums of consecutive amounts in same order) of your detail transaction's amounts , then it would be quite easy - you could do running sums on both detail and subtotal and use pd.cut() with label parameter equal to Ref to "split" your detail to appropriate parts. For your data you can split your subtotal/detail dataframe by date (you can use groupby and zip) and then for each date try to find grouping of detail amounts that gives your subtotals. Finding that grouping is not a pandas problem; as it seems like more complicated variation of subset sum, it could be quite demanding for bigger amount of transactions. RE: Match a table with sub-total values to its detail value table - klllmmm - Apr-03-2019 Hi there, I'm still facing this same problem. Subtotals needs to matched with details table and also there can be mismatching items in detail table. import pandas as pd import datetime as dt subtotal = pd.DataFrame(data = {'Date':['21/09/2018 17:45:27','21/09/2018 18:59:24','21/09/2018 20:42:03'], 'Amount':[2000,3000,6000], 'Ref':[1,2,3]},columns=['Date', 'Amount', 'Ref']) detail = pd.DataFrame(data = {'Date':['21/09/2018 17:37:05','21/09/2018 17:56:22','21/09/2018 17:56:53','21/09/2018 18:54:56','21/09/2018 19:12:56','21/09/2018 19:30:30 ','21/09/2018 21:35:59','21/09/2018 21:36:20','21/09/2018 21:43:32 '], 'Amount':[1000,500,500,1000,3000,12000,1000,2000,3000]}, columns=['Date', 'Amount'])My expected result is a table like this
|