SQL and Spreadsheets

Overview
Round 1: pandas
Round 2: SQL
1. Overview and setup
2. Aside: DuckDB
Round 3: Spreadsheets
1. Overview and setup
2. Walkthrough video

Overview

Representations of tabular data

In this class, we’ve worked with DataFrames in pandas.

When we say pandas DataFrame, we’re talking about the pandas API for its DataFrame objects.
When we say “DataFrame”, we’re referring to a general way to represent data. Specifically, DataFrames organize data into rows and columns, with labels for both rows and columns.

There many other ways to work with data tables, each of which have their pros and cons.
Some examples include R data frames, SQL databases, spreadsheets in Google Sheets/Excel, or even matrices from linear algebra.

Below, we discuss pros and cons of (some of) the ways we can work with tabular data.

Platform	Pros ✅	Cons ❌
`pandas` DataFrames	Works well with the Python ecosystem (for visualization, machien learning, domain-specifc purposes, etc.), extremely flexible, reproducible steps	Steep learning curve (need to know Python too) and messy code, easy to make destructive in-place modifications, no persistence (everything starts from a `CSV` file)
R data frames	Designed specifically for data science so statistics and visualizations are easy; reproducible steps	R isn’t as general-purpose as Python, no persistence
SQL	Scalable, good for maintaining many large, important datasets with many concurrent users	Requires lots of infrastructure, advanced operations can be challenging
Spreadsheets	Widespread use, very easy to get started, easy for sharing	Steps aren’t reproducible, advanced operations can be challenging

A common workflow is to load a subset of data in from a database system into pandas, then do further cleaning and visualization. Another is to load and clean data in pandas, then store it in a database system for others to use.

Relational algebra

Relational algebra captures common data operations between many data table systems. For example, the following expression describes a calculation in relational algebra:

\[\pi_{\text{int_rate}}(\sigma_{\text{state} = \text{"MI"}}(\text{loans}))\]

\(\pi\) stands for “project,” i.e. selecting columns; \(\sigma\) stands for “select,” i.e. selecting rows. We won’t test you on relational algebra syntax, but if you’d like to learn more, take EECS 484: Database Management Systems.

In pandas, we’d implement this expression as follows:

loans.loc[loans['state'] == 'MI', 'int_rate']

Question: How would we implement it in SQL? Or a spreadsheet?

To show you how the tabular manipulation skills you’ve learned in pandas generalize to other systems, we will answer a few questions in all three of the above platforms. First, we’ll work through our analysis in pandas, and then in SQL, and finally, in a Google Sheets document.

Dataset: Top 200 Streams

Our dataset contains the number of streams for the top 200 songs on Spotify, for the week leading up to September 19th. We downloaded it from here in Fall 2024, and you can find a copy of it (to repeat the analysis) here.

charts = pd.read_csv('data/regional-global-weekly-2024-09-19.csv')
charts

	rank	uri	artist_names	track_name	...	peak_rank	previous_rank	weeks_on_chart	streams
0	1	spotify:track:2plbrEY59IikOBgBGLjaoe	Lady Gaga, Bruno Mars	Die With A Smile	...	1	1	5	76502673
1	2	spotify:track:6dOtVTDdiauQNBQEDOtlAB	Billie Eilish	BIRDS OF A FEATHER	...	1	2	18	54756808
2	3	spotify:track:5G2f63n7IPVPPjfNIGih7Q	Sabrina Carpenter	Taste	...	3	3	4	48790619
...	...	...	...	...	...	...	...	...	...
197	198	spotify:track:3EaJDYHA0KnX88JvDhL9oa	Steve Lacy	Dark Red	...	66	200	118	8901006
198	199	spotify:track:0gEyKnHvgkrkBM6fbeHdwK	The Cranberries	Linger	...	199	-1	2	8867800
199	200	spotify:track:7iUtQNMRB8ZkKC4AmEuCJC	Myke Towers	LA FALDA	...	24	185	36	8848312

200 rows × 9 columns

Scroll through the columns above. A song’s 'uri' is its uniform resource identifier. Given a song’s 'uri', we can play it!

def show_spotify(uri):
    code = uri[uri.rfind(':')+1:]
    src = f"https://open.spotify.com/embed/track/{code}"
    width = 400
    height = 75
    display(IFrame(src, width, height))

