Polyglot Data Wrangling

 Posted on March 28, 2019  |  40 minutes  |  8479 words  |  Adel Rahmani

SQL vs Python vs R

In this post we will look a how basic data wrangling operations can be performed in 3 languages commonly used in data science:

  • Structured Query Language (SQL)
  • Python
  • R

Note that while Python and R have obvious similarities in their approach to data wrangling, SQL is designed to work with relational data, where most of the time consuming operations are (ideally) performed on the database side rather than in the client’s memory. Both Python and R can process data in chunks, or even work with huge, distributed data sets through libraries like Dask (Python only) or Apache Spark (Python, R, Scala, Java). However, we’re mostly interested in illustrating some basic data wrangling pipelines in all three languages, so we’ll be using a small database that can easily fit in memory.

The Jupyter notebook allows us to run all our examples in one place. We’ll run an IPython kernel as our base platform and use an IPython magic command to run our R code within the same environment, and we’ll use sqlalchemy to run queries on a postgreSQL data base.

We won’t be covering the basics of each language in this post. There are many excellent resources online. For data wrangling the Python Data Science Handbook is an outstanding book. For R it’s hard to get past the wonderful R for Data Science by Garrett Grolemund and Hadley Wickham. For SQL there are more resources than we can mention, especially once we factor in the various vendor-specific flavours of SQL. One of my personal favourites is SQL Fundamentals for BI by Jeffrey James. Our SQL examples are based on some of the contents of that course.

We start by loading the Python required libraries (we’ll do all our data wrangling using the awesome pandas module) and create a connection to the PostgreSQL database using sqlalchemy. For our examples we’ll be using the well-known dvdrental database which contains 15 tables representing various aspects of a DVD rental business.

# Connect to a SQL (PostgreSQL) database
from sqlalchemy import create_engine
db = 'postgres:///dvdrental'
con = create_engine(db, echo=False)

# Import pandas for data wrangling in python
import pandas as pd
import warnings
warnings.simplefilter('ignore')

# use ipython magic command to load R into our environment.
%load_ext rpy2.ipython

from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))

All our R code will be preceded by %%R command to run it on the R interpreter from within our Jupyter notebook. We start by loading the tidyverse library (which is in fact a collection of libraries), and setting an option to improve how some outputs are rendered in the notebook.

%%R
library(tidyverse)
options(crayon.enabled = FALSE)

Because we’re running on top of an ipython kernel, we’ll write our SQL queries as strings and use pandas to run them on our database via sqlalchemy.

The data

First, let’s see what tables are in our DVD rental database.

q = '''
SELECT * FROM pg_catalog.pg_tables;
'''
df = pd.read_sql_query(q, con)
tables = df[~(df.tablename.str.startswith('pg_')|df.tablename.str.startswith('sql_'))].tablename.values
print(tables)
['category' 'country' 'film_category' 'language' 'inventory' 'actor'
 'staff' 'payment' 'rental' 'store' 'city' 'film' 'address' 'film_actor'
 'customer']

For convenience, let’s create a python dictionary with all the tables as dataframes.

Obviously, this means that we’re loading all the tables into memory, which is not what one would typically want to do with a SQL database, however, since our database is small, and we’re only interested in comparing the wrangling syntax across different languages, this is fine for our purpose.

tbl = {table: pd.read_sql_query(f'SELECT * FROM {table};', 
                                con, parse_dates=['payment_date']) 
       for table in tables}

Let’s look at a few rows of the payment table.

payment = tbl['payment']
payment.head()
payment_id customer_id staff_id rental_id amount payment_date
0 17503 341 2 1520 7.99 2007-02-15 22:25:46.996577
1 17504 341 1 1778 1.99 2007-02-16 17:23:14.996577
2 17505 341 1 1849 7.99 2007-02-16 22:41:45.996577
3 17506 341 2 2829 2.99 2007-02-19 19:39:56.996577
4 17507 341 2 3130 7.99 2007-02-20 17:31:48.996577

We can “pass” the payment dataframe to R in the following way:

%%R -i payment
head(payment)
  payment_id customer_id staff_id rental_id amount        payment_date
0      17503         341        2      1520   7.99 2007-02-15 22:25:46
1      17504         341        1      1778   1.99 2007-02-16 17:23:14
2      17505         341        1      1849   7.99 2007-02-16 22:41:45
3      17506         341        2      2829   2.99 2007-02-19 19:39:56
4      17507         341        2      3130   7.99 2007-02-20 17:31:48
5      17508         341        1      3382   5.99 2007-02-21 12:33:49

Now that we’ve got all the tables loaded, let’s run a few queries.

Let’s start with something simple.

Which movie rentals were below a dollar for customers with an id above 500?

▶ SQL

The query is nothing fancy here. We’re selecting a few features (columns) from the payment table, filtering on the dollar amount and customer id before ordering the rows.

q = '''
SELECT 
    p.payment_id, p.customer_id, p.amount 
FROM 
    payment p
WHERE 
    p.amount < 1 AND p.customer_id > 500
ORDER BY 1 ASC, 2 DESC, 3 ASC;
'''
df_sql = pd.read_sql_query(q, con)     # run the query on the database
print(df_sql.shape)                    # output the shape of the resulting table
df_sql.head()                          # display the first few rows
(413, 3)
payment_id customer_id amount
0 18121 502 0.99
1 18122 502 0.99
2 18125 503 0.99
3 18134 506 0.99
4 18144 510 0.99

▶ Python

Pandas allow us to chain methods on a dataframe, however, writing everything on a single line is cumbersome for long pipelines.

We can write each step of the pipeline on its own line by wrapping the pipeline in brackets.

df = (payment[(payment.amount < 1) & (payment.customer_id > 500)]  # select rows based on conditions
         .loc[:, ['payment_id','customer_id','amount']]            # select 3 columns
         .sort_values(by=['payment_id', 'customer_id', 'amount'],  # sort the rows according to the values  
                      ascending=[True, False, True])               #      in the columns
         .reset_index(drop=True)                                   # not really required but looks nicer
        )
print(df.shape)
df.head()
(413, 3)
payment_id customer_id amount
0 18121 502 0.99
1 18122 502 0.99
2 18125 503 0.99
3 18134 506 0.99
4 18144 510 0.99

▶ R

For this simple query the R code is similar to the Python one. The pipe symbol %>% is equivalent to the dot symbol in pandas which allows us to chain methods together.

The %%R -o df_r part of the code is a way to transfer the R dataframe back to our Python interpreter.

%%R -o df_r   
df_r = payment %>% 
        filter(amount < 1, customer_id > 500) %>% 
        select(payment_id, customer_id, amount) %>% 
        arrange(payment_id, desc(customer_id), amount)
df_r.shape
df_r.head()
payment_id customer_id amount
0 18121 502 0.99
1 18122 502 0.99
2 18125 503 0.99
3 18134 506 0.99
4 18144 510 0.99

Sanity check

Just to make sure that we’ve run our query correctly, we can compare the result from the SQL query with the results of the Python and R pipelines. Because of the fact that R indexing starts at 1 instead of 0, we need to reset the index of the R dataframe after we export it to Python.

all(df==df_sql), all(df_r==df_sql)
(True, True)

Any other trivial query could be handled in a similar way…

Let’s look at a query where we need to extract a feature that doesn’t appear explicitly in the original data.

Which transactions occured in the month of March only?

The payment table has a payment_date feature from which we will need to extract the month.

▶ SQL

We can extract the month value out of the payment_date column using the EXTRACT function.

