Python Forum

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.

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.

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.

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

Output:
Table2			
Ref	DateTime	          Value 	 Result 
1	09/21/2018 17:37	 1,000.00 	 Index1 
2	09/21/2018 17:56	 500.00 	 Index1 
3	09/21/2018 17:56	 500.00 	 Index1 
4	09/21/2018 18:54	 1,000.00 	 Index2 
5	09/21/2018 19:12	 2,000.00 	 Index2 
6	09/21/2018 19:30	 12,000.00 	 No Match 
7	09/21/2018 21:35	 1,000.00 	 Index3 
8	09/21/2018 21:36	 2,000.00 	 Index3 
9	09/21/2018 21:43	 3,000.00 	 Index3