{"id":72948,"date":"2025-07-10T11:44:15","date_gmt":"2025-07-10T06:14:15","guid":{"rendered":"https:\/\/www.tothenew.com\/blog\/?p=72948"},"modified":"2025-07-14T12:16:13","modified_gmt":"2025-07-14T06:46:13","slug":"from-48-hours-to-6-minutes-my-journey-optimizing-a-pandas-reconciliation-process-for-large-scale-data","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/from-48-hours-to-6-minutes-my-journey-optimizing-a-pandas-reconciliation-process-for-large-scale-data\/","title":{"rendered":"From 48 Hours to 6 Minutes: My Journey Optimizing a Pandas Reconciliation Process for Large-Scale Data"},"content":{"rendered":"

Introduction<\/h2>\n

I recently went through a wild but rewarding ride optimizing a heavy pandas-based reconciliation workflow. What started as a slow, clunky piece of code eating up over 48 hours<\/strong>, ended up getting polished into a lean, mean 6-minutes<\/strong> machine.
\nI wanted to share my story – not as a tutorial, but as a real-world case study from the trenches. If you\u2019ve ever felt your pandas code was taking forever, this post is for you.<\/p>\n

The Problem: Reconciling Huge DataFrames<\/strong><\/h3>\n

I was working with two large pandas DataFrames – let\u2019s call them df1 and df2, and to reconcile under two categories:<\/p>\n

    \n
  1. Exact Match<\/li>\n
  2. Tolerance-Based Match<\/li>\n<\/ol>\n

    The reconciliation keys were mostly PAN, TAN, TAXABLE_VALUE, TDS and DATE, but for the second category, TAXABLE_VALUE & TDS match allowed a tolerance window – not an exact match. The date tolerance was applicable in both categories (i.e., not a strict equality match on date)<\/p>\n

    Here\u2019s how it looked:<\/p>\n

      \n
    • category_1: Grouped by [PAN, TAN, TAXABLE_VALUE, TDS]<\/li>\n
    • category_2: Grouped by just [PAN, TAN], since we wanted more flexibility with TAXABLE_VALUE & TDS tolerance<\/li>\n<\/ul>\n

      A Look into Data Complexity<\/h3>\n

      The data wasn\u2019t just large – it was messy and unbalanced, which made the problem more challenging:<\/p>\n

        \n
      • 3.9 million rows in one DataFrame (df1) and 770,000 rows in the other DataFrame (df2)<\/li>\n
      • Duplicate entries on both sides for the same PAN-TAN combinations<\/li>\n
      • Multiple TANs under a single PAN, not always aligned between df1 and df2<\/li>\n
      • Skewed distribution – some PAN-TAN groups had thousands of rows, others had only a few<\/li>\n<\/ul>\n

        This resulted in combinatorial explosions when matching duplicate groups – especially during tolerance-based reconciliation – and made naive multiprocessing very inefficient.
        \nBelow are samples of how the actual data looks-<\/p>\n

        df1-<\/p>\n\n\n\n\n\n\n\n\n\n
        PAN<\/strong><\/span><\/td>\nTAN<\/strong><\/span><\/td>\nTAXABLE_VALUE<\/strong><\/span><\/td>\nTDS<\/strong><\/span><\/td>\ndate<\/strong><\/span><\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN001<\/td>\n1000<\/td>\n100<\/td>\n2023-04-01<\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN001<\/td>\n1000<\/td>\n100<\/td>\n2023-04-01<\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN002<\/td>\n2000<\/td>\n200<\/td>\n2023-04-05<\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN002<\/td>\n3000<\/td>\n300<\/td>\n2023-04-10<\/td>\n<\/tr>\n
        …<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n
        …<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

        df2-<\/p>\n\n\n\n\n\n\n\n\n
        PAN<\/strong><\/span><\/td>\nTAN<\/strong><\/span><\/td>\nTAXABLE_VALUE<\/strong><\/span><\/td>\nTDS<\/strong><\/span><\/td>\ndate<\/strong><\/span><\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN001<\/td>\n1000<\/td>\n100<\/td>\n2023-04-02<\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN001<\/td>\n999<\/td>\n101<\/td>\n2023-04-03<\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN002<\/td>\n1995<\/td>\n198<\/td>\n2023-04-06<\/td>\n<\/tr>\n
        ABCDE1234F<\/td>\nTAN004<\/td>\n4000<\/td>\n400<\/td>\n2023-04-11<\/td>\n<\/tr>\n
        …<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

         <\/p>\n

        First Attempt<\/h3>\n

        The initial implementation had two separate methods, one for each category. Each ran independently in its own multiprocessing pool. I was relying heavily on .apply(lambda…) for logic like date tolerance, and the performance was… let\u2019s just say, not ideal.<\/p>\n

        def complex_check(row, category):\r\n    .... .... ....\r\n\u00a0 \u00a0 time_delta = (dt.strptime(row[DATE_X], \"%Y-%m-%d\") - dt.strptime(row[DATE_Y], \"%Y-%m-%d\")).days\r\n\r\n\u00a0 \u00a0 if not time_delta or LOWER_BOUND <= time_delta <= UPPER_BOUND:\r\n\u00a0 \u00a0 \u00a0 \u00a0 return category\r\n\u00a0 \u00a0 return \"X\"\r\n<\/pre>\n
        # Here, data and df3 are derived from df1 and df2, respectively.\r\n.... .... ....\r\ndata.set_index(data[KEY_INDEX], inplace=True)\r\ndf3.set_index(df3[KEY_INDEX], inplace=True)\r\ndf3 = pd.merge(data, df3, left_index=True, right_index=True)\r\nif df3.empty:\r\n\u00a0 \u00a0 continue\r\n.... ... ....\r\ndf3[CATEGORY] = df3.apply(lambda record: complex_check(record, category), axis=1)\r\n.... .... ....<\/pre>\n
        Task parallelism is driven by the unique values in the KEY_INDEX column of df1. Let\u2019s explore this further below.<\/p>\n
        .... .... ....\r\ndf1.set_index(df1[KEY_INDEX], inplace=True)\r\ndf2.set_index(df2[KEY_INDEX], inplace=True)\r\njobs = []\r\nwith Pool(processes=ie.n_processes) as pool:\r\n\u00a0 \u00a0 for key_index in df1[KEY_INDEX].unique():\r\n\u00a0 \u00a0 \u00a0 \u00a0 data = df1[df1[KEY_INDEX] == key_index]\r\n\u00a0 \u00a0 \u00a0 \u00a0 df3 = df2[df2[KEY_INDEX] == key_index]\r\n\u00a0 \u00a0 \u00a0 \u00a0 if not data.empty and not df3.empty:\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 jobs.append(pool.apply_async(partial(func, data, df3, category, ... .... ....)))\r\nif jobs:\r\n\u00a0 \u00a0 df = pd.concat([job.get() for job in jobs])\r\n.... .... ....\r\n.... .... ....<\/pre>\n
        Execution Time:\r\nCategory 1: 46.53 hours\r\nCategory 2: 1.40 hours<\/strong><\/pre>\n
        \"Execution
        Execution Flow Diagram: Before Optimization<\/p><\/div>\n
        Refactoring & Optimizations<\/h3>\n
        I knew I had to fix this. So I took a phased approach, breaking down the bottlenecks and tackling them one by one.<\/p>\n
        \"Execution
        Execution Flow Diagram: After Optimization<\/p><\/div>\n
        Case 1: Merging Parallel Workflows<\/strong><\/h3>\n
        Problem<\/strong>: I was running both categories separately, duplicating effort and wasting resources.
        \nFix<\/strong>: Instead of parallelizing the categories, I parallelized each PAN-TAN combination, and then handled both categories together in each process.<\/p>\n
        # Here, we are determining how many tasks will be processed in parallel for the chunks of PAN-TAN.\r\n.... .... ....\r\ngroups_df1 = dict(list(df1.groupby([PAN, TAN])))\r\ngroups_df2 = dict(list(df2.groupby([PAN, TAN])))\r\ncommon_keys = groups_df1.keys() & groups_df2.keys()\r\nmax_workers = int(os.cpu_count() * 0.85)\r\n\r\nmanager = multiprocessing.Manager()\r\nshared_list = manager.list()\r\n\r\ngrouped_task = [\r\n    (shared_list, key, groups_df1.pop(key), groups_df2.pop(key), ..., ..., ....) for key in common_keys\r\n]\r\nwith ProcessPoolExecutor(max_workers=max_workers) as executor:\r\nresults = list(executor.map(process_pan_tan_chunk, grouped_task))\r\n\r\nif results:\r\n\u00a0 \u00a0 .... .... ....\u00a0\r\n\u00a0 \u00a0 .... .... ....\r\ndel results, grouped_task, common_keys, groups_df1, groups_df2\r\n.... .... ....<\/pre>\n
        Result:\r\nExecution time dropped from 46+ hours to 15 hours.\r\nNot amazing, but hey \u2014 progress!<\/strong><\/pre>\n
        Case 2: Ditching .apply() for Vectorization<\/strong><\/h3>\n
        Problem<\/strong>: I was doing too much in .apply(), especially around date tolerance.
        \nFix<\/strong>: I rewrote those pieces using pandas vectorized operations – things like broadcasting ranges and using .merge_asof() instead of row-wise checks.<\/p>\n
        # Here, Vectorized version of .apply(lambda .... ....)\r\n.... .... ....\r\ndf3 = pd.merge(data, df3, left_index=True, right_index=True)\r\nif df3.empty:\r\n  \u00a0 continue\r\n\r\ndf3[\"delta_days\"] = (pd.to_datetime(df3[DATE_X]) - pd.to_datetime(df3[DATE_Y])).dt.days\r\ndf3[CATEGORY] = np.where((df3[\"delta_days\"] >= LOWER_BOUND) & (df3[\"delta_days\"] <= UPPER_BOUND), category, \"X\")\r\n.... .... ....<\/pre>\n
        Result:\r\nBoom - down to 2 hours. That\u2019s a 7x improvement.\r\nVectorization is king in pandas, no kidding.<\/strong><\/pre>\n
        Case 3: Smart Chunking + Resource Balancing<\/strong><\/h3>\n
        Problem<\/strong>: My chunks were uneven. Some processes had massive data loads; others were idle.
        \nFix<\/strong>: I wrote a dynamic chunk manager that split PAN-TAN<\/em> groups more intelligently based on available memory and CPU cores. Now, multiple small chunks could be handled per process without starving or overloading the system.<\/p>\n
        # Here, we finalize the chunk size and decide how many tasks will run in parallel, aiming for as balanced resource usage as possible.\r\n.... .... ....\r\ngrouped_task = []\r\ntemp_df1 = None\r\ntemp_df2 = None\r\ntotal_rows_df1 = sum(groups_df1[k].shape[0] for k in common_keys)\r\ntotal_rows_df2 = sum(groups_df2[k].shape[0] for k in common_keys)\r\ntotal_rows = max(total_rows_df1, total_rows_df2)\r\nchunk_size = min(MIN_CHUCK_SIZE, total_rows \/\/ max_workers)\r\n\r\nfor key in common_keys:\r\n\u00a0 \u00a0 if temp_df1 is None:\r\n\u00a0 \u00a0 \u00a0 \u00a0 temp_df1 = groups_df1.pop(key)\r\n\u00a0 \u00a0 \u00a0 \u00a0 temp_df2 = groups_df2.pop(key)\r\n\u00a0 \u00a0 else:\r\n\u00a0 \u00a0 \u00a0 \u00a0 temp_df1 = pd.concat([temp_df1, groups_df1.pop(key)], ignore_index=True)\r\n\u00a0 \u00a0 \u00a0 \u00a0 temp_df2 = pd.concat([temp_df2, groups_df2.pop(key)], ignore_index=True)\r\n\u00a0 \u00a0 if temp_df1.shape[0]>=chunk_size or temp_df2.shape[0]>=chunk_size:\r\n\u00a0 \u00a0 \u00a0 \u00a0 grouped_task.append((shared_list, key, temp_df1, temp_df2, ..., ...., ....))\r\n\u00a0 \u00a0 \u00a0 \u00a0 temp_df1 = None\r\n\u00a0 \u00a0 \u00a0 \u00a0 temp_df2 = None\r\nif temp_df1 is not None:\r\ngrouped_task.append((shared_list, key, temp_df1, temp_df2, ..., ..., ....))\r\ntemp_df1 = None\r\ntemp_df2 = None\r\n.... .... ....<\/pre>\n
        Result:\r\nExecution time fell to just 6 minutes.\r\nYes, from 48 hours to 6 minutes. And no fancy tools -- just pandas, multiprocessing, and smart engineering.<\/strong><\/pre>\n
        Conclusion<\/h2>\n
          \n
        • Avoid the use of .apply() unless you really have to. It\u2019s intuitive, but slow for big data.<\/li>\n
        • Chunk your data smartly – think in groups, especially if your business logic allows for clean separation (like PAN-TAN in my case).<\/li>\n
        • Don\u2019t over-parallelize – multiprocessing is powerful, but only when you balance memory, I\/O, and CPU smartly.<\/li>\n
        • Merge related logic where possible to avoid redundant data loads<\/li>\n
        • Vectorized code isn’t just faster – it’s cleaner and easier to maintain.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
          Introduction I recently went through a wild but rewarding ride optimizing a heavy pandas-based reconciliation workflow. What started as a slow, clunky piece of code eating up over 48 hours, ended up getting polished into a lean, mean 6-minutes machine. I wanted to share my story – not as a tutorial, but as a real-world […]<\/p>\n","protected":false},"author":2102,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":53},"categories":[5879],"tags":[1197,7553],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/72948"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/2102"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=72948"}],"version-history":[{"count":17,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/72948\/revisions"}],"predecessor-version":[{"id":73239,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/72948\/revisions\/73239"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=72948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=72948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=72948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}