q = '''
SELECT 
    p.payment_id, p.customer_id cust_id, p.amount, p.payment_date
FROM 
    payment p
WHERE 
    EXTRACT(month FROM p.payment_date) = 3
    AND p.amount < 1
ORDER BY 
    cust_id DESC, 3 ASC;
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql.head()
(1021, 4)
payment_id cust_id amount payment_date
0 22611 598 0.99 2007-03-02 15:51:25.996577
1 22613 598 0.99 2007-03-21 07:09:41.996577
2 22606 597 0.99 2007-03-19 05:51:32.996577
3 22589 596 0.99 2007-03-01 20:50:37.996577
4 22598 596 0.99 2007-03-22 21:49:07.996577

▶ Python

When we loaded the payment table from the database using sqlalchemy, the payment_date feature was correctly parsed as a datetime object.

This allows us to use the pandas dt accessor to extract a variety of date and time related features, including the month.

df = (payment[(payment.amount < 1) & (payment.payment_date.dt.month==3)]
       .rename({'customer_id': 'cust_id'}, axis=1)
       .reindex(['payment_id','cust_id','amount','payment_date'], axis=1)
       .sort_values(['cust_id', 'amount'], ascending=[False, True])
       .reset_index(drop=True)
     )
      
print(df.shape)
df.head()
(1021, 4)
payment_id cust_id amount payment_date
0 22611 598 0.99 2007-03-02 15:51:25.996577
1 22613 598 0.99 2007-03-21 07:09:41.996577
2 22606 597 0.99 2007-03-19 05:51:32.996577
3 22589 596 0.99 2007-03-01 20:50:37.996577
4 22592 596 0.99 2007-03-17 15:08:26.996577

▶ R

A great way to handle datetime objects in R is via the lubridate library. To make it clear when we’re using a lubridate object, we won’t load the library, but we call its functions using the lubridate:: prefix.

%%R -o df_r
df_r = payment %>% 
        filter(amount < 1, lubridate::month(payment$payment_date)==3) %>% 
        rename(cust_id=customer_id) %>% 
        select(-rental_id, -staff_id) %>% 
        arrange(desc(cust_id), amount)
print(df_r.shape)
df_r.head()
(1021, 4)
payment_id cust_id amount payment_date
0 22611 598 0.99 2007-03-02 15:51:25+11:00
1 22613 598 0.99 2007-03-21 07:09:41+11:00
2 22606 597 0.99 2007-03-19 05:51:32+11:00
3 22589 596 0.99 2007-03-01 20:50:37+11:00
4 22592 596 0.99 2007-03-17 15:08:26+11:00

Here we get an glimpse into the infinite joy of working with datetime objects.

During the transfer from Python to R and back to Python, our payment_date format has changed slightly.

Instead of worrying about fixing this we’ll just exclude that column when comparing the R result with the SQL/Python ones.

Henceforth, we won’t be worrying about this issue. We’ll simply check that the dataframes shape match and the first few rows are consistent.

all(df==df_sql), all(df.iloc[:, :-1]==df_r.iloc[:, :-1])
(True, True)

Which day saw the most business (largest number of transactions)?

This is a common operation. We need to group our payments by date, count how many transactions occured on that date and order the results accordingly.

▶ SQL

q = '''
SELECT 
    p.payment_date::date, COUNT(*)
FROM 
    payment p
GROUP BY 1
ORDER BY 2 DESC
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql
(32, 2)
payment_date count
0 2007-04-30 1311
1 2007-03-01 676
2 2007-03-21 673
3 2007-04-27 643
4 2007-04-29 640
5 2007-03-19 631
6 2007-04-28 627
7 2007-03-18 624
8 2007-03-22 621
9 2007-03-20 611
10 2007-03-02 595
11 2007-03-17 584
12 2007-03-23 557
13 2007-04-08 516
14 2007-04-09 514
15 2007-04-06 486
16 2007-04-10 482
17 2007-04-07 472
18 2007-04-11 468
19 2007-04-12 452
20 2007-02-19 310
21 2007-02-15 308
22 2007-02-18 302
23 2007-02-20 291
24 2007-02-17 283
25 2007-02-16 282
26 2007-02-21 213
27 2007-05-14 182
28 2007-04-26 79
29 2007-03-16 72
30 2007-04-05 64
31 2007-02-14 27

▶ Python

The Python pipeline mimics the SQL query rather closely.

df = (payment
       .groupby(payment.payment_date.dt.date)['amount']
       .count().rename('count')
       .sort_values(ascending=False)
       .reset_index()
     )
df
payment_date count
0 2007-04-30 1311
1 2007-03-01 676
2 2007-03-21 673
3 2007-04-27 643
4 2007-04-29 640
5 2007-03-19 631
6 2007-04-28 627
7 2007-03-18 624
8 2007-03-22 621
9 2007-03-20 611
10 2007-03-02 595
11 2007-03-17 584
12 2007-03-23 557
13 2007-04-08 516
14 2007-04-09 514
15 2007-04-06 486
16 2007-04-10 482
17 2007-04-07 472
18 2007-04-11 468
19 2007-04-12 452
20 2007-02-19 310
21 2007-02-15 308
22 2007-02-18 302
23 2007-02-20 291
24 2007-02-17 283
25 2007-02-16 282
26 2007-02-21 213
27 2007-05-14 182
28 2007-04-26 79
29 2007-03-16 72
30 2007-04-05 64
31 2007-02-14 27

▶ R

We follow the same pattern for our R code. The conversion to a dataframe at the end (using as.data.frame) is simply to get a nicer formatting of the output.

%%R 
df_r = payment %>% 
        group_by(payment_date=lubridate::date(payment$payment_date)) %>% 
        summarise(n = n()) %>% 
        arrange(desc(n)) %>%
        as.data.frame()
df_r
   payment_date    n
1    2007-04-30 1311
2    2007-03-01  676
3    2007-03-21  673
4    2007-04-27  643
5    2007-04-29  640
6    2007-03-19  631
7    2007-04-28  627
8    2007-03-18  624
9    2007-03-22  621
10   2007-03-20  611
11   2007-03-02  595
12   2007-03-17  584
13   2007-03-23  557
14   2007-04-08  516
15   2007-04-09  514
16   2007-04-06  486
17   2007-04-10  482
18   2007-04-07  472
19   2007-04-11  468
20   2007-04-12  452
21   2007-02-19  310
22   2007-02-15  308
23   2007-02-18  302
24   2007-02-20  291
25   2007-02-17  283
26   2007-02-16  282
27   2007-02-21  213
28   2007-05-14  182
29   2007-04-26   79
30   2007-03-16   72
31   2007-04-05   64
32   2007-02-14   27

How much did each customer spend and when?

We can answer this question in a number of ways. Let’s return a sequence of transaction dates for each customer. In SQL we can do that with an array.

▶ SQL

q = '''
SELECT 
    p.customer_id, 
    SUM(p.amount) total, 
    ARRAY_AGG(p.payment_date::date) dates
FROM 
    payment p
GROUP BY 1
ORDER BY 2 DESC
'''

df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql.head()
(599, 3)
customer_id total dates
0 148 211.55 [2007-02-15, 2007-02-15, 2007-02-19, 2007-02-1...
1 526 208.58 [2007-02-15, 2007-02-16, 2007-02-17, 2007-02-1...
2 178 194.61 [2007-02-15, 2007-02-15, 2007-02-16, 2007-02-1...
3 137 191.62 [2007-02-18, 2007-02-19, 2007-02-20, 2007-02-2...
4 144 189.60 [2007-02-16, 2007-02-17, 2007-02-19, 2007-02-2...

▶ Python

In Python we’ll gather the transaction dates for each customer as a list.

We can build that list using a lambda (anonymous) function during the aggregation step.

df = (payment
       .groupby('customer_id')[['amount', 'payment_date']]
       .agg({
               'amount':'sum',
               'payment_date': lambda x: list(x.dt.date)
            })
       .rename(columns={'amount':'total', 'payment_date': 'date'})
       .sort_values('total', ascending=False)
       .reset_index()
      )
print(df.shape)
df.head()
(599, 3)
customer_id total date
0 148 211.55 [2007-02-15, 2007-02-15, 2007-02-19, 2007-02-1...
1 526 208.58 [2007-02-15, 2007-02-16, 2007-02-17, 2007-02-1...
2 178 194.61 [2007-02-15, 2007-02-15, 2007-02-16, 2007-02-1...
3 137 191.62 [2007-02-18, 2007-02-19, 2007-02-20, 2007-02-2...
4 144 189.60 [2007-02-16, 2007-02-17, 2007-02-19, 2007-02-2...

▶ R

For this query the R code is similar to the Python one (syntactic idiosyncrasies aside).

%%R 
df_r = payment %>% 
      mutate(payment_date = as.Date(payment_date)) %>% 
      group_by(customer_id) %>% 
      summarise(total=sum(amount), dates=list(payment_date)) %>% 
      arrange(desc(total))

print(dim(df_r))
print(head(df_r, 5))
[1] 599   3
# A tibble: 5 x 3
  customer_id total dates      
        <int> <dbl> <list>     
1         148  212. <date [45]>
2         526  209. <date [42]>
3         178  195. <date [39]>
4         137  192. <date [38]>
5         144  190. <date [40]>

At first glance it looks like the results are different from the SQL/Python, but this is purely cosmetic. The R data structure (a tibble actually) rounds up the floating point values and shrinks the lists when displaying the results. We could convert the tibble to a R dataframe but the output would be unwieldy. Instead, we can unpack the first row to show that the data has been parsed correctly.

%%R
print(df_r$total[1]) 
print(df_r$dates[1])
[1] 211.55
[[1]]
 [1] "2007-02-15" "2007-02-15" "2007-02-19" "2007-02-19" "2007-02-19"
 [6] "2007-03-01" "2007-03-02" "2007-03-17" "2007-03-17" "2007-03-18"
[11] "2007-03-18" "2007-03-18" "2007-03-20" "2007-03-20" "2007-03-20"
[16] "2007-03-21" "2007-03-21" "2007-03-21" "2007-03-21" "2007-03-22"
[21] "2007-03-22" "2007-03-22" "2007-03-22" "2007-04-05" "2007-04-06"
[26] "2007-04-08" "2007-04-08" "2007-04-08" "2007-04-09" "2007-04-10"
[31] "2007-04-11" "2007-04-12" "2007-04-27" "2007-04-27" "2007-04-27"
[36] "2007-04-28" "2007-04-28" "2007-04-28" "2007-04-29" "2007-04-29"
[41] "2007-04-29" "2007-04-29" "2007-04-30" "2007-04-29" "2007-04-30"

Which days of March 2007 recorded no sale?

This may seem like the sort of query we’ve already looked at, however there is a catch. The database only records transactions that have occurred (it seems obvious when said like that!). This means that dates with no sales aren’t in the database. Therefore we need to generate a series of dates (with a daily frequency) and join our payment data on it to find the days with no sales.

In PostgreSQL we can use generate_series to do that. Note that we select from it as if it were a table (which we name gs). By joining on it we can “expand” our payment data to include the dates for which no transaction took place. These would of course contain NULL values for the transaction data. By identifying where those NULL rows are we can answer the original question.

▶ SQL

q = '''
SELECT 
    gs::date, p.*
FROM 
    generate_series('2007-03-01', '2007-03-31', INTERVAL '1 Day') gs
LEFT JOIN 
    payment p 
ON 
    p.payment_date::date = gs::date
WHERE 
    payment_date is NULL
'''
df_sql = pd.read_sql_query(q, con).fillna('')['gs']
print(df_sql.shape)
df_sql
(21,)
0     2007-03-03
1     2007-03-04
2     2007-03-05
3     2007-03-06
4     2007-03-07
5     2007-03-08
6     2007-03-09
7     2007-03-10
8     2007-03-11
9     2007-03-12
10    2007-03-13
11    2007-03-14
12    2007-03-15
13    2007-03-24
14    2007-03-25
15    2007-03-26
16    2007-03-27
17    2007-03-28
18    2007-03-29
19    2007-03-30
20    2007-03-31
Name: gs, dtype: object

▶ Python

We could do something similar in Python, however let’s try a more pythonic approach and use set operations to extract the dates we want. We simply construct the set of all dates in March 2007 and take the difference with the dates that are already in the database. The moral of the story here is that whereas the Python (and R) syntax can often look SQLish, this is not always the most concise or even suitable way to get to the answer.

gs = pd.date_range(start='20070301', end='20070331', freq='D')
df = pd.DataFrame(index = set(gs.date) - set(payment.payment_date.dt.date.values)).sort_index()
print(df.shape)
df
(21, 0)
2007-03-03
2007-03-04
2007-03-05
2007-03-06
2007-03-07
2007-03-08
2007-03-09
2007-03-10
2007-03-11
2007-03-12
2007-03-13
2007-03-14
2007-03-15
2007-03-24
2007-03-25
2007-03-26
2007-03-27
2007-03-28
2007-03-29
2007-03-30
2007-03-31

▶ R

Set operations also work in R. The output is a bit strange. We have a list of lists with a single element. We could flatten the nested list but it would only obfuscate the code so we’ll leave it like that.

%%R
gs = seq(lubridate::ymd('2007-03-01'), lubridate::ymd('2007-03-31'), by = '1 day')
lapply(sort(setdiff(gs, lubridate::date(payment$payment_date))), as.Date, origin='1970-01-01')
[[1]]
[1] "2007-03-03"

[[2]]
[1] "2007-03-04"

[[3]]
[1] "2007-03-05"

[[4]]
[1] "2007-03-06"

[[5]]
[1] "2007-03-07"

[[6]]
[1] "2007-03-08"

[[7]]
[1] "2007-03-09"

[[8]]
[1] "2007-03-10"

[[9]]
[1] "2007-03-11"

[[10]]
[1] "2007-03-12"

[[11]]
[1] "2007-03-13"

[[12]]
[1] "2007-03-14"

[[13]]
[1] "2007-03-15"

[[14]]
[1] "2007-03-24"

[[15]]
[1] "2007-03-25"

[[16]]
[1] "2007-03-26"

[[17]]
[1] "2007-03-27"

[[18]]
[1] "2007-03-28"

[[19]]
[1] "2007-03-29"

[[20]]
[1] "2007-03-30"

[[21]]
[1] "2007-03-31"

How many transactions were there for each day of March 2007?

This is a simple question, we just need to keep in mind that some days had no transaction. We should still ouput those with a count of zero. The basic idea is similar to the previous example. We create a time series to “pad out” the missing days in the payment_date feature.

▶ SQL

q = '''
SELECT 
    gs::date date, 
    COUNT(p.*) number_of_transactions
FROM 
    generate_series('2007-03-01', '2007-03-31', INTERVAL '1 Day') gs
LEFT JOIN 
    payment p 
ON 
    p.payment_date::date = gs::date 
GROUP BY 
    gs::date 
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql
(31, 2)
date number_of_transactions
0 2007-03-01 676
1 2007-03-02 595
2 2007-03-03 0
3 2007-03-04 0
4 2007-03-05 0
5 2007-03-06 0
6 2007-03-07 0
7 2007-03-08 0
8 2007-03-09 0
9 2007-03-10 0
10 2007-03-11 0
11 2007-03-12 0
12 2007-03-13 0
13 2007-03-14 0
14 2007-03-15 0
15 2007-03-16 72
16 2007-03-17 584
17 2007-03-18 624
18 2007-03-19 631
19 2007-03-20 611
20 2007-03-21 673
21 2007-03-22 621
22 2007-03-23 557
23 2007-03-24 0
24 2007-03-25 0
25 2007-03-26 0
26 2007-03-27 0
27 2007-03-28 0
28 2007-03-29 0
29 2007-03-30 0
30 2007-03-31 0

▶ Python

Similarly, in Python we can create the series of dates for March 2007 using the date_range object from pandas.

gs = pd.date_range(start='20070301', end='20070331', freq='D')
df = (payment
       .assign(payment_date=pd.to_datetime(payment.payment_date).dt.date)
       .groupby('payment_date')
       .agg({'amount':'count'})
       .reindex(gs, fill_value=0)
       .reset_index()
       .rename(columns={'index':'date','amount':'number_of_transactions'})
)

print(df.shape)
df
(31, 2)
date number_of_transactions
0 2007-03-01 676
1 2007-03-02 595
2 2007-03-03 0
3 2007-03-04 0
4 2007-03-05 0
5 2007-03-06 0
6 2007-03-07 0
7 2007-03-08 0
8 2007-03-09 0
9 2007-03-10 0
10 2007-03-11 0
11 2007-03-12 0
12 2007-03-13 0
13 2007-03-14 0
14 2007-03-15 0
15 2007-03-16 72
16 2007-03-17 584
17 2007-03-18 624
18 2007-03-19 631
19 2007-03-20 611
20 2007-03-21 673
21 2007-03-22 621
22 2007-03-23 557
23 2007-03-24 0
24 2007-03-25 0
25 2007-03-26 0
26 2007-03-27 0
27 2007-03-28 0
28 2007-03-29 0
29 2007-03-30 0
30 2007-03-31 0

▶ R

Once again, the pattern for R follows the same principles.

%%R 
gs = data_frame(payment_date=seq(lubridate::ymd('2007-03-01'), 
                                 lubridate::ymd('2007-03-31'), 
                                 by = '1 day'))

df_r = payment %>% 
      mutate(payment_date=lubridate::date(payment_date)) %>% 
      group_by(payment_date) %>% 
      summarise(number_of_transactions=n()) %>% 
      right_join(gs, by='payment_date') %>%
      replace_na(list(number_of_transactions=0))

as.data.frame(df_r)
   payment_date number_of_transactions
1    2007-03-01                    676
2    2007-03-02                    595
3    2007-03-03                      0
4    2007-03-04                      0
5    2007-03-05                      0
6    2007-03-06                      0
7    2007-03-07                      0
8    2007-03-08                      0
9    2007-03-09                      0
10   2007-03-10                      0
11   2007-03-11                      0
12   2007-03-12                      0
13   2007-03-13                      0
14   2007-03-14                      0
15   2007-03-15                      0
16   2007-03-16                     72
17   2007-03-17                    584
18   2007-03-18                    624
19   2007-03-19                    631
20   2007-03-20                    611
21   2007-03-21                    673
22   2007-03-22                    621
23   2007-03-23                    557
24   2007-03-24                      0
25   2007-03-25                      0
26   2007-03-26                      0
27   2007-03-27                      0
28   2007-03-28                      0
29   2007-03-29                      0
30   2007-03-30                      0
31   2007-03-31                      0

Days with no transactions in March 2007 - Alternate version

Let’s revisit the problem we dealt with previous using set operations in Python and R and illustrate how we could answer the question using a more SQLish pipeline. We can reuse the code from the previous example. We just need to add a filtering step.

▶ SQL

q = '''
SELECT 
    gs::date date, COUNT(p.*) number_of_transactions
FROM 
    generate_series('2007-03-01', '2007-03-31', INTERVAL '1 Day') gs
LEFT JOIN 
    payment p 
ON 
    p.payment_date::date = gs::date 
GROUP BY 
    gs::date 
HAVING 
    COUNT(p.*) = 0
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql
(21, 2)
date number_of_transactions
0 2007-03-03 0
1 2007-03-04 0
2 2007-03-05 0
3 2007-03-06 0
4 2007-03-07 0
5 2007-03-08 0
6 2007-03-09 0
7 2007-03-10 0
8 2007-03-11 0
9 2007-03-12 0
10 2007-03-13 0
11 2007-03-14 0
12 2007-03-15 0
13 2007-03-24 0
14 2007-03-25 0
15 2007-03-26 0
16 2007-03-27 0
17 2007-03-28 0
18 2007-03-29 0
19 2007-03-30 0
20 2007-03-31 0

▶ Python

gs = pd.date_range(start='20070301', end='20070331', freq='D')
df = (payment
       .assign(payment_date=pd.to_datetime(payment.payment_date).dt.date)
       .groupby('payment_date')
       .agg({'amount':'count'})
       .rename(columns={'amount':'number_of_transactions'})
       .reindex(gs, fill_value=0)
       .query('number_of_transactions == 0')  
)

print(df.shape)
df
(21, 1)
number_of_transactions
2007-03-03 0
2007-03-04 0
2007-03-05 0
2007-03-06 0
2007-03-07 0
2007-03-08 0
2007-03-09 0
2007-03-10 0
2007-03-11 0
2007-03-12 0
2007-03-13 0
2007-03-14 0
2007-03-15 0
2007-03-24 0
2007-03-25 0
2007-03-26 0
2007-03-27 0
2007-03-28 0
2007-03-29 0
2007-03-30 0
2007-03-31 0

▶ R

%%R
gs = data_frame(payment_date=seq(lubridate::ymd('2007-03-01'), 
                                 lubridate::ymd('2007-03-31'), 
                                 by = '1 day'))

df_r = payment %>% 
      mutate(payment_date=lubridate::date(payment_date)) %>% 
      group_by(payment_date) %>% 
      summarise(number_of_transactions=n()) %>% 
      right_join(gs, by="payment_date") %>%
      replace_na(list(number_of_transactions=0)) %>%
      subset(number_of_transactions==0)

print(dim(df_r))
as.data.frame(df_r)
[1] 21  2
   payment_date number_of_transactions
1    2007-03-03                      0
2    2007-03-04                      0
3    2007-03-05                      0
4    2007-03-06                      0
5    2007-03-07                      0
6    2007-03-08                      0
7    2007-03-09                      0
8    2007-03-10                      0
9    2007-03-11                      0
10   2007-03-12                      0
11   2007-03-13                      0
12   2007-03-14                      0
13   2007-03-15                      0
14   2007-03-24                      0
15   2007-03-25                      0
16   2007-03-26                      0
17   2007-03-27                      0
18   2007-03-28                      0
19   2007-03-29                      0
20   2007-03-30                      0
21   2007-03-31                      0

Which movies have never been rented?

So far we’ve only worked with a single table out of the 15 contained in the database. To match movies to rental transactions we will need to use 3 new tables. The film one contains information about the movies, the inventory one links the movies to an inventory id, which is used in the rental table to connect movies to actual rentals.

Let’s have a look at the structures of these tables.

for t in ['inventory', 'payment', 'rental', 'film']:
    q = f'''
SELECT * FROM {t}
LIMIT 1
'''
    df = pd.read_sql_query(q, con)
    print(t)
    display(df)
inventory
inventory_id film_id store_id last_update
0 1 1 1 2006-02-15 10:09:17 
payment
payment_id customer_id staff_id rental_id amount payment_date
0 17503 341 2 1520 7.99 2007-02-15 22:25:46.996577 
rental
rental_id rental_date inventory_id customer_id return_date staff_id last_update
0 2 2005-05-24 22:54:33 1525 459 2005-05-28 19:40:33 1 2006-02-16 02:30:53 
film
film_id title description release_year language_id rental_duration rental_rate length replacement_cost rating last_update special_features fulltext
0 133 Chamber Italian A Fateful Reflection of a Moose And a Husband ... 2006 1 7 4.99 117 14.99 NC-17 2013-05-26 14:50:58.951 [Trailers] 'chamber':1 'fate':4 'husband':11 'italian':2 ...

▶ SQL

Let’s join the tables, count how many times each movie has been rented and select those for which the count is zero.

q = '''
SELECT 
    t.film_id, t.title, t.rentals 
FROM 
    (SELECT 
        f.film_id, f.title, 
        COUNT(distinct r.rental_id) as rentals
    FROM film f
        LEFT JOIN inventory i ON i.film_id = f.film_id
        LEFT JOIN rental r ON r.inventory_id = i.inventory_id
    GROUP BY 1,2
    HAVING 
        COUNT(distinct r.rental_id) = 0
        ) t
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql
(42, 3)
film_id title rentals
0 14 Alice Fantasia 0
1 33 Apollo Teen 0
2 36 Argonauts Town 0
3 38 Ark Ridgemont 0
4 41 Arsenic Independence 0
5 87 Boondock Ballroom 0
6 108 Butch Panther 0
7 128 Catch Amistad 0
8 144 Chinatown Gladiator 0
9 148 Chocolate Duck 0
10 171 Commandments Express 0
11 192 Crossing Divorce 0
12 195 Crowds Telemark 0
13 198 Crystal Breaking 0
14 217 Dazed Punk 0
15 221 Deliverance Mulholland 0
16 318 Firehouse Vietnam 0
17 325 Floats Garden 0
18 332 Frankenstein Stranger 0
19 359 Gladiator Westward 0
20 386 Gump Date 0
21 404 Hate Handicap 0
22 419 Hocus Frida 0
23 495 Kentuckian Giant 0
24 497 Kill Brotherhood 0
25 607 Muppet Mile 0
26 642 Order Betrayed 0
27 669 Pearl Destiny 0
28 671 Perdition Fargo 0
29 701 Psycho Shrunk 0
30 712 Raiders Antitrust 0
31 713 Rainbow Shock 0
32 742 Roof Champion 0
33 801 Sister Freddy 0
34 802 Sky Miracle 0
35 860 Suicides Silence 0
36 874 Tadpole Park 0
37 909 Treasure Command 0
38 943 Villain Desperate 0
39 950 Volume House 0
40 954 Wake Jaws 0
41 955 Walls Artist 0

▶ Python

The go to approach for join operations in pandas is the merge method. The type of join can be specified as an optional parameter.

film, inventory, rental = tbl['film'], tbl['inventory'], tbl['rental']

df = (film
        .merge(inventory, on='film_id', how='left')
        .merge(rental, on='inventory_id', how='left')
        .groupby(['film_id','title'])[['rental_id']]
        .count()
        .rename(columns={'rental_id':'rentals'})
        .query('rentals == 0')
        .reset_index()
     )
print(df.shape)
df
(42, 3)
film_id title rentals
0 14 Alice Fantasia 0
1 33 Apollo Teen 0
2 36 Argonauts Town 0
3 38 Ark Ridgemont 0
4 41 Arsenic Independence 0
5 87 Boondock Ballroom 0
6 108 Butch Panther 0
7 128 Catch Amistad 0
8 144 Chinatown Gladiator 0
9 148 Chocolate Duck 0
10 171 Commandments Express 0
11 192 Crossing Divorce 0
12 195 Crowds Telemark 0
13 198 Crystal Breaking 0
14 217 Dazed Punk 0
15 221 Deliverance Mulholland 0
16 318 Firehouse Vietnam 0
17 325 Floats Garden 0
18 332 Frankenstein Stranger 0
19 359 Gladiator Westward 0
20 386 Gump Date 0
21 404 Hate Handicap 0
22 419 Hocus Frida 0
23 495 Kentuckian Giant 0
24 497 Kill Brotherhood 0
25 607 Muppet Mile 0
26 642 Order Betrayed 0
27 669 Pearl Destiny 0
28 671 Perdition Fargo 0
29 701 Psycho Shrunk 0
30 712 Raiders Antitrust 0
31 713 Rainbow Shock 0
32 742 Roof Champion 0
33 801 Sister Freddy 0
34 802 Sky Miracle 0
35 860 Suicides Silence 0
36 874 Tadpole Park 0
37 909 Treasure Command 0
38 943 Villain Desperate 0
39 950 Volume House 0
40 954 Wake Jaws 0
41 955 Walls Artist 0

▶ R

We start by “transfering” the new dataframes to R. The rest of the pipeline is very much like the one used in Python.

%%R -i rental -i inventory -i film -o df_r
df_r = film %>% 
        left_join(inventory, by="film_id") %>% 
        left_join(rental, by="inventory_id") %>% 
        group_by(film_id, title) %>% 
        summarise(rentals=n_distinct(rental_id, na.rm=TRUE)) %>% 
        filter(rentals==0) %>%
        as.data.frame()

df_r
   film_id                  title rentals
1       14         Alice Fantasia       0
2       33            Apollo Teen       0
3       36         Argonauts Town       0
4       38          Ark Ridgemont       0
5       41   Arsenic Independence       0
6       87      Boondock Ballroom       0
7      108          Butch Panther       0
8      128          Catch Amistad       0
9      144    Chinatown Gladiator       0
10     148         Chocolate Duck       0
11     171   Commandments Express       0
12     192       Crossing Divorce       0
13     195        Crowds Telemark       0
14     198       Crystal Breaking       0
15     217             Dazed Punk       0
16     221 Deliverance Mulholland       0
17     318      Firehouse Vietnam       0
18     325          Floats Garden       0
19     332  Frankenstein Stranger       0
20     359     Gladiator Westward       0
21     386              Gump Date       0
22     404          Hate Handicap       0
23     419            Hocus Frida       0
24     495       Kentuckian Giant       0
25     497       Kill Brotherhood       0
26     607            Muppet Mile       0
27     642         Order Betrayed       0
28     669          Pearl Destiny       0
29     671        Perdition Fargo       0
30     701          Psycho Shrunk       0
31     712      Raiders Antitrust       0
32     713          Rainbow Shock       0
33     742          Roof Champion       0
34     801          Sister Freddy       0
35     802            Sky Miracle       0
36     860       Suicides Silence       0
37     874           Tadpole Park       0
38     909       Treasure Command       0
39     943      Villain Desperate       0
40     950           Volume House       0
41     954              Wake Jaws       0
42     955           Walls Artist       0
all(df==df_sql), all(df==df_r)
(True, True)

Find each customer’s first order.

To solve the problem in SQL we need to use a correlated subquery whereby the inner query gets executed for every row of the outer query.

In our case, the inner query gets executed for every row of the outer query because min(rental_id) will change with each customer.

▶ SQL

q = '''
SELECT 
    r.customer_id, 
    min(r.rental_id) first_order_id, 
    (    
        SELECT r2.rental_date::date FROM rental r2 
        WHERE r2.rental_id = min(r.rental_id)
    ) first_order_date
FROM 
    rental r
GROUP BY 1
ORDER BY 1
'''

df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
display(df_sql.head())
(599, 3)
customer_id first_order_id first_order_date
0 1 76 2005-05-25
1 2 320 2005-05-27
2 3 435 2005-05-27
3 4 1297 2005-06-15
4 5 731 2005-05-29

▶ Python

In this case the Python syntax is a bit simpler. We group by the customer_id and pick the smallest rental_id.

df = (rental
        .assign(rental_date=rental.rental_date.dt.date)
        .groupby(['customer_id' ])['rental_id','rental_date']
        .min()
        .reset_index()
        .rename(columns={'rental_id':'first_order_id', 'rental_date':'first_order_date'})
      )

print(df.shape)
display(df.head())
(599, 3)
customer_id first_order_id first_order_date
0 1 76 2005-05-25
1 2 320 2005-05-27
2 3 435 2005-05-27
3 4 1297 2005-06-15
4 5 731 2005-05-29 
all(df==df_sql)
True

▶ R

The R version of the code is similar to the Python one.

%%R 

df_r = rental %>% 
        mutate(rental_date=lubridate::date(rental_date)) %>% 
        group_by(customer_id) %>% 
        summarise_at(vars(rental_id, rental_date), funs(min)) %>%
        as.data.frame()

print(dim(df_r))
print(head(df_r, 5))
[1] 599   3
  customer_id rental_id rental_date
1           1        76  2005-05-25
2           2       320  2005-05-27
3           3       435  2005-05-27
4           4      1297  2005-06-15
5           5       731  2005-05-29

Handling multiple conditions.

Let’s look at a more complex query. We’d like to return all the rental transactions conducted by the staff with id 1, for which the rental id is larger than 15000, and the payment occured between 3am and 4am or 11pm and midnight in March 2007.

▶ SQL

Although more complex than our previous examples, this query can be built from the pieces we’ve used before. To keep things somewhat simple, we’ll assume that between 3am and 4am means between 3:00 and 3:59. This will allow use to only concern ourselves with the hour number when filtering our data. Same with the second time period.

q = '''
WITH base_table AS (
    SELECT gs::date AS date,  p.*
    FROM 
        generate_series('2007-03-01', '2007-03-31', INTERVAL '1 day') as gs
    LEFT JOIN payment p ON p.payment_date::date=gs::date AND p.staff_id = 1
    ORDER BY 1 NULLS FIRST
) 
SELECT 
    bt.date, bt.payment_id, bt.customer_id, bt.staff_id, bt.rental_id, bt.amount, 
    EXTRACT(hour FROM bt.payment_date)::int AS hour
FROM 
    base_table bt
WHERE 
    bt.rental_id > 15000 
    AND         
    EXTRACT(hour FROM bt.payment_date) IN (4,23)
ORDER BY bt.payment_date, bt.rental_id
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql
(36, 7)
date payment_id customer_id staff_id rental_id amount hour
0 2007-03-22 23134 46 1 15438 2.99 23
1 2007-03-22 23958 136 1 15439 4.99 23
2 2007-03-22 23095 42 1 15442 2.99 23
3 2007-03-22 22615 598 1 15443 7.99 23
4 2007-03-22 22960 28 1 15445 4.99 23
5 2007-03-22 20574 379 1 15446 4.99 23
6 2007-03-22 21651 490 1 15448 2.99 23
7 2007-03-22 20027 322 1 15450 0.99 23
8 2007-03-22 21866 514 1 15451 2.99 23
9 2007-03-22 20370 359 1 15453 1.99 23
10 2007-03-22 24882 238 1 15455 0.99 23
11 2007-03-22 25113 262 1 15456 0.99 23
12 2007-03-22 19906 306 1 15457 2.99 23
13 2007-03-22 22876 20 1 15460 2.99 23
14 2007-03-22 23646 103 1 15461 5.99 23
15 2007-03-22 20675 389 1 15462 5.99 23
16 2007-03-22 23869 127 1 15463 5.99 23
17 2007-03-22 23283 62 1 15464 6.99 23
18 2007-03-22 21921 520 1 15465 0.99 23
19 2007-03-22 20970 418 1 15466 4.99 23
20 2007-03-22 23647 103 1 15467 3.99 23
21 2007-03-22 20768 399 1 15468 4.99 23
22 2007-03-22 22610 597 1 15469 4.99 23
23 2007-03-23 24692 215 1 15583 2.99 4
24 2007-03-23 21731 500 1 15584 2.99 4
25 2007-03-23 22149 545 1 15585 0.99 4
26 2007-03-23 21723 499 1 15587 7.99 4
27 2007-03-23 21764 503 1 15588 3.99 4
28 2007-03-23 22901 22 1 15589 6.99 4
29 2007-03-23 23284 62 1 15591 0.99 4
30 2007-03-23 24163 153 1 15593 1.99 4
31 2007-03-23 25135 264 1 15595 4.99 4
32 2007-03-23 24478 186 1 15596 0.99 4
33 2007-03-23 23323 66 1 15598 8.99 4
34 2007-03-23 23648 103 1 15599 5.99 4
35 2007-03-23 23729 113 1 15600 1.99 4

▶ Python

Just for some variety we use the between method of pandas series to select the March 2007 data. We pass pandas Timestamp objects to mark the first and last day of the month. The rest of the pipeline should be self-explanatory. The last line is added for convenience. We order the columns of the pandas dataframe to match the ordering of the columns in our SQL result.

df = (payment
  .assign(date=payment.payment_date.dt.date)
  .loc[payment.payment_date.between(
              pd.Timestamp(year=2007, month=3, day=1), 
              pd.Timestamp(year=2007, month=3, day=31))
      ]
  .assign(hour=payment.payment_date.dt.hour)
  .query('(hour in (4, 23)) & (rental_id > 15000) & (staff_id == 1)')
  .sort_values('payment_date')
  .reset_index()
  .loc[:, df_sql.columns]
) 

print(df.shape)
df
(36, 7)
date payment_id customer_id staff_id rental_id amount hour
0 2007-03-22 23134 46 1 15438 2.99 23
1 2007-03-22 23958 136 1 15439 4.99 23
2 2007-03-22 23095 42 1 15442 2.99 23
3 2007-03-22 22615 598 1 15443 7.99 23
4 2007-03-22 22960 28 1 15445 4.99 23
5 2007-03-22 20574 379 1 15446 4.99 23
6 2007-03-22 21651 490 1 15448 2.99 23
7 2007-03-22 20027 322 1 15450 0.99 23
8 2007-03-22 21866 514 1 15451 2.99 23
9 2007-03-22 20370 359 1 15453 1.99 23
10 2007-03-22 24882 238 1 15455 0.99 23
11 2007-03-22 25113 262 1 15456 0.99 23
12 2007-03-22 19906 306 1 15457 2.99 23
13 2007-03-22 22876 20 1 15460 2.99 23
14 2007-03-22 23646 103 1 15461 5.99 23
15 2007-03-22 20675 389 1 15462 5.99 23
16 2007-03-22 23869 127 1 15463 5.99 23
17 2007-03-22 23283 62 1 15464 6.99 23
18 2007-03-22 21921 520 1 15465 0.99 23
19 2007-03-22 20970 418 1 15466 4.99 23
20 2007-03-22 23647 103 1 15467 3.99 23
21 2007-03-22 20768 399 1 15468 4.99 23
22 2007-03-22 22610 597 1 15469 4.99 23
23 2007-03-23 24692 215 1 15583 2.99 4
24 2007-03-23 21731 500 1 15584 2.99 4
25 2007-03-23 22149 545 1 15585 0.99 4
26 2007-03-23 21723 499 1 15587 7.99 4
27 2007-03-23 21764 503 1 15588 3.99 4
28 2007-03-23 22901 22 1 15589 6.99 4
29 2007-03-23 23284 62 1 15591 0.99 4
30 2007-03-23 24163 153 1 15593 1.99 4
31 2007-03-23 25135 264 1 15595 4.99 4
32 2007-03-23 24478 186 1 15596 0.99 4
33 2007-03-23 23323 66 1 15598 8.99 4
34 2007-03-23 23648 103 1 15599 5.99 4
35 2007-03-23 23729 113 1 15600 1.99 4 
all(df==df_sql)
True

▶ R

Once again a similar pipeline can be build in R.

%%R -o df_r

df_r = payment %>% 
  mutate(date=lubridate::date(payment_date)) %>% 
  filter(date >= as.Date('2007-03-01') &  date <= as.Date('2007-03-31')) %>% 
  mutate(hour=lubridate::hour(payment_date)) %>% 
  filter(hour %in% c(4, 23) & rental_id > 15000 & staff_id == 1) %>% 
  arrange(payment_date) %>% 
  select(date, payment_id, customer_id, staff_id, rental_id, amount, hour) %>% 
  as.data.frame()

print(dim(df_r))
df_r
[1] 36  7
         date payment_id customer_id staff_id rental_id amount hour
1  2007-03-22      23134          46        1     15438   2.99   23
2  2007-03-22      23958         136        1     15439   4.99   23
3  2007-03-22      23095          42        1     15442   2.99   23
4  2007-03-22      22615         598        1     15443   7.99   23
5  2007-03-22      22960          28        1     15445   4.99   23
6  2007-03-22      20574         379        1     15446   4.99   23
7  2007-03-22      21651         490        1     15448   2.99   23
8  2007-03-22      20027         322        1     15450   0.99   23
9  2007-03-22      21866         514        1     15451   2.99   23
10 2007-03-22      20370         359        1     15453   1.99   23
11 2007-03-22      24882         238        1     15455   0.99   23
12 2007-03-22      25113         262        1     15456   0.99   23
13 2007-03-22      19906         306        1     15457   2.99   23
14 2007-03-22      22876          20        1     15460   2.99   23
15 2007-03-22      23646         103        1     15461   5.99   23
16 2007-03-22      20675         389        1     15462   5.99   23
17 2007-03-22      23869         127        1     15463   5.99   23
18 2007-03-22      23283          62        1     15464   6.99   23
19 2007-03-22      21921         520        1     15465   0.99   23
20 2007-03-22      20970         418        1     15466   4.99   23
21 2007-03-22      23647         103        1     15467   3.99   23
22 2007-03-22      20768         399        1     15468   4.99   23
23 2007-03-22      22610         597        1     15469   4.99   23
24 2007-03-23      24692         215        1     15583   2.99    4
25 2007-03-23      21731         500        1     15584   2.99    4
26 2007-03-23      22149         545        1     15585   0.99    4
27 2007-03-23      21723         499        1     15587   7.99    4
28 2007-03-23      21764         503        1     15588   3.99    4
29 2007-03-23      22901          22        1     15589   6.99    4
30 2007-03-23      23284          62        1     15591   0.99    4
31 2007-03-23      24163         153        1     15593   1.99    4
32 2007-03-23      25135         264        1     15595   4.99    4
33 2007-03-23      24478         186        1     15596   0.99    4
34 2007-03-23      23323          66        1     15598   8.99    4
35 2007-03-23      23648         103        1     15599   5.99    4
36 2007-03-23      23729         113        1     15600   1.99    4

Customer lifetime value

Which customers made their first order on a weekend, paid more than \$5, and have a customer lifetime value (total amount spent) which exceeds \$100?

▶ SQL

For this query we need to extract the day of the week for each transaction. This can be done using dow.

Note that in PostgreSQL Sunday is coded as 0, and Saturday as 6.

q = '''
SELECT t.* FROM (
    SELECT 
        p.*, 
        EXTRACT(dow FROM p.payment_date)::int  dow,
        (
            SELECT SUM(p3.amount) 
            FROM payment p3
            WHERE p3.customer_id = p.customer_id   
        ) as CLV
    FROM 
        payment p
    WHERE 
        p.payment_id = (
            SELECT MIN(p2.payment_id)
            FROM payment p2
            WHERE p.customer_id = p2.customer_id
        ) 
        AND 
            EXTRACT(dow FROM p.payment_date) IN (0, 6)
        AND 
            p.amount > 5     

    GROUP BY 1
)t WHERE t.CLV > 100
ORDER BY t.CLV DESC
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql
(17, 8)
payment_id customer_id staff_id rental_id amount payment_date dow clv
0 19029 137 1 2469 6.99 2007-02-18 18:52:49.996577 0 191.62
1 18572 21 2 2235 7.99 2007-02-18 02:37:16.996577 0 146.68
2 17526 346 1 1994 5.99 2007-02-17 09:35:32.996577 6 145.70
3 19502 265 2 2027 7.99 2007-02-17 11:35:22.996577 6 132.72
4 17509 342 2 2190 5.99 2007-02-17 23:58:17.996577 6 130.68
5 17866 436 1 2291 9.99 2007-02-18 06:05:12.996577 0 126.73
6 18099 497 2 2180 8.99 2007-02-17 23:16:09.996577 6 121.73
7 18995 128 2 2519 7.99 2007-02-18 22:47:47.996577 0 118.70
8 19496 263 2 2126 8.99 2007-02-17 19:23:02.996577 6 116.73
9 18636 32 2 1887 6.99 2007-02-17 02:21:44.996577 6 112.74
10 19345 225 2 2226 7.99 2007-02-18 02:08:22.996577 0 111.76
11 18395 579 2 2425 5.99 2007-02-18 16:06:11.996577 0 111.73
12 18554 16 2 1934 6.99 2007-02-17 05:33:23.996577 6 109.75
13 18666 40 2 2470 7.99 2007-02-18 18:56:57.996577 0 105.74
14 18446 593 2 2055 5.99 2007-02-17 13:55:29.996577 6 101.76
15 18367 572 2 1889 10.99 2007-02-17 02:33:38.996577 6 100.76
16 19441 251 1 2238 6.99 2007-02-18 02:50:32.996577 0 100.75

▶ Python

Let’s break the pipeline in two parts. First we compute the subset of customers who placed their first order on a week-end.

Note that unlike PostgreSQL, pandas codes Saturday as 5 and Sunday as 6.

subset_fo = (payment
               .assign(dow=lambda x: x.payment_date.dt.weekday) # extract the day of the week as an integer
               .groupby('customer_id')[['payment_date', 'payment_id', 'amount', 'rental_id', 'staff_id', 'dow']]
               .first()                                         # pick the first row for each group
               .query('(amount > 5) & (dow in [5, 6])')
             )
subset_fo.head()
payment_date payment_id amount rental_id staff_id dow
customer_id
16 2007-02-17 05:33:23.996577 18554 6.99 1934 2 5
17 2007-02-17 22:46:24.996577 18558 5.99 2175 2 5
21 2007-02-18 02:37:16.996577 18572 7.99 2235 2 6
32 2007-02-17 02:21:44.996577 18636 6.99 1887 2 5
40 2007-02-18 18:56:57.996577 18666 7.99 2470 2 6

We then join the payment table on this subset to get the information we want.

df = (payment
       .loc[payment.customer_id.isin(subset_fo.index)]
       .groupby('customer_id')[['amount']].sum()
       .rename(columns={'amount':'clv'})
       .query('clv >= 100')
       .join(subset_fo, how='left')
       .sort_values('clv', ascending=False)
       .reset_index()
       .loc[:, df_sql.columns]
    )

print(df.shape)
df
(17, 8)
payment_id customer_id staff_id rental_id amount payment_date dow clv
0 19029 137 1 2469 6.99 2007-02-18 18:52:49.996577 6 191.62
1 18572 21 2 2235 7.99 2007-02-18 02:37:16.996577 6 146.68
2 17526 346 1 1994 5.99 2007-02-17 09:35:32.996577 5 145.70
3 19502 265 2 2027 7.99 2007-02-17 11:35:22.996577 5 132.72
4 17509 342 2 2190 5.99 2007-02-17 23:58:17.996577 5 130.68
5 17866 436 1 2291 9.99 2007-02-18 06:05:12.996577 6 126.73
6 18099 497 2 2180 8.99 2007-02-17 23:16:09.996577 5 121.73
7 18995 128 2 2519 7.99 2007-02-18 22:47:47.996577 6 118.70
8 19496 263 2 2126 8.99 2007-02-17 19:23:02.996577 5 116.73
9 18636 32 2 1887 6.99 2007-02-17 02:21:44.996577 5 112.74
10 19345 225 2 2226 7.99 2007-02-18 02:08:22.996577 6 111.76
11 18395 579 2 2425 5.99 2007-02-18 16:06:11.996577 6 111.73
12 18554 16 2 1934 6.99 2007-02-17 05:33:23.996577 5 109.75
13 18666 40 2 2470 7.99 2007-02-18 18:56:57.996577 6 105.74
14 18446 593 2 2055 5.99 2007-02-17 13:55:29.996577 5 101.76
15 18367 572 2 1889 10.99 2007-02-17 02:33:38.996577 5 100.76
16 19441 251 1 2238 6.99 2007-02-18 02:50:32.996577 6 100.75 
all(df==df_sql)
True

▶ R

Let’s do the same with R. We’ll drop the payment_date column at the end so that we can fit all the remaining columns horizontally.

Of course, R (lubridate) uses yet another encoding for the days of the week. Saturday is represented as 7 and Sunday as 1.

%%R

subset_fo = payment %>% 
              group_by(customer_id) %>% 
              mutate(dow=lubridate::wday(payment_date)) %>% 
              filter(row_number()==1 & dow%in% c(1, 7) & amount>5) 

df_r = payment %>% 
        right_join(subset_fo, by="customer_id") %>% 
        group_by(customer_id) %>% 
        summarise(clv=sum(amount.x)) %>% 
        filter(clv > 100) %>% 
        left_join(subset_fo, by='customer_id') %>% 
        select(payment_id, customer_id, staff_id, rental_id, amount, payment_date, dow, clv) %>% 
        arrange(desc(clv)) %>%
        select(-payment_date)

print(dim(df_r))
as.data.frame(df_r)
[1] 17  7
   payment_id customer_id staff_id rental_id amount dow    clv
1       19029         137        1      2469   6.99   1 191.62
2       18572          21        2      2235   7.99   1 146.68
3       17526         346        1      1994   5.99   7 145.70
4       19502         265        2      2027   7.99   7 132.72
5       17509         342        2      2190   5.99   7 130.68
6       17866         436        1      2291   9.99   1 126.73
7       18099         497        2      2180   8.99   7 121.73
8       18995         128        2      2519   7.99   1 118.70
9       19496         263        2      2126   8.99   7 116.73
10      18636          32        2      1887   6.99   7 112.74
11      19345         225        2      2226   7.99   1 111.76
12      18395         579        2      2425   5.99   1 111.73
13      18554          16        2      1934   6.99   7 109.75
14      18666          40        2      2470   7.99   1 105.74
15      18446         593        2      2055   5.99   7 101.76
16      18367         572        2      1889  10.99   7 100.76
17      19441         251        1      2238   6.99   1 100.75

How many movies have a replacement cost above or below the average replacement cost?

Once way to answer this is to compute how many movies have a replacement cost higher than the average, how many have a replacement cost lower or equal to the average, and take the union of the two.

▶ SQL

q = '''
SELECT t.grouping, COUNT(*) FROM (
    SELECT f.*, 'above' as grouping
    FROM film f
    WHERE f.replacement_cost > (SELECT AVG(f2.replacement_cost) FROM film f2) 

    UNION 

    SELECT f.*, 'below_eq' as grouping
    FROM film f
    WHERE f.replacement_cost <= (SELECT AVG(f2.replacement_cost) FROM film f2) 
    )t 
GROUP BY 1
'''
df_sql = pd.read_sql_query(q, con)
print(df_sql.shape)
df_sql.head()
(2, 2)
grouping count
0 below_eq 464
1 above 536

▶ Python

The Python syntax is definitely simpler here because we can create a Boolean mask telling us whether the replacement cost is above average, and then use value_counts to tally up the True and False counts.

(film
  .assign(above=film.replacement_cost > film.replacement_cost.mean())
  .loc[:, 'above']
  .value_counts()
  .rename(index={True: 'above', False: 'below_eq'})
)
above       536
below_eq    464
Name: above, dtype: int64

▶ R

The R code is similar to the Python one.

%%R
film %>% 
  mutate(above=replacement_cost>mean(film$replacement_cost)) %>% 
  count(above) %>% 
  mutate(above=if_else(above, 'above', 'below_eq'))
# A tibble: 2 x 2
  above        n
  <chr>    <int>
1 below_eq   464
2 above      536

Which movie generated the more revenue, for each viewer rating category?

▶ SQL

The grouping by rating part is straightforward, however, we want the top performing movie within each group. We can use a window function (the OVER part) to do this.

q = '''
WITH some_table AS (
    SELECT 
        f.film_id, 
        f.title, 
        f.rating, 
        SUM(p.amount),
        ROW_NUMBER() OVER(PARTITION BY f.rating ORDER BY SUM(p.amount) DESC)

    FROM film f
    JOIN inventory i ON f.film_id = i.film_id
    JOIN rental r ON r.inventory_id = i.inventory_id
    JOIN payment p ON p.rental_id = r.rental_id
    GROUP BY 1, 2, 3
    ORDER BY 3) 
    
SELECT st.* FROM some_table st 
WHERE st.row_number = 1
ORDER BY 4 DESC
'''
df_sql = pd.read_sql_query(q, con)
df_sql
film_id title rating sum row_number
0 879 Telegraph Voyage PG 215.75 1
1 1000 Zorro Ark NC-17 199.72 1
2 460 Innocent Usual PG-13 191.74 1
3 764 Saturday Lambs G 190.74 1
4 938 Velvet Terminator R 152.77 1

▶ Python

The pattern below is a common one. We group by some features to compute an aggregate measure, and then ungroup (reset_index) to group the data again at a different level of granularity.

df = (film
       .merge(inventory, on='film_id', how='left')    
       .merge(rental, on='inventory_id', how='left')
       .merge(payment, on='rental_id', how='left')
       .groupby(['rating','film_id'])[['amount']]
       .sum()
       .sort_values('amount', ascending=False)
       .rename(columns={'amount': 'sum'})
       .reset_index()
       .groupby('rating')
       .first()
       .merge(film, on='film_id', how='left')
       .sort_values('sum', ascending=False)
       .reset_index()
       .loc[:, ['film_id','title','rating','sum']]
      )

df
film_id title rating sum
0 879 Telegraph Voyage PG 215.75
1 1000 Zorro Ark NC-17 199.72
2 460 Innocent Usual PG-13 191.74
3 764 Saturday Lambs G 190.74
4 938 Velvet Terminator R 152.77

▶ R

This is essentially the same approach as in Python.

%%R
df_r = film %>% 
  left_join(inventory, by='film_id') %>% 
  left_join(rental, by='inventory_id') %>% 
  left_join(payment, by='rental_id') %>% 
  group_by(rating, film_id) %>% 
  summarise(sum=sum(amount, na.rm=TRUE)) %>% 
  ungroup() %>% 
  arrange(desc(sum)) %>% 
  group_by(rating) %>% 
  filter(row_number()==1) %>% 
  left_join(film, by='film_id') %>% 
  select(film_id, title, rating.x, sum) 

as.data.frame(df_r)
  film_id             title rating.x    sum
1     879  Telegraph Voyage       PG 215.75
2    1000         Zorro Ark    NC-17 199.72
3     460    Innocent Usual    PG-13 191.74
4     764    Saturday Lambs        G 190.74
5     938 Velvet Terminator        R 152.77

Hopefully, the examples above show that a significant part of the syntax used to wrangle structured data (data arranged in tables), in both Python (pandas) and R (tidyverse), has been influenced by SQL.

Many of the pipelines we built are comprised of the same steps as the corresponding SQL query. This is especially true for basic queries where we do simple things like group by columns or build a simple aggregate measure. Similarly, syntactic differences aside, joining operations work in a comparable way across the three languages.

As we consider more complex operations, however, a direct port of the SQL querie to the other languages may not be the most optimal way. We saw a small example of this when we computed which days in March had no rentals. The good news, however, is that these languages aren’t mutually exclusive. After all, we did run all our SQL via Python (pandas). Rather these languages complement each other and can be used in conjunction to build pipelines that can perform complex manipulation of the data in an efficient way. Very often, SQL is used to first extract a subset of the data from a large database, which can then be manipulated and transformed further in Python or R.

To steal a line from Alexandre Dumas’ The Three Musketeers, this is truly a case of All for one and one for all!

python  sql  r  pandas  tidyverse  data wrangling