Cumulative totals three different ways in data science

The data analysis field has moved away from querying tools like Crystal Reports (shudder), OLAP cubes, and Excel, to programming languages closer to the raw data.

I primarily work with IPython notebooks, R, and SQL these days, and I thought it would be interesting to look at the differences between these three tools and three ways of thinking through a single task.

Something people often ask of data scientists (or analysts or engineers or what have you) is to just find out how many total customers the company has from the very beginning on a cumulative basis. Often product heads like to see this to figure out if their sexy new product is showing that legendary hockey stick growth.

Often times, this data is only available at a granular level (i.e. you have a base number and the adds every month.) For example, let’s say we want to find out how many total employees each of these Silicon Valley Companies have in any given month. Are they growing as quickly as Hooli?

Here’s the CSV file you’re given to work with: (also available in the associated GitHub repo for all the code in this post.)

Company	Month	New Employees
Hooli	    14-Jan	 123,456
Hooli	    14-Feb	   1,434
Hooli	    14-Mar	   2,455
Pied Piper	14-Jan	       1
Pied Piper	14-Feb	       2
Pied Piper	14-Mar	       2
Raviga	    14-Jan	      50
Raviga	    14-Feb	      -2
Raviga	    14-Mar	      17

But what we really want is this:

Company	Month	New Employees
Hooli	    14-Jan	 123,456
Hooli	    14-Feb	 124,890
Hooli	    14-Mar	 127,345
Pied Piper	14-Jan	       1
Pied Piper	14-Feb	       3
Pied Piper	14-Mar	       5
Raviga	    14-Jan	      50
Raviga	    14-Feb	      48
Raviga	    14-Mar	      65

What I often want to do is create a cumulative total, by month and by company. In Excel, this is super-easy. Just add a new column and add the previous row’s values to it. I’ve shown the formula view:


But, it’s extremely annoying when there’s any more than 100 rows of data, because then I’m resorting to cut and paste and manually tweaking. As soon as I start to do things manually, I make mistakes, and it’s not very good data practice to have code you can’t reproduce.

Doing this kind of thing in more data science-y environments is great because then you have reproducible code you can run over and over again. However, it does take a bit more legwork and requires thinking about the problem in a little bit of a different way than just summing cells.

So here are three typical ways to do cumulative totals in three pretty typical data science environments.

###Cumulative totals in Python

Python, like most programming languages, performs operations over rows of data sequentially, stopping when it hits a new column. (for example, it doesn’t see data as a matrix, but more as individual values. )

This is a “problem” for data people with all languages: Software developers often see data as means to an end (compiling the program). This means data doesn’t need to be organized exactly, just well enough to move through different pipes. Whereas for data people, data is the end goal. (If you’re interested in more about this, I gave a presentation a year or so ago with graphics illustrating the topic here.)

This “problem” (which is really just a feature of programming) is easy to solve with Pandas, which transforms data once again into matrices that need to be operated on in their entirerty. These matrices are called data frames. But it’s a little unwieldy when performing operations on operations on matrices, so you have to do a cumulative sum of a cumulative sum:

###Import Pandas for working with data

import pandas as pd
from pandas import DataFrame
import dateutil.parser as parser

###Read in CSV file

print df
      Company   Month  New Employess
0       Hooli  14-Jan         123456
1       Hooli  14-Feb           1434
2       Hooli  14-Mar           2455
3  Pied Piper  14-Jan              1
4  Pied Piper  14-Feb              2
5  Pied Piper  14-Mar              2
6      Raviga  14-Jan             50
7      Raviga  14-Feb             -2
8      Raviga  14-Mar             17

###Make sure date and number values are rendered correctly from CSV file (the dateutil library) and check each column’s datatype

print df.dtypes
Company                  object
Month            datetime64[ns]
New Employess           float64
dtype: object

###Finally, roll up the cumulative total with two group-bys:

print df.groupby(by=['Company','Month']).sum().groupby(level=[0]).cumsum()

                       New Employess
Company    Month                    
Hooli      2015-01-14         123456
           2015-02-14         124890
           2015-03-14         127345
Pied Piper 2015-01-14              1
           2015-02-14              3
           2015-03-14              5
Raviga     2015-01-14             50
           2015-02-14             48
           2015-03-14             65 

##Cumulative Totals in R

R, in theory, operates on matrices. But mostly, R “thinks about data sets” in columns as opposed to across both rows and columns. In order for it to understand matrices the same way databases do, you need to get the data.table package. (Check out this link for more details.) It’s a little more straightforward than Python because it handles CSV formatting a little better and you don’t need to do as much pre-processing. Like Python, the data.table package has cumulative sum as a built-in function, but there are two steps to organizing the data correctly to be sorted instead of one.