my_uri = charts.loc[charts['track_name'] == 'Yellow', 'uri'].iloc[0] 
my_uri

'spotify:track:3AJwUDP919kvQ9QcozQPxg'

show_spotify(my_uri)

Round 1: `pandas`

Below, we present six tasks and their solutions. A good way to practice your pandas skills is to download the CSV linked above and attempt the six tasks on your own, without looking at the solutions.

Task 1: Find the total number of streams of songs by Sabrina Carpenter.

Click here to see the solution.

charts.loc[charts['artist_names'] == 'Sabrina Carpenter', 'streams'].sum()

212960137

Task 2: Find the total number of streams per artist, sorted by number of streams in descending order. Only show the top 5 artists.

Click here to see the solution.

(
    charts
    .groupby('artist_names')
    ['streams']
    .sum()
    .sort_values(ascending=False)
    .head(5)
)

artist_names
Sabrina Carpenter        212960137
Billie Eilish            107797821
Chappell Roan             92990557
Linkin Park               89935206
Lady Gaga, Bruno Mars     76502673
Name: streams, dtype: int64

Task 3: Find the artist with the lowest average number of streams, among artists with at least 5 songs in the Top 200.

Click here to see the solution.

(
    charts
    .groupby('artist_names')
    .filter(lambda df: df.shape[0] >= 5)
    .groupby('artist_names')
    ['streams']
    .mean()
    .sort_values()
    .head(1)
)

artist_names
Bruno Mars    1.24e+07
Name: streams, dtype: float64

Task 4: Find the number of songs with a higher ranking this week than last week.

Click here to see the solution.

charts[charts['rank'] > charts['previous_rank']].shape[0]

Task 5: charts_old contains the Top 200 songs in the previous week. You can download it from here.

charts_old = pd.read_csv('data/regional-global-weekly-2024-09-12.csv')
charts_old.head()

	rank	uri	artist_names	track_name	...	peak_rank	previous_rank	weeks_on_chart	streams
0	1	spotify:track:2plbrEY59IikOBgBGLjaoe	Lady Gaga, Bruno Mars	Die With A Smile	...	1	1	4	74988392
1	2	spotify:track:6dOtVTDdiauQNBQEDOtlAB	Billie Eilish	BIRDS OF A FEATHER	...	1	2	17	56949755
2	3	spotify:track:5G2f63n7IPVPPjfNIGih7Q	Sabrina Carpenter	Taste	...	3	3	3	49847514
3	4	spotify:track:7tI8dRuH2Yc6RuoTjxo4dU	Jimin	Who	...	1	4	8	45431448
4	5	spotify:track:2qSkIjg1o9h3YT9RAgYN75	Sabrina Carpenter	Espresso	...	1	5	22	44982843

5 rows × 9 columns

Find the song with the largest increase in streams between last week and this week, among songs that were in the Top 200 in both weeks.

Click here to see the solution.

with_old = (
    charts[['uri', 'track_name', 'artist_names', 'streams']]
    .merge(charts_old[['uri', 'streams',]], on='uri', suffixes=('_new', '_old'))
)

(
    with_old
    .assign(change=with_old['streams_new'] - with_old['streams_old'])
    .sort_values('change', ascending=False)
    [['track_name', 'artist_names', 'change']]
    .head(1)
)

	track_name	artist_names	change
5	Good Luck, Babe!	Chappell Roan	3217539

Task 6: Find the 4 songs with the most artists.

Click here to see the solution.

(
    charts
    .assign(num_artists=charts['artist_names'].str.count(', '))
    .sort_values('num_artists', ascending=False)
    .head(4)
)

	rank	uri	artist_names	track_name	...	previous_rank	weeks_on_chart	streams	num_artists
45	46	spotify:track:1BJJbSX6muJVF2AK7uH1x4	Adam Port, Stryv, Keinemusik, Orso, Malachiii	Move	...	31	14	15987047	4
186	187	spotify:track:22skzmqfdWrjJylampe0kt	Macklemore & Ryan Lewis, Macklemore, Ryan Lewi...	Can't Hold Us (feat. Ray Dalton)	...	163	132	9050984	3
194	195	spotify:track:28drn6tQo95MRvO0jQEo5C	Future, Metro Boomin, Travis Scott, Playboi Carti	Type Shit	...	-1	25	8942475	3
177	178	spotify:track:4QNpBfC0zvjKqPJcyqBy9W	Pitbull, AFROJACK, Ne-Yo, Nayer	Give Me Everything (feat. Nayer)	...	165	19	9265696	3

4 rows × 10 columns

Round 2: SQL

Overview and setup

SQL stands for “Structured Query Language”. It is the standard language for database manipulation. Each database system – for instance, MySQL, SQLite, DuckDB, or PostgreSQL – has its own slightly different dialect of SQL with unique features.

Of note, SQL is a declarative language. That is, in SQL, you just describe what you want calculated, not how you want it calculated. It’s the database engine’s job to figure out how to process your query.

        SELECT artist_names, SUM(streams) AS total_streams FROM charts
        GROUP BY artist_names
        ORDER BY total_streams DESC
        LIMIT 5;

One of the SQL queries we'll write shortly.

We bolded the word “query” above because all code we write in SQL is referred to as a query. All SQL queries follow the same general template:

(source)

W3Schools has examples of SQL syntax, as does the example above.

In this guide, we’ll demonstrate the usage of two SQL database platforms: SQLite and DuckDB. SQLite comes pre-installed on most systems, while DuckDB is open-source, and integrates with pandas really well. We’ll answer our first few tasks by working with sqlite3 in the command-line. To follow along:

Download spotify.db from here.
Open your Terminal and cd to the directory where you downloaded spotify.db.
Run sqlite3 spotify.db.

These steps will open a sqlite3 interpreter, connected to the spotify.db database. A database file can contain multiple tables; this one contains two: charts and charts_old. Note that tables are also known as relations in database terminology.

Now, on with the tasks. Here, we’ll show the solutions directly. That said, this walkthrough video also covers the answers, and develops them step-by-step. (It’s taken from a lecture recording in Fall 2024.)

Task 1: Find the total number of streams of songs by Sabrina Carpenter.

Task 2: Find the total number of streams per artist, sorted by number of streams in descending order. Only show the top 5 artists.

Note that the values above match the outputs in the pandas implementations of the tasks!

Aside: DuckDB

Instead of having to run all of our SQL queries in the Terminal using a .db file, we can use DuckDB, which allows us to execute queries in a notebook using a pandas DataFrame!

To use DuckDB, pip install it in your Terminal using pip install duckdb. Then, in your notebook, run import duckdb. That will allow you to use the run_sql function, defined below, to execute SQL queries using the DataFrames in your notebook as SQL tables.

def run_sql(query_str, as_df=False):
    out = duckdb.query(query_str)
    if as_df:
        return out.to_df()
    else:
        return out

Let’s revisit Task 2:

run_sql('''
SELECT artist_names, SUM(streams) AS total_streams FROM charts
GROUP BY artist_names
ORDER BY total_streams DESC
LIMIT 5;
''')

┌───────────────────────┬───────────────┐
│     artist_names      │ total_streams │
│        varchar        │    int128     │
├───────────────────────┼───────────────┤
│ Sabrina Carpenter     │     212960137 │
│ Billie Eilish         │     107797821 │
│ Chappell Roan         │      92990557 │
│ Linkin Park           │      89935206 │
│ Lady Gaga, Bruno Mars │      76502673 │
└───────────────────────┴───────────────┘

We can even ask for the output back as a DataFrame by setting as_df to True! This means that you can combine both SQL operations and pandas operations.

run_sql('''
SELECT artist_names, SUM(streams) AS total_streams FROM charts
GROUP BY artist_names
ORDER BY total_streams DESC
LIMIT 5;
''', as_df=True)

	artist_names	total_streams
0	Sabrina Carpenter	2.13e+08
1	Billie Eilish	1.08e+08
2	Chappell Roan	9.30e+07
3	Linkin Park	8.99e+07
4	Lady Gaga, Bruno Mars	7.65e+07

Back to the tasks.

Task 3: Find the artist with the lowest average number of streams, among artists with at least 5 songs in the Top 200.

run_sql('''
SELECT artist_names, AVG(streams) as avg_streams FROM charts
GROUP BY artist_names
HAVING COUNT(*) >= 5
ORDER BY avg_streams
LIMIT 1;
''')

┌──────────────┬─────────────┐
│ artist_names │ avg_streams │
│   varchar    │   double    │
├──────────────┼─────────────┤
│ Bruno Mars   │  12411488.8 │
└──────────────┴─────────────┘

Task 4: Find the number of songs with a higher ranking this week than last week.

run_sql('''
SELECT COUNT(*) as num_songs FROM (
    SELECT * FROM charts
    WHERE rank > previous_rank
)
''')

┌───────────┐
│ num_songs │
│   int64   │
├───────────┤
│       119 │
└───────────┘

Task 5: charts_old contains the Top 200 songs in the previous week.

Find the song with the largest increase in streams between last week and this week, among songs that were in the Top 200 in both weeks.

run_sql('''
SELECT track_name, artist_names, (new_streams - old_streams) AS change
FROM (
    SELECT charts.uri, 
           charts.track_name, 
           charts.artist_names, 
           charts.streams AS new_streams, 
           charts_old.uri, 
           charts_old.streams AS old_streams
    FROM charts
    INNER JOIN charts_old ON charts.uri = charts_old.uri
) AS merged
ORDER BY change DESC
LIMIT 1;
''', as_df=True)

	track_name	artist_names	change
0	Good Luck, Babe!	Chappell Roan	3217539

Task 6: Find the 4 songs with the most artists.

Note that the syntax used in our solution doesn’t exist in SQLite, but does exist in DuckDB.

run_sql('''
SELECT track_name, artist_names, array_length(str_split(artist_names, ', ')) AS num_artists
FROM charts
ORDER BY num_artists DESC
LIMIT 4;
''', as_df=True)

	track_name	artist_names	num_artists
0	Move	Adam Port, Stryv, Keinemusik, Orso, Malachiii	5
1	Give Me Everything (feat. Nayer)	Pitbull, AFROJACK, Ne-Yo, Nayer	4
2	Can't Hold Us (feat. Ray Dalton)	Macklemore & Ryan Lewis, Macklemore, Ryan Lewi...	4
3	Type Shit	Future, Metro Boomin, Travis Scott, Playboi Carti	4

In all of the examples above, the results matched our pandas implementations, as we’d expect.

Round 3: Spreadsheets

Overview and setup

While we’re big fans of writing code in this class, in the business world, spreadsheets are (still) the most common tool for tabular data manipulation. Spreadsheets are great for showing information directly to someone else, and are easy to share.

Here, we’ll replicate our prior analyses using Google Sheets. Excel may be more industry-standard, but Google Sheets are widely popular as well, and are easy for sharing.

This Google Sheet has both the charts and charts_old datasets that we’ve referred to above. You can try your hand at the analyses by creating a copy of it; do so by opening it and going to File > Make a copy.

Walkthrough video

Since Google Sheets operations are slightly more complicated to share via text, we’ve produced a walkthrough video. It covers the same six tasks that we’ve already completed in pandas and SQL.

If following along, at each stage, make sure you see the same results as in the pandas and SQL sections!

Task 1: Find the total number of streams of songs by Sabrina Carpenter.

Relevant functions: FILTER, SUMIF.

Task 2: Find the total number of streams per artist, sorted by number of streams in descending order. Only show the top 5 artists.

Relevant functions: UNIQUE, SUMIF.

Task 3: Find the artist with the lowest average number of streams, among artists with at least 5 songs in the Top 200.

Relevant functions: AVERAGEIF.

Task 4: Find the number of songs with a higher ranking this week than last week.

Relevant functions: IF.

Task 5: charts_old contains the Top 200 songs in the previous week.

Find the song with the largest increase in streams between last week and this week, among songs that were in the Top 200 in both weeks.

Relevant functions: FILTER.

Task 6: Find the 4 songs with the most artists.

Relevant functions: SPLIT, LEN, SUBSTITUTE.

As you’ve seen, the DataFrame manipulation techniques we’ve learned about over the past month generalize to other systems that you might be exposed to. This is important to realize, since in 20 years, pandas may not exist, but grouping, pivoting, querying, etc. are all concepts that still will be useful.

SQL and Spreadsheets

Table of contents

Overview

Representations of tabular data

Relational algebra

Dataset: Top 200 Streams

Round 1: pandas

Round 2: SQL

Overview and setup

Aside: DuckDB

Round 3: Spreadsheets

Overview and setup

Walkthrough video

Round 1: `pandas`