Python Forum
Match a table with sub-total values to its detail value table
Thread Rating:
  • 2 Vote(s) - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Match a table with sub-total values to its detail value table
#1
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.
Reply
#2
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.
Reply
#3
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Simple linear regression with interaction summary table Andrzej_Andrzej 0 227 Feb-21-2024, 07:44 AM
Last Post: Andrzej_Andrzej
  Extracting tables and text above the table from a PDF to CSV DivAsh 3 2,391 Jan-18-2023, 07:39 AM
Last Post: perfringo
  How to write table to different sheet in openpyxl rsearing 2 2,019 Aug-22-2022, 11:59 PM
Last Post: Pedroski55
  Create a Table to a PNG file amanajosh 0 1,070 Aug-11-2022, 02:54 AM
Last Post: amanajosh
  Chi-square with the contingency table applied to the die Leloup 4 1,938 Mar-16-2022, 12:22 AM
Last Post: jefsummers
  pivot table (excel vs python) Bric 1 1,641 Feb-02-2022, 11:04 PM
Last Post: Bric
Question How does one clean a populated table in MySQL/MariaDB? Copying values across tables? BrandonKastning 2 1,539 Jan-17-2022, 05:46 AM
Last Post: BrandonKastning
  How do I use bounding box to locate a table in a pdf file? JaneTan 1 2,450 Sep-01-2021, 05:44 PM
Last Post: Larz60+
  Identify anomalies in a payment detail dataset for a period klllmmm 1 1,878 Aug-05-2021, 09:41 AM
Last Post: Larz60+
  How to set the font size for a pandas table? AlekseyPython 0 2,297 Feb-06-2021, 03:37 AM
Last Post: AlekseyPython

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020