Install the package, read in the csv file, set it as the data table, and set the two keys as the columns you want to group by, then run the cumulative sum function.

 sv <- read.csv("~/Desktop/ipythondata/sv.csv") #read in data
require(data.table) #package for transforming to data table
setDT (sv) #set the table as your dataset
setkey(sv, Company,Month) 
sv[,csum := cumsum(New.Employees),by=c('Company')] 
View(sv) #view your results

And you get:


##Cumulative Totals in SQL

This one is a little trickier because instead of running RStudio or IPython notebooks locally, you have to start a database instance…somewhere. You can, in theory, set up SQLite or MySQL locally, but it’s probably more of a pain than it’s worth. I have a Digital Ocean droplet that has Postgres installed exactly for this kind of tomfoolery. There is a bunch of admin work that will have to be done before you can create tables in Postgres, but then you’re on your way on the command line:

 postgres@data:~$ psql
  postgres=# CREATE SCHEMA employees; 
  postgres=#  CREATE TABLE cumtot(company CHAR(50) NOT NULL, 
                                  month DATE NOT NULL,nemp NUMERIC NOT NULL);

Then take a look at the table that you’ve created: postgres=# \d List of relations Schema | Name | Type | Owner
——–+——–+——-+———- public | cumtot | table | postgres

Then, let’s copy the csv file into the table, instead of creating each row one by one:

postgres=# copy cumtot FROM '/data/sv.csv' DELIMITER ',' CSV HEADER;

In the file, we first have to change the date format because Postgres only takes certain formats

So what we’re importing is:

Pied Piper,2014-Jan-01,1
Pied Piper,2014-Feb-01,2
Pied Piper,2014-Mar-01,2

Check out the table created with the \d command:

    postgres=# \d cumtot
    Table "public.cumtot"
    Column  |     Type      | Modifiers 
    company | character(50) | not null
    month   | date          | not null
    nemp    | numeric       | not null

And now view the contents of the table: (don’t forget the semi-colon…Postgres is pretty picky with syntax):

postgres=# select * from cumtot; 
company         |   month    |  nemp  
Hooli          | 2014-01-01 | 123456
Hooli          | 2014-02-01 |   1434
Hooli          | 2014-03-01 |   2455
Pied Piper     | 2014-01-01 |      1
Pied Piper     | 2014-02-01 |      2
Pied Piper     | 2014-03-01 |      2
Raviga         | 2014-01-01 |     50
Raviga         | 2014-02-01 |     48
Raviga         | 2014-03-01 |     65

That was just the pre-work gruntwork. Now we get to actually do the cumulative total, which requires a window function. Window functions in SQL seem complicated but they’re pretty easy once you get the hang of them. They say, “don’t look at this entire table, look at a portion of the table in a specific order.”

postgres=# SELECT company, month, nemp, sum(nemp) 
OVER (PARTITION BY company ORDER BY month) as cum_tot 
FROM cumtot ORDER BY company, month;

company                 |   month    |  nemp  | cum_tot 
Hooli                    | 2014-01-01 | 123456 |  123456
Hooli                    | 2014-02-01 |   1434 |  124890
Hooli                    | 2014-03-01 |   2455 |  127345
Pied Piper               | 2014-01-01 |      1 |       1
Pied Piper               | 2014-02-01 |      2 |       3
Pied Piper               | 2014-03-01 |      2 |       5
Raviga                   | 2014-01-01 |     50 |      50
Raviga                   | 2014-02-01 |     -2 |      48
Raviga                   | 2014-03-01 |     17 |      65 

And then you’re done.

So that’s pretty much it. Three different approaches to cumulative totals, that will each give you the right answer.

I worked with a tiny dataset that is lightning-fast in memory and very easy to transfer from place to place. The larger your dataset grows, the less you will want to move it. In this case, if you already have the data set up in a SQL database, keep it there and run your window function. It will take MUCH less time than exporting it out as a csv and importing it into either IPython or R. At the size of the data included in this post, it pretty much doesn’t matter which one you use (although creating and importing into a database will take longer.)

###When to use what:

  • Use IPython if you need to do further cleaning to the data after you cumulatively total it, and want to retrace your steps back to the original data.
  • Use R if you need to do simple statistics or regression or charting on the data as-is, and if the data is small.
  • Use SQL if your data’s already in SQL and you need to create more groups from it.
Written on July 8, 2015