DEV Community

Robert Breen
Robert Breen

Posted on Originally published at learn.ynteractive.com

Python: Stack Three Monthly Exports Into One Quarter With pandas (Label, concat, groupby, No Copy-Paste)

#python #pandas #beginners #productivity

Most small-business software exports one file per month. Sales, invoices, time sheets: July is one CSV, August is another.

Then the quarterly review comes around, and someone opens all three, pastes them under each other, deletes the header row that got pasted in twice, and hopes nothing was skipped. If the totals look off, they start again.

pandas does that stacking in one line, with no repeated headers and nothing skipped. Here's the version from my free lesson. It runs in Google Colab with a free Google account, nothing to install.

Three small monthly tables

Lakeside Office Supply is a made-up shop with two orders a month. In a new Colab notebook, run import pandas as pd, then in the next cell:

july = pd.DataFrame({'customer': ['Maple Dental', 'Cobalt Legal'], 'amount': [1250, 860]})
august = pd.DataFrame({'customer': ['Cobalt Legal', 'Harbor Accounting'], 'amount': [2310, 640]})
september = pd.DataFrame({'customer': ['Northside Realty', 'Maple Dental'], 'amount': [1890, 980]})
Enter fullscreen mode Exit fullscreen mode

Typing the tables keeps the example small. With real exports, each line is july = pd.read_csv('july.csv') after you upload the files in Colab's Files panel.

Step 1: label each month before you stack 

july['month'] = 'Jul'
august['month'] = 'Aug'
september['month'] = 'Sep'
Enter fullscreen mode Exit fullscreen mode

This is the step people skip. Once three months are in one table, nothing says which row came from which file. A month column with the same value on every row keeps that, so you can still total by month, filter one month, or notice that a month is missing.

Step 2: stack them 

quarter = pd.concat([july, august, september], ignore_index=True)
quarter
Enter fullscreen mode Exit fullscreen mode

Output:

            customer  amount month
0       Maple Dental    1250   Jul
1       Cobalt Legal     860   Jul
2       Cobalt Legal    2310   Aug
3  Harbor Accounting     640   Aug
4   Northside Realty    1890   Sep
5       Maple Dental     980   Sep
Enter fullscreen mode Exit fullscreen mode

pd.concat puts tables with the same columns under each other. ignore_index=True numbers the rows 0 to 5. Leave it out and you get 0, 1, 0, 1, 0, 1, which bites later when you look a row up by its number.

If you need to match two different tables side by side, the way VLOOKUP does, that's merge, not concat.

Step 3: total each month 

quarter.groupby('month', sort=False)['amount'].sum()
Enter fullscreen mode Exit fullscreen mode 
month
Jul    2110
Aug    2950
Sep    2870
Name: amount, dtype: int64
Enter fullscreen mode Exit fullscreen mode

sort=False matters here. By default groupby sorts the groups A to Z, which puts Aug before Jul. I ran it both ways: without sort=False you get Aug 2950, Jul 2110, Sep 2870. The numbers are right, but the review slide reads wrong.

Step 4: total each customer 

quarter.groupby('customer')['amount'].sum()
Enter fullscreen mode Exit fullscreen mode 
customer
Cobalt Legal         3170
Harbor Accounting     640
Maple Dental         2230
Northside Realty     1890
Name: amount, dtype: int64
Enter fullscreen mode Exit fullscreen mode

That's $7,930 for the quarter, and the same total whether you add up the months or the customers. That cross-check is the quickest way to know nothing got lost.

I re-ran every line with pandas 2.2.3 today, and the output matches the lesson.

The quarter totals by customer in Google Colab

Three things that trip people up

  1. The columns must match. If August's export calls it Customer and July's calls it customer, concat makes two columns and fills the gaps with NaN. Rename first: august = august.rename(columns={'Customer': 'customer'}).
  2. Amounts read as text. If the export has $1,250, pandas reads text and sum() glues strings together. Clean it with .str.replace('[$,]', '', regex=True).astype(float) before totaling. Module 3 of the course cleans a messy export like that, plus stray spaces in names and a duplicate row.
  3. Twelve months, not three. Use glob to read every file in a folder and add the month from each file name in a loop. The idea is the same: label, then stack.

Why this beats the spreadsheet

The same six lines work on 6 orders or 60,000. Next quarter you drop in three new files and run the notebook again. Nobody deletes header rows, and the cross-check tells you right away if a month went missing.

Practice it step by step

The lesson, Python: Combine Monthly Sales Exports into One Quarter, has you type every line yourself on real Colab screens, and it checks each step. It's module 2 of the free Python for Spreadsheet Work course, which starts with totaling a sales export by customer. There's also a one-page Python cheat sheet for the whole course.

Practice it step by step, free: https://learn.ynteractive.com/content/python-combine-exports

Top comments (0)