Skip to the notebook

drvas.coThe analysisThe dataThe game

Hit-Song Mixtape · The data

SIDE 0 · DEFINING AND CLEANING

Defining and cleaning

Get the data, look hard at it, fix what evidence can settle, and grade it. Outcomes and hypotheses wait for the next notebook.

Two notebooks, two jobs: one for defining and cleaning, the other for shining and gleaning. This one defines and cleans: what a row is, what each column means, what was wrong, what was repaired with evidence, and how much each file can be trusted. When the data is the way we want it, the analysis shines and gleans.

Show the code

# the data card, at a glance. It grades the clean files this notebook saves (part 10) and
#   defines every column; part 11 writes it to data/DATA_CARD.md. On a first run, the card fills in
#   once the notebook has run to the end.
import sys
from pathlib import Path

from IPython.display import HTML

sys.path.append("tools")
sys.path.append(".claude/skills/fresh-figures/scripts")
import fresh_style as ms
from data_card import build_html

ms.use_theme()
if all(Path(f).exists() for f in ["data/songs_2023.csv", "data/catalog.csv", "data/columns.json"]):
    display(HTML(build_html()))
else:
    display(HTML(ms.card("📋 Data card", "Not built yet: run this notebook once to the end.", "")))

📋 Data card

Each file is graded on six checks (A: no fails, at most 1 warn · B: no fails, 2–3 warns · C: 1 fail or 4+ warns · D: 2+ fails). Open a card for the evidence and a dictionary of every column.

GRADE CThe 2023 list: Most Streamed Spotify Songs 2023

Usable with care: some questions it cannot answer. Solid numbers. 109 song names arrived damaged and 71 are repaired from evidence; only hits are on the list, so it can't show what makes a song a hit.

▸ Open for the checks and column dictionary

data/songs_2023.csv · 949 rows × 26 columns · a row is: One song on the list of 2023's most-streamed songs on Spotify. Source: Nidula Elgiriyewithana, Kaggle. Pulled, cleaned and repaired by the data notebook (see data/PROVENANCE.md and data/data_fixes.csv).

Quality grade: C, usable with care: some questions it cannot answer

Check	Result	Why
provenance	⚠️ warn	Source, raw file and its sha256 are recorded, but the publisher never says when the stream counts were read. the data notebook narrows it to between 14 July and 26 August 2023.
completeness	❌ fail	10.67% of all cells are blank. Columns with blanks: release_year (1), streams_millions (2), release_date (1), repair_method (877), repair_confidence (877), fixes (874).
validity	⚠️ warn	One stream count was text and one was impossible (2,762 plays for a song on 763 playlists); the data notebook set both to blank. Fix F2 (approved) marked one release year unknown: "Agudo Mágico", a phonk track by streaming-era producers, was listed as 1930. One likely wrong credit is documented, not changed (F7): "traitor" is credited to Juan Cruz Toledo, Huilen Toledo, but its year, streams and sound match Olivia Rodrigo's.
uniqueness	⚠️ warn	Four songs appeared twice. the data notebook kept the row with more streams: 953 rows became 949. 33 songs list their artists in a different order than the catalog does; they are flagged in artist_order_differs_from_catalog (F8).
text integrity	⚠️ warn	109 songs (11.5%) arrived with damaged names: accented letters lost and many names cut off before the file was published. The bytes can't be recovered, but the names can be found again: fix F1 (approved) repaired 71 from evidence (a unique catalog match, the catalog's artist list, or the official video's title and channel), logged song by song in data/name_repairs.csv. 40 still carry damage because no evidence settles them.
selection	⚠️ warn	Only hits are on the list, by design. It can compare hits with each other, but not hits with songs that never became hits.

Column dictionary

Column	What it means	Unit or values	Type	Blank	Observed	Publisher's name
track	The song's title		object	0	943 distinct	track_name
artists	Every credited artist, separated by commas		object	0	645 distinct	artist(s)_name
n_artists	How many artists are credited	count	int64	0	1 to 8	artist_count
release_year	Year the song came out	year	float64	1	1,942.0 to 2,023.0	released_year
release_month	Month the song came out	1–12	int64	0	1 to 12	released_month
release_day	Day of the month the song came out	1–31	int64	0	1 to 31	released_day
spotify_playlists	How many Spotify playlists include the song	count	int64	0	31 to 52,898	in_spotify_playlists
streams_millions	Total Spotify plays since release, read once in mid-2023	millions of plays	float64	2	1.4 to 3,703.9	streams
tempo_bpm	The song's speed	beats per minute	int64	0	65 to 206	bpm
mode	Whether the song is in a major key (often brighter) or a minor key (often darker)	Major / Minor	object	0	2 distinct	mode
danceability_pct	How suitable the song is for dancing: tempo, how steady the rhythm is, how strong the beat is	0–100	int64	0	23 to 96	danceability_%
valence_pct	How happy the song sounds: near 0 sad or angry, near 100 cheerful or euphoric	0–100	int64	0	4 to 97	valence_%
energy_pct	How intense and active the song feels: fast, loud, busy	0–100	int64	0	9 to 97	energy_%
acousticness_pct	How sure Spotify is that the song is acoustic (no electronic instruments)	0–100	int64	0	0 to 97	acousticness_%
instrumentalness_pct	How likely the song has no vocals	0–100	int64	0	0 to 91	instrumentalness_%
liveness_pct	How likely the song was recorded live, with an audience	0–100	int64	0	3 to 97	liveness_%
speechiness_pct	How much of the song is spoken words, such as rap or talk	0–100	int64	0	2 to 64	speechiness_%
release_date	The full release date, built from year, month and day	date	object	1	476 distinct	(built in the data notebook)
track_in_file	The title exactly as the publisher's file had it, before any repair		object	0	943 distinct	track_name
artists_in_file	The artist list exactly as the publisher's file had it, before any repair		object	0	645 distinct	artist(s)_name
release_year_in_file	The release year as the publisher's file had it (fix F2 blanked one implausible year)	year	int64	0	1930 to 2023	released_year
name_repaired	Whether an approved repair (fix F1) changed the title or the artists	True / False	bool	0	7% True	(added in the data notebook)
repair_method	The evidence a repaired name came from: catalog, official video, or artist list		object	877	4 distinct	(added in the data notebook)
repair_confidence	How sure the repair is: high (a unique catalog match) or medium (an official video's title)	high / medium	object	877	2 distinct	(added in the data notebook)
fixes	The approved fixes that changed this row, for example F1;F3 (see data/data_fixes.csv)		object	874	3 distinct	(added in the data notebook)
artist_order_differs_from_catalog	True when the catalog has this song with the same artists in a different order, so a match on the first artist misses it (F8)	True / False	bool	0	4% True	(added in the data notebook)

GRADE DThe catalog: Spotify Tracks Dataset

Not fit for this analysis without more data. Every sound is measured, but every genre has exactly 1,000 rows by the collector's choice, so genre mixes are man-made.

▸ Open for the checks and column dictionary

data/catalog.csv · 113,999 rows × 19 columns · a row is: One search result: a track found by one genre search (a track found by two searches appears twice). Source: Maharshi Pandya, Hugging Face, BSD licence. Pulled and cleaned by the data notebook (see data/PROVENANCE.md).

Quality grade: D, not fit for this analysis without more data

Check	Result	Why
provenance	⚠️ warn	Source, BSD licence, raw file and its sha256 are recorded, but not when the popularity scores were read.
completeness	❌ fail	5.29% of all cells are blank. Columns with blanks: tempo_bpm (157), danceability_pct (157), valence_pct (157), speechiness_pct (157), fixes (113,991).
validity	⚠️ warn	157 rows had danceability, valence, speechiness and tempo all exactly 0: Spotify could not measure them (88% are sleep tracks). the data notebook set those four to blank. Popularity 0 (14% of rows) is a real value.
uniqueness	⚠️ warn	A row is a search result, not a track: 16,641 tracks appear under more than one genre. Questions about tracks use one row per track.
text integrity	✅ pass	One row had no track name or artist; the data notebook dropped it. No damaged names.
selection	❌ fail	Every genre has exactly 1,000 rows, a number the collector chose. The mix of genres, and the share of hits in each genre, reflect how the data was gathered, not how music is played.

Column dictionary

Column	What it means	Unit or values	Type	Blank	Observed	Publisher's name
track_id	Spotify's ID for the track (open.spotify.com/track/)		object	0	89,740 distinct	track_id
track	The track's title		object	0	73,608 distinct	track_name
artists	Every credited artist, separated by semicolons		object	0	31,437 distinct	artists
album	The album the track appears on		object	0	46,589 distinct	album_name
genre	The genre search that found this row	one of 114	object	0	114 distinct	track_genre
popularity	Spotify's score, mostly from how many plays a track has had and how recent they are; 0 means not played much lately	0–100	int64	0	0 to 100	popularity
duration_s	Length of the track	seconds	float64	0	8.6 to 5,237.3	duration_ms (÷ 1000)
explicit	Whether the track has explicit lyrics	True / False	bool	0	9% True	explicit
tempo_bpm	The track's speed; blank where Spotify could not measure the rhythm	beats per minute	float64	157	30.2 to 243.4	tempo
loudness_db	Average loudness; higher is louder. A few tracks are mastered above 0	decibels, mostly −50 to 0	float64	0	-49.5 to 4.5	loudness
mode	Major key or minor key	Major / Minor	object	0	2 distinct	mode (1/0)
danceability_pct	How suitable the track is for dancing; blank where the rhythm could not be measured	0–100	float64	157	5.1 to 98.5	danceability (× 100)
valence_pct	How happy the track sounds; blank where the rhythm could not be measured	0–100	float64	157	0.0 to 99.5	valence (× 100)
energy_pct	How intense and active the track feels	0–100	float64	0	0.0 to 100.0	energy (× 100)
acousticness_pct	How sure Spotify is that the track is acoustic	0–100	float64	0	0.0 to 99.6	acousticness (× 100)
instrumentalness_pct	How likely the track has no vocals	0–100	float64	0	0.0 to 100.0	instrumentalness (× 100)
liveness_pct	How likely the track was recorded live	0–100	float64	0	0.0 to 100.0	liveness (× 100)
speechiness_pct	How much of the track is spoken words; blank where the rhythm could not be measured	0–100	float64	157	2.2 to 96.5	speechiness (× 100)
fixes	The approved fixes that changed this row (F3: stray spaces trimmed)		object	113,991	1 distinct	(added in the data notebook)

5 NEWDerived columns

The columns and tables this analysis creates: what each is built from, how, and why.

▸ Open to see how each new column is made

New column or table	Made from	How	Why	Where
tracks (table)	catalog	one row per track_id (its first genre), minus the 157 tracks with no rhythm analysis	questions about how tracks sound need one row per track, not one per genre search	analysis notebook · The data
released	release_year	pd.cut into before 2000, 2000-09, 2010-14, 2015-18, 2019-20, 2021, 2022, 2023	a stream count is a running total, so songs must be compared with songs of about the same age	analysis notebook · Digging deeper · Streams by release year
danceability / valence quarter (groups)	danceability_pct, valence_pct	pd.qcut into four equal-sized groups	compare typical streams from the least to the most danceable (or upbeat) songs	analysis notebook · Hypotheses 1 and 2
match_name	track, artists	track plus first artist, lower case, trimmed	the two files share no ID, so songs are found in the catalog by name	analysis notebook · Digging deeper · What the list leaves out
popularity (list songs)	catalog popularity by match_name	the highest popularity among catalog tracks with the same match_name	puts list songs on the catalog's popularity scale	analysis notebook · Digging deeper · What the list leaves out

SIDE 0 · PART 01

Where the data comes from

Two public files, collected by others through Spotify's API, because Spotify closed that door in 2024.

Spotify measures how every track sounds (danceability, energy, valence and so on) and exposed those measurements through its Web API. In November 2024 Spotify stopped giving new applications access to those audio features, so we cannot pull them ourselves. We use two files that others collected with that API and published.

Detail	Most Streamed Spotify Songs 2023	Spotify Tracks Dataset
Publisher	Nidula Elgiriyewithana, on Kaggle	Maharshi Pandya, on Hugging Face
Page	https://www.kaggle.com/datasets/nelgiriyewithana/top-spotify-songs-2023	https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset
What a row is	one song on the list of 2023's most-streamed songs	one track, found by searching one genre
How popular	streams: total Spotify streams, all time	popularity: Spotify's 0-100 score, based mostly on total plays and how recent they are
Licence	"Other (specified in description)"; the description names none	BSD

Copies of the Kaggle file circulate on GitHub. Download from the publisher: a copy can be edited, and its page will not tell you.

The two files measure popularity differently. Streams count every play since release. Popularity is Spotify's own score, weighted toward recent plays. Neither is the other, and the case keeps them apart.

SIDE 0 · PART 02

Naming conventions

One naming rule for both files, applied once, right after loading.

The two files name the same things differently (track_name in both, but artist(s)_name in one and artists in the other; danceability_% on a 0-100 scale in one and danceability on a 0-1 scale in the other). We rename every column once, right after loading, through one explicit mapping per file, so that every line after this cell uses our names:

• lower_snake_case, no punctuation: artist(s)_name becomes artists.
• The unit in the name: danceability_pct (0-100), duration_s, tempo_bpm, loudness_db, streams_millions.
• The same quantity gets the same name and unit in both files. The catalog's 0-1 audio features are converted to 0-100, so danceability_pct means the same thing wherever it appears.
• Columns we do not use are dropped at the rename, so nothing downstream depends on them.

Units are converted after renaming, so the conversion lines already use our names.

Show the code

import hashlib
import zipfile
from datetime import datetime, timezone
from pathlib import Path
from urllib.request import urlretrieve

import pandas as pd

raw = Path("data/raw")
raw.mkdir(parents=True, exist_ok=True)

import sys
sys.path.append(".claude/skills/fresh-figures/scripts")
import fresh_style as ms  # added: one style for every table, as in the final notebook

ms.use_theme()

{'name': 'drvas-mixtape',
 'description': 'Dr. Vas brand, dark, with an 80s cassette J-card label',
 'mode': 'dark',
 'colors': {'bg': '#0a0a0a',
  'ink': '#e9e2d0',
  'muted': '#a8a29a',
  'grid': '#262626',
  'context': '#626262',
  'highlight': '#ff3da6',
  'categorical': ['#ff3da6', '#5bcefa', '#d4ff00', '#ff9e2c']},
 'fonts': {'title': 'Montserrat', 'body': 'Space Grotesk', 'tag': 'Bungee'},
 'label': {'stripes': ['#ff3da6', '#5bcefa', '#d4ff00'],
  'tag': 'SIDE {side} · TRACK {n:02d}'}}

SIDE 0 · PART 03

The request

Both files downloaded unchanged; every change after this happens in code you can read.

Both publishers allow anonymous downloads. The raw files are saved unchanged in data/raw/; every change we make happens after loading, in code you can read.

Show the code

KAGGLE_URL = "https://www.kaggle.com/api/v1/datasets/download/nelgiriyewithana/top-spotify-songs-2023"
HF_URL = ("https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset"
          "/resolve/main/dataset.csv")

pulled_at = datetime.now(timezone.utc)
urlretrieve(KAGGLE_URL, raw / "top-spotify-songs-2023.zip")
urlretrieve(HF_URL, raw / "spotify-tracks-dataset.csv")

with zipfile.ZipFile(raw / "top-spotify-songs-2023.zip") as z:
    member = z.infolist()[0]
    z.extract(member, raw)
    file_dated = datetime(*member.date_time)

display(ms.table(pd.Series({"file in the Kaggle archive": member.filename,
           "date of that file": f"{file_dated:%Y-%m-%d}"}, name="Kaggle download")))

item	Kaggle download
file in the Kaggle archive	spotify-2023.csv
date of that file	2023-08-26

SIDE 0 · PART 04

Loading

The Kaggle file isn't valid UTF-8: the first sign that its names are damaged.

The Kaggle file is not valid UTF-8: reading it with the default settings stops with a UnicodeDecodeError. The audit below shows why. encoding_errors="replace" lets it load and marks every unreadable byte with the replacement character �, so the damage stays visible.

Show the code

songs_raw = pd.read_csv(raw / "spotify-2023.csv", encoding_errors="replace")
catalog_raw = pd.read_csv(raw / "spotify-tracks-dataset.csv", index_col=0)

display(ms.table(pd.DataFrame({"rows": [len(songs_raw), len(catalog_raw)],
              "columns": [songs_raw.shape[1], catalog_raw.shape[1]]},
             index=["songs file (Kaggle)", "catalog (Hugging Face)"])))

item	rows	columns
songs file (Kaggle)	953	24
catalog (Hugging Face)	114,000	20

SIDE 0 · PART 05

Renaming

One mapping per file; everything after this uses our names and units.

One mapping per file. The keys are the publishers' names; everything after this cell uses the values.

Show the code

SONGS_NAMES = {
    "track_name": "track",
    "artist(s)_name": "artists",
    "artist_count": "n_artists",
    "released_year": "release_year",
    "released_month": "release_month",
    "released_day": "release_day",
    "in_spotify_playlists": "spotify_playlists",
    "streams": "streams_millions",
    "bpm": "tempo_bpm",
    "mode": "mode",
    "danceability_%": "danceability_pct",
    "valence_%": "valence_pct",
    "energy_%": "energy_pct",
    "acousticness_%": "acousticness_pct",
    "instrumentalness_%": "instrumentalness_pct",
    "liveness_%": "liveness_pct",
    "speechiness_%": "speechiness_pct",
}

CATALOG_NAMES = {
    "track_id": "track_id",
    "track_name": "track",
    "artists": "artists",
    "album_name": "album",
    "track_genre": "genre",
    "popularity": "popularity",
    "duration_ms": "duration_s",
    "explicit": "explicit",
    "tempo": "tempo_bpm",
    "loudness": "loudness_db",
    "mode": "mode",
    "danceability": "danceability_pct",
    "valence": "valence_pct",
    "energy": "energy_pct",
    "acousticness": "acousticness_pct",
    "instrumentalness": "instrumentalness_pct",
    "liveness": "liveness_pct",
    "speechiness": "speechiness_pct",
}

songs = songs_raw.rename(columns=SONGS_NAMES)[list(SONGS_NAMES.values())]
catalog = catalog_raw.rename(columns=CATALOG_NAMES)[list(CATALOG_NAMES.values())]
songs.columns.tolist()

['track',
 'artists',
 'n_artists',
 'release_year',
 'release_month',
 'release_day',
 'spotify_playlists',
 'streams_millions',
 'tempo_bpm',
 'mode',
 'danceability_pct',
 'valence_pct',
 'energy_pct',
 'acousticness_pct',
 'instrumentalness_pct',
 'liveness_pct',
 'speechiness_pct']

Now the unit conversions, using our names. The catalog's audio features go from 0-1 to 0-100 to match the songs file; its durations go from milliseconds to seconds; the catalog's mode is coded 1/0 and becomes the words the songs file uses.

Show the code

features = ["danceability_pct", "valence_pct", "energy_pct", "acousticness_pct",
            "instrumentalness_pct", "liveness_pct", "speechiness_pct"]
catalog[features] = catalog[features] * 100
catalog["duration_s"] = catalog["duration_s"] / 1000
catalog["mode"] = catalog["mode"].map({1: "Major", 0: "Minor"})

display(ms.table(catalog[["danceability_pct", "duration_s", "mode"]].describe(include="all").loc[["count", "mean", "min", "max", "top"]]))

item	danceability_pct	duration_s	mode
count	114,000	114,000	114000
mean	56.68	228.029	nan
min	0	0	nan
max	98.5	5,237.3	nan
top			Major

SIDE 0 · PART 06

The audit

Seven problems that raise no error, each of which would quietly produce a wrong number.

None of the items below raises an error. Each one would quietly produce a wrong number if it went unnoticed.

1. One stream count is not a number

Convert the streams column to numbers, and list the rows that do not convert.

Show the code

as_number = pd.to_numeric(songs["streams_millions"], errors="coerce")
display(ms.table(songs.loc[as_number.isna(), ["track", "artists", "streams_millions"]]))

item	track	artists	streams_millions
574	Love Grows (Where My Rosemary Goes)	Edison Lighthouse	BPM110KeyAModeMajorDanceability53Valence75Energy69Acousticness7Instrumentalness0Liveness17Speechiness3

What we found. One row's streams field holds the song's audio features run together as text ("BPM110KeyAModeMajorDanceability53..."). It was pasted into the wrong column somewhere upstream, and that one value makes pandas read the whole column as text. The row's real stream count is unknown.

Analyst decision. Set that stream count to missing, keep the song, and convert the column to millions of streams.

Show the code

songs["streams_millions"] = as_number / 1e6
display(ms.table(songs.nsmallest(3, "streams_millions")[["track", "artists", "release_year", "streams_millions", "spotify_playlists"]]))

item	track	artists	release_year	streams_millions	spotify_playlists
123	Que Vuelvas	Carin Leon, Grupo Frontera	2,022	0.002762	763
393	Jhoome Jo Pathaan	Arijit Singh, Vishal Dadlani, Sukriti Kakar, Vishal-Shekhar, Shekhar Ravjiani, Kumaar	2,022	1.36518	138
144	QUEMA	Sog, Ryan Castro, Peso Pluma	2,023	11.5994	437

2. One stream count is impossible

The table above lists the three smallest stream counts on the list.

What we found. "Que Vuelvas" is listed with 0.003 million streams, 2,762 in all, while sitting on 763 Spotify playlists. A song on a list of the year's most-streamed songs does not have 2,762 plays; the next smallest count is 1.4 million.

Analyst decision. The true value is unknown, so it becomes missing too.

Show the code

songs.loc[songs["streams_millions"] < 0.01, "streams_millions"] = float("nan")
display(ms.table(pd.Series({"songs with no stream count": songs["streams_millions"].isna().sum()}, name="songs")))

item	songs
songs with no stream count	2

3. Accented names were damaged before the file was published

Count the songs whose track or artist name holds the replacement character.

Show the code

damaged = songs["track"].str.contains("\ufffd") | songs["artists"].str.contains("\ufffd")
display(ms.table(pd.Series({"songs with a damaged track or artist name": damaged.sum()}, name="songs")))

item	songs
songs with a damaged track or artist name	109

The first five:

Show the code

display(ms.table(songs.loc[damaged, ["track", "artists"]].head()))

item	track	artists
21	I Can See You (Taylor���s Version) (From The	Taylor Swift
26	Calm Down (with Selena Gomez)	R��ma, Selena G
36	Fr��gil (feat. Grupo Front	Yahritza Y Su Esencia, Grupo Frontera
60	T��	dennis, MC Kevin o Chris
63	BESO	Rauw Alejandro, ROSAL�

What we found. 109 songs have a damaged name. Every accented letter was already replaced before the file reached Kaggle ("Taylor's Version" became "Taylor���s Version"), and some names were cut off mid-character. No choice of encoding recovers them. They matter only when we try to match these songs by name to another file, which the case does once.

4. Four songs appear twice

List every song whose track name and artist appear on more than one row.

Show the code

repeated = songs[songs.duplicated(["track", "artists"], keep=False)]
display(ms.table(repeated.sort_values("track")[["track", "artists", "release_year", "streams_millions", "spotify_playlists"]]))

item	track	artists	release_year	streams_millions	spotify_playlists
372	About Damn Time	Lizzo	2,022	723.894	2,332
764	About Damn Time	Lizzo	2,022	723.894	9,021
178	SNAP	Rosa Linn	2,022	726.307	3,202
873	SNAP	Rosa Linn	2,022	711.367	1,818
345	SPIT IN MY FACE!	ThxSoMch	2,022	303.216	629
482	SPIT IN MY FACE!	ThxSoMch	2,022	301.87	573
512	Take My Breath	The Weeknd	2,021	130.656	2,597
616	Take My Breath	The Weeknd	2,021	432.702	6,392

What we found. Each pair shares a track name and artist but differs elsewhere: "About Damn Time" has the same stream count twice but two different playlist counts, and the other three pairs have different stream counts. They are probably two releases of the same song, or one song entered twice.

Analyst decision. A song listed twice would count twice in every summary, so keep the row with more streams for each.

Show the code

songs = (songs.sort_values("streams_millions", ascending=False)
              .drop_duplicates(["track", "artists"])
              .sort_index())
display(ms.table(pd.Series({"songs before": len(songs_raw), "songs after removing repeats": len(songs)}, name="songs")))

item	songs
songs before	953
songs after removing repeats	949

5. The file never says when it was collected

A stream count is a running total, so it depends on when it was read. Build each song's release date, and compare the latest one with the date of the file.

Show the code

songs["release_date"] = pd.to_datetime(
    songs[["release_year", "release_month", "release_day"]].rename(
        columns={"release_year": "year", "release_month": "month", "release_day": "day"}))
display(ms.table(pd.Series({"latest release in the file": f"{songs['release_date'].max():%Y-%m-%d}",
           "date of the file in the archive": f"{file_dated:%Y-%m-%d}"}, name="date")))

item	date
latest release in the file	2023-07-14
date of the file in the archive	2023-08-26

What we found. The Kaggle page gives no collection date. The latest release in the file is 14 July 2023 and the file itself is dated 26 August 2023, so the counts were read between those two dates. A song released in July 2023 had had a few weeks to collect streams; a song from 2010, more than a decade.

6. The catalog lists many tracks more than once

Count rows, tracks and genres in the catalog.

Show the code

per_genre = catalog["genre"].value_counts()
display(ms.table(pd.Series({
    "rows": len(catalog),
    "unique tracks": catalog["track_id"].nunique(),
    "tracks listed under more than one genre": (catalog["track_id"].value_counts() > 1).sum(),
    "genres": catalog["genre"].nunique(),
    "fewest rows in a genre": per_genre.min(),
    "most rows in a genre": per_genre.max(),
    "rows with no track name": catalog["track"].isna().sum(),
})))

item	0
rows	114,000
unique tracks	89,741
tracks listed under more than one genre	16,641
genres	114
fewest rows in a genre	1,000
most rows in a genre	1,000
rows with no track name	1

What we found. Four facts about how the catalog was built:

• A row is a search result, not a track. The collector searched Spotify one genre at a time, so a track found under two genres appears twice: 16,641 tracks do. Questions about tracks need one row per track; questions about genres can use every row.
• Every genre has exactly 1,000 rows. That number was chosen by the collector, so the catalog's mix of genres says nothing about how much of each genre exists or gets played.
• The dataset card says 125 genres; the file has 114.
• One row has no track name or artist.

Analyst decision. Drop the row with no name; it cannot be matched or identified.

Show the code

catalog = catalog.dropna(subset=["track", "artists"])
display(ms.table(pd.Series({"catalog rows": len(catalog)}, name="rows")))

item	rows
catalog rows	113,999

7. Popularity zero is common

Spotify's popularity score runs from 0 to 100. Find the share of catalog rows at exactly 0.

Show the code

display(ms.table(pd.Series({"share of catalog rows with popularity 0": (catalog["popularity"] == 0).mean().round(3)}, name="rows")))

item	rows
share of catalog rows with popularity 0	0.141

What we found. 14% of catalog rows have a popularity of 0. Spotify's score is relative and weighted toward recent plays, so a track nobody played recently scores 0 even if it was once popular. Zero here means "not played much lately", not "never played", and it is a real value.

SIDE 0 · PART 07

Missing values

A blank means not recorded, not zero. And some zeros are really blanks.

A blank cell means the value was not recorded. It does not mean zero, and it does not mean the thing did not happen. pandas skips blanks without a warning: median() and corr() use the values that are there, and a model refuses to fit until the blanks are removed. A comparison can therefore run on fewer rows than the file holds, and if the rows with blanks differ from the rest, on a different kind of row.

Four checks on the two tables as cleaned above:

1. how many values each column is missing, as a count and as a share of rows;
2. whether a column records 0 where it means "not measured";
3. which rows have the gaps, and whether they differ from the rest;
4. what each finding means for the analysis.

Blank values in each column of the songs table:

Show the code

display(ms.table(pd.DataFrame({"blank values": songs.isna().sum(), "share of songs": songs.isna().mean().round(3)})))

item	blank values	share of songs
track	0	0
artists	0	0
n_artists	0	0
release_year	0	0
release_month	0	0
release_day	0	0
spotify_playlists	0	0
streams_millions	2	0.002
tempo_bpm	0	0
mode	0	0
danceability_pct	0	0
valence_pct	0	0
energy_pct	0	0
acousticness_pct	0	0
instrumentalness_pct	0	0
liveness_pct	0	0
speechiness_pct	0	0
release_date	0	0

The raw songs file had blanks in two more columns, which the rename dropped because the case does not use them:

Show the code

display(ms.table(songs_raw.isna().sum()[lambda blanks: blanks > 0].rename("blank values in the raw file")))

item	blank values in the raw file
in_shazam_charts	50
key	95

Blank values in each column of the catalog:

Show the code

display(ms.table(pd.DataFrame({"blank values": catalog.isna().sum(), "share of rows": catalog.isna().mean().round(3)})))

item	blank values	share of rows
track_id	0	0
track	0	0
artists	0	0
album	0	0
genre	0	0
popularity	0	0
duration_s	0	0
explicit	0	0
tempo_bpm	0	0
loudness_db	0	0
mode	0	0
danceability_pct	0	0
valence_pct	0	0
energy_pct	0	0
acousticness_pct	0	0
instrumentalness_pct	0	0
liveness_pct	0	0
speechiness_pct	0	0

Blank or zero. Neither table has a blank left in an audio feature, but a measurement can also be missing as a 0. A danceability of 0 would mean a track with no beat at all; a tempo of 0 beats per minute is not a tempo. Count the zeros in each numeric column of the catalog:

Show the code

numbers = catalog.select_dtypes("number")
display(ms.table(pd.DataFrame({"zeros": (numbers == 0).sum(), "blanks": numbers.isna().sum()})))

item	zeros	blanks
popularity	16,019	0
duration_s	0	0
tempo_bpm	157	0
loudness_db	0	0
danceability_pct	157	0
valence_pct	176	0
energy_pct	1	0
acousticness_pct	39	0
instrumentalness_pct	38,763	0
liveness_pct	2	0
speechiness_pct	157	0

Danceability, speechiness and tempo each have 157 zeros. Are they the same tracks? Compare the rows with a tempo of 0 with every other row:

Show the code

unmeasured = catalog["tempo_bpm"] == 0
rhythm = ["danceability_pct", "valence_pct", "speechiness_pct", "tempo_bpm"]
group = unmeasured.map({True: "tempo 0", False: "tempo recorded"})
display(ms.table(pd.DataFrame({
    "rows": group.value_counts(),
    "share with danceability, valence, speechiness and tempo all 0": (catalog[rhythm] == 0).all(axis=1).groupby(group).mean().round(3),
    "share in the sleep genre": (catalog["genre"] == "sleep").groupby(group).mean().round(3),
    "median energy (0-100)": catalog["energy_pct"].groupby(group).median().round(1),
    "median popularity (0-100)": catalog["popularity"].groupby(group).median(),
})))

tempo_bpm	rows	share with danceability, valence, speechiness and tempo all 0	share in the sleep genre	median energy (0-100)	median popularity (0-100)
tempo 0	157	1	0.879	0.1	35
tempo recorded	113,842	0	0.008	68.5	34

What we found. The songs table is missing 2 stream counts, the two set to missing in the audit (0.2% of songs), and nothing else. The raw file's other blanks, 95 musical keys and 50 Shazam chart positions, are in columns the case does not use. The catalog has no blanks left after the audit.

The catalog's zeros are another matter. In the same 157 rows, danceability, valence, speechiness and tempo are all exactly 0, while loudness and acousticness have values. 88% of these rows are in the sleep genre, against 0.8% of the other rows, and their median energy is 0.1 out of 100, against 68.5: they are steady, nearly silent sounds such as white noise, with no beat for Spotify's rhythm analysis to find. Those zeros are "not measured", not measurements of 0. They are not spread at random: they sit almost entirely in one genre. Their popularity is ordinary (a median of 35, against 34 for every other row), so leaving them out of the sound comparisons does not tilt the comparison of hits and misses.

Analyst decision. Set danceability, valence, speechiness and tempo to missing in those 157 rows, and keep the rows: their popularity and genre were recorded and are real. What this means for the analysis:

• Comparisons of streams use 947 of the 949 songs. median() and corr() skip the 2 blanks.
• Comparisons of how tracks sound use the tracks whose sound was measured, every track but these 157. The case notebook removes them in one stated step before any sound comparison.
• Comparisons of popularity by genre use every row, including these 157.

Show the code

catalog.loc[unmeasured, rhythm] = float("nan")
display(ms.table(catalog[rhythm].isna().sum().rename("blank values now")))

item	blank values now
danceability_pct	157
valence_pct	157
speechiness_pct	157
tempo_bpm	157

SIDE 0 · PART 08

Checks against what we already know

Eight facts we already know, checked against what arrived.

Show the code

top = songs.loc[songs["streams_millions"].idxmax()]
display(ms.table(pd.Series({
    "the songs file had 953 rows": len(songs_raw) == 953,
    "the catalog had 114,000 rows": len(catalog_raw) == 114000,
    "the most-streamed song is Blinding Lights": top["track"] == "Blinding Lights",
    "it has between 3.5 and 4 billion streams": 3500 < top["streams_millions"] < 4000,
    "danceability runs 0-100 in the songs file": songs["danceability_pct"].between(0, 100).all(),
    "danceability runs 0-100 in the catalog": catalog["danceability_pct"].dropna().between(0, 100).all(),
    "every release is before the file's date": (songs["release_date"] <= pd.Timestamp(file_dated)).all(),
    "no catalog tempo of 0 is left": not (catalog["tempo_bpm"] == 0).any(),
}, name="passed")))

item	passed
the songs file had 953 rows	True
the catalog had 114,000 rows	True
the most-streamed song is Blinding Lights	True
it has between 3.5 and 4 billion streams	True
danceability runs 0-100 in the songs file	True
danceability runs 0-100 in the catalog	True
every release is before the file's date	True
no catalog tempo of 0 is left	True

What we found. All eight checks pass: both files arrived with the row counts their publishers describe, Blinding Lights is the most-streamed song with between 3.5 and 4 billion streams, both files' danceability runs from 0 to 100 after the conversion, no song was released after the file's date, and no unmeasured tempo is left in the catalog.

SIDE 0 · PART 09

Repairing what the audit found

Evidence settles what the bytes can't: 71 song names come back.

The audit found the problems; this section fixes the ones that evidence can settle, under four rules:

1. Never overwrite the source. The raw downloads in data/raw/ stay as they are. Every fix happens here, in code.
2. Propose with evidence, then wait. Each fix was proposed with its evidence and a confidence level, and applied only after it was approved. The fixes are in data/data_fixes.csv, and the song-by-song name repairs are in data/name_repairs.csv. tools/repair_names.py made those proposals.
3. Flag every changed row. Each song keeps its original values (track_in_file, artists_in_file, release_year_in_file) and says what changed (name_repaired, repair_method, repair_confidence, fixes).
4. Document it in the data card at the end.

Audit item 3 revisited. The damaged names can't be recovered from the bytes, but most of them can be found again elsewhere. Each damaged name becomes a pattern: each run of � is one lost letter, and the name may be cut off. A repair is accepted only when exactly one name fits, either in the catalog, which holds many of the same songs with intact names, or in the song's official video, by title and channel. Only the damaged letters and the cut-off ending are filled in; the rest of the file's text stays as it was.

Show the code

# apply the approved fixes and show what changed.
import sys

sys.path.append("tools")
sys.path.append(".claude/skills/fresh-figures/scripts")
import fresh_style as ms
from apply_fixes import apply_fixes
from IPython.display import HTML

ms.use_theme()
fixes = pd.read_csv("data/data_fixes.csv")
display(ms.table(fixes[["id", "issue", "proposal", "confidence", "approved"]], index_label="fix"))

songs, catalog, _, changed = apply_fixes(songs, catalog)
display(ms.table(pd.Series({
    "F1 · names repaired from evidence": songs["fixes"].str.contains("F1").sum(),
    "F2 · implausible release year marked unknown": songs["fixes"].str.contains("F2").sum(),
    "F3 · stray spaces trimmed (songs and catalog rows)": songs["fixes"].str.contains("F3").sum()
                                                         + catalog["fixes"].str.contains("F3").sum(),
    "F8 · flagged: same artists as the catalog, in a different order": songs["artist_order_differs_from_catalog"].sum(),
    "names still damaged (no evidence settles them)": (songs["track"].str.contains("\ufffd")
                                                       | songs["artists"].str.contains("\ufffd")).sum(),
}, name="rows"), index_label="approved fix"))
display(HTML(ms.card(
    "Every changed song", f"{len(changed)} songs changed by approved fixes. Señorita, Don't Blame Me, "
    "CORAZÓN VACÍO, ROSALÍA and Beyoncé are back; the original text is kept beside each repair.",
    ms.table(changed.astype(object).fillna(""), index_label="row").to_html(), badge=f"{len(changed)} FIXED",
    hint="Open for every original and repair")))

fix	id	issue	proposal	confidence	approved
0	F1	Damaged names: accented letters lost and many names cut off before publishing (109 songs)	Fill damaged letters and cut-off endings from evidence, keeping the file's own text: 44 high-confidence repairs (catalog match or catalog artist list), 28 medium (official YouTube video title + channel). 37 stay as they are.	high/medium	yes, 2026-10-09
1	F2	"Agudo M��gi" (Styrx, utku INC, Thezth) is listed as released in 1930, the oldest song on the list	Set its release year to unknown and leave it out of the release-year groups, instead of guessing a year	medium	yes, 2026-10-09
2	F3	Names with spaces before or after them (6 song titles, 2 song artist lists, 8 catalog artist lists)	Trim the spaces	high	yes, 2026-10-09
3	F4	The same title and artists appear under several track IDs (4,657 titles), sometimes with very different popularity (773 differ by 50+ points)	No change. Document it: these are re-releases and versions. Matching already takes each song's most popular version.	high	yes, 2026-10-09
4	F5	Extreme lengths: 81 tracks over 20 minutes (DJ mixes up to 87 minutes), 16 under 30 seconds	No change. Document them as real recordings; they matter only for the duration feature in the Question 2 models.	high	yes, 2026-10-09
5	F6	Some artist lists are cut off too (3 end in '&'; 1 song's artist count disagrees with its list)	No change beyond F1. Document it: the publisher cut long text fields.	high	yes, 2026-10-09
6	F7	"traitor" (1.23 billion streams, 2021) is credited to Juan Cruz Toledo, Huilen Toledo; it is almost certainly Olivia Rodrigo's song from SOUR	No change (her call). Document the likely wrong credit.	high	yes, 2026-10-09 (document only)
7	F8	36 songs list the same artists as the catalog in a different order, so the match on title + first artist misses them	No change to the matching method. Flag them in a new column, artist_order_differs_from_catalog, and report the count where matching is used.	high	yes, 2026-10-09 (flag and document)

approved fix	rows
F1 · names repaired from evidence	71
F2 · implausible release year marked unknown	1
F3 · stray spaces trimmed (songs and catalog rows)	11
F8 · flagged: same artists as the catalog, in a different order	36
names still damaged (no evidence settles them)	40

75 FIXEDEvery changed song

75 songs changed by approved fixes. Señorita, Don't Blame Me, CORAZÓN VACÍO, ROSALÍA and Beyoncé are back; the original text is kept beside each repair.

▸ Open for every original and repair

row	track_in_file	track	artists_in_file	artists	release_year_in_file	release_year	repair_method	repair_confidence	fixes
21	I Can See You (Taylor���s Version) (From The	I Can See You (Taylor’s Version) (From The	Taylor Swift	Taylor Swift	2,023	2023	official video	medium	F1
63	BESO	BESO	Rauw Alejandro, ROSAL�	Rauw Alejandro, ROSALÍA	2,023	2023	artist list	high	F1
79	CORAZ��N VA	CORAZÓN VACÍO	Maria Becerra	Maria Becerra	2,023	2023	official video	medium	F1
82	Novidade na ��	Novidade na área	Mc Livinho, DJ Matt D	Mc Livinho, DJ Matt D	2,023	2023	official video	medium	F1
93	Don���t Bl	Don’t Blame Me	Taylor Swift	Taylor Swift	2,017	2017	catalog	high	F1
118	VAGABUNDO	VAGABUNDO	Sebastian Yatra, Manuel Turizo, Be��	Sebastian Yatra, Manuel Turizo, Beéle	2,023	2023	artist list	high	F1
125	Feliz Cumplea��os Fe	Feliz Cumpleaños Ferxxo	Feid	Feid	2,022	2022	catalog	high	F1
141	Novo Balan�	Novo Balanço	Veigh, Bvga Beatz, Supernova Ent, Prod Malax	Veigh, Bvga Beatz, Supernova Ent, Prod Malax	2,023	2023	official video	medium	F1
146	Ojitos Lindos	Ojitos Lindos	Bomba Est��reo, Bad B	Bomba Estéreo, Bad B	2,022	2022	artist list	high	F1
174	������������	アイドル	YOASOBI	YOASOBI	2,023	2023	official video	medium	F1
191	You Belong With Me (Taylor���s Ve	You Belong With Me (Taylor’s Version)	Taylor Swift	Taylor Swift	2,021	2021	catalog	high	F1
202	Acr��s	Acróstico	Shakira	Shakira	2,023	2023	official video	medium	F1
212	Pi��man De�	Pişman Değilim	Semicenk, Do��u	Semicenk, Do��u	2,023	2023	catalog or official video, partly	medium	F1
231	CUFF IT	CUFF IT	Beyonc�	Beyoncé	2,022	2022	catalog	high	F1
236	AMERICA HAS A PROBLEM (feat. Kendrick Lamar)	AMERICA HAS A PROBLEM (feat. Kendrick Lamar)	Kendrick Lamar, Beyonc�	Kendrick Lamar, Beyoncé	2,023	2023	artist list	high	F1
247	Cart��o B	Cartão Black	MC Caverinha, KayBlack	MC Caverinha, KayBlack	2,023	2023	official video	medium	F1
249	Oi Balde - Ao Vivo	Oi Balde - Ao Vivo	Z�� Neto & Crist	Zé Neto & Cristiano	2,023	2023	artist list	high	F1
258	Conex��es de M��fia (feat. Rich	Conex��es de M��fia (feat. Rich	Rich The Kid, Matu�	Rich The Kid, Matu�	2,023	2023			F3
275	Igualito a Mi Ap�	Igualito a Mi Apá	Fuerza Regida, Peso Pluma	Fuerza Regida, Peso Pluma	2,022	2022	official video	medium	F1
278	DESPECH�	DESPECHÁ	ROSAL�	ROSALÍA	2,022	2022	official video	medium	F1
303	10:35	10:35	Ti��sto, Tate M	Tiësto, Tate M	2,022	2022	artist list	high	F1
346	PLAYA DEL INGL�	PLAYA DEL INGLÉS	Myke Towers, Quevedo	Myke Towers, Quevedo	2,022	2022	official video	medium	F1
351	PERO T�	PERO TÚ	Karol G, Quevedo	Karol G, Quevedo	2,023	2023	official video	medium	F1
354	Monoton�	Monotonía	Ozuna, Shakira	Ozuna, Shakira	2,022	2022	official video	medium	F1
355	���98 Braves	’98 Braves	Morgan Wallen	Morgan Wallen	2,023	2023	official video	medium	F1
364	Sem Alian��a no	Sem Aliança no	MC Xenon, Os Gemeos da Putaria	MC Xenon, Os Gemeos da Putaria	2,022	2022	official video	medium	F1
368	LA CANCI�	LA CANCIÓN	J Balvin, Bad Bunny	J Balvin, Bad Bunny	2,019	2019	catalog	high	F1
369	Qu�� Ago	Qué Agonía	Yuridia, Angela Aguilar	Yuridia, Angela Aguilar	2,022	2022	official video	medium	F1
374	���������������������	へでもねーよ - LASA edit	Fujii Kaze	Fujii Kaze	2,020	2020	catalog	high	F1
375	Besos Moja2	Besos Moja2	Wisin & Yandel, ROSAL�	Wisin & Yandel, ROSALÍA	2,022	2022	catalog	high	F1
380	LLYLM	LLYLM	ROSAL�	ROSALÍA	2,023	2023	artist list	high	F1
411	MIDDLE OF THE NIGHT	MIDDLE OF THE NIGHT	Elley Duh�	Elley Duhé	2,020	2020	catalog	high	F1
431	Evergreen (You Didn���t Deserve Me A	Evergreen (You Didn’t Deserve Me At	Omar Apollo	Omar Apollo	2,022	2022	official video	medium	F1
439	Agudo M��gi	Agudo M��gi	Styrx, utku INC, Thezth	Styrx, utku INC, Thezth	1,930				F2
445	It's Beginning To Look A Lot Like Christmas	It's Beginning To Look A Lot Like Christmas	Michael Bubl�	Michael Bublé	2,011	2011	artist list	high	F1
452	Holly Jolly Christmas	Holly Jolly Christmas	Michael Bubl�	Michael Bublé	2,011	2011	catalog	high	F1
488	Tubar��o Te	Tubarão Te	Dj LK da Esc��cia, Tchakabum, mc jhenny, M	Dj LK da Esc��cia, Tchakabum, mc jhenny, M	2,022	2022	catalog or official video, partly	medium	F1
499	GAT��	GATÚBELA	Maldy, Karol G	Maldy, Karol G	2,022	2022	official video	medium	F1
526	Beggin	Beggin'	M��ne	Måneskin	2,017	2017	catalog	high	F1
530	The Motto	The Motto	Ti��sto, Ava	Tiësto, Ava	2,021	2021	artist list	high	F1
536	Malvad��	Malvadão	Xam��, Gustah, Neo B	Xamã, Gustah, Neo B	2,021	2021	official video	medium	F1
543	Medallo	Medallo	Justin Quiles, Lenny Tav��rez, BL	Justin Quiles, Lenny Tavárez, BL	2,021	2021	artist list	high	F1
547	Volv�	Volví	Aventura, Bad Bunny	Aventura, Bad Bunny	2,021	2021	catalog	high	F1
573	Don't Be Shy	Don't Be Shy	Ti��sto, Kar	Tiësto, Kar	2,021	2021	catalog	high	F1
575	LA FAMA (with The Weeknd)	LA FAMA (with The Weeknd)	The Weeknd, ROSAL�	The Weeknd, ROSALÍA	2,021	2021	artist list	high	F1
600	I WANNA BE YOUR SLAVE	I WANNA BE YOUR SLAVE	M��ne	Måneskin	2,021	2021	catalog	high	F1
601	Vai L�� Em Casa	Vai Lá Em Casa	Mar��lia Mendon��a, George Henrique &	Marília Mendonça, George Henrique &	2,021	2021	official video	medium	F1
618	C��	Cúrame	Rauw Alejandro	Rauw Alejandro	2,021	2021	catalog	high	F1
622	Qu�� M��	Qué Más Pues?	J Balvin, Maria Becerra	J Balvin, Maria Becerra	2,021	2021	catalog	high	F1
642	Se Le Ve	Se Le Ve	Arcangel, De La Ghetto, Justin Quiles, Lenny Tav��rez, Sech, Dalex, Dimelo Flow, Rich Music	Arcangel, De La Ghetto, Justin Quiles, Lenny Tavárez, Sech, Dalex, Dimelo Flow, Rich Music	2,021	2021	artist list	high	F1
644	When I���m Gone (with Katy	When I���m Gone (with Katy	Katy Perry, Alesso	Katy Perry, Alesso	2,021	2021			F3
645	Esque��a-Me Se For C	Esqueça-Me Se For Capaz	Mar��lia Mendon��a, Maiara &	Marília Mendonça, Maiara &	2,021	2021	official video	medium	F1
646	Mi��n	Miénteme	Tini, Maria Becerra	Tini, Maria Becerra	2,021	2021	official video	medium	F1
679	Problem�	Problemón	Alvaro Diaz, Rauw Alejandro	Alvaro Diaz, Rauw Alejandro	2,021	2021	catalog	high	F1
688	Mal Feito - Ao Vivo	Mal Feito - Ao Vivo	Mar��lia Mendon��a, Hugo & G	Marília Mendonça, Hugo & G	2,022	2022	artist list	high	F1
693	Se��o	Señorita	Shawn Mendes, Camila Cabello	Shawn Mendes, Camila Cabello	2,019	2019	catalog	high	F1
700	Una Noche en Medell�	Una Noche en Medellín	Cris Mj	Cris Mj	2,022	2022	official video	medium	F1
732	X ��LTIMA	X ÚLTIMA VEZ	Daddy Yankee, Bad Bunny	Daddy Yankee, Bad Bunny	2,022	2022	catalog	high	F1
742	Vampiro	Vampiro	Matu��, Wiu,	Matu��, Wiu,	2,022	2022			F3
745	Me Arrepent�	Me Arrepentí	Ak4:20, Cris Mj, Pailita	Ak4:20, Cris Mj, Pailita	2,022	2022	official video	medium	F1
748	RUMBAT�	RUMBATÓN	Daddy Yankee	Daddy Yankee	2,022	2022	catalog	high	F1
749	sentaDONA (Remix) s2	sentaDONA (Remix) s2	Lu��sa Sonza, MC Frog, Dj Gabriel do Borel, Davi K	Luísa Sonza, MC Frog, Dj Gabriel do Borel, Davi K	2,022	2022	artist list	high	F1
759	Nobody Like U - From "Turning Red"	Nobody Like U - From "Turning Red"	Jordan Fisher, Josh Levi, Finneas O'Connell, 4*TOWN (From Disney and Pixar���s Turning Red), Topher Ngo, Grayson Vill	Jordan Fisher, Josh Levi, Finneas O'Connell, 4*TOWN (From Disney and Pixar’s Turning Red), Topher Ngo, Grayson Vill	2,022	2022	artist list	high	F1
782	Otro Atardecer	Otro Atardecer	Bad Bunny, The Mar��	Bad Bunny, The Marías	2,022	2022	catalog	high	F1
788	Ens����ame	Enséñame a Bailar	Bad Bunny	Bad Bunny	2,022	2022	catalog	high	F1
789	El Apag�	El Apagón	Bad Bunny	Bad Bunny	2,022	2022	catalog	high	F1
798	SUPERMODEL	SUPERMODEL	M��ne	Måneskin	2,022	2022	catalog	high	F1
863	BREAK MY SOUL	BREAK MY SOUL	Beyonc�	Beyoncé	2,022	2022	artist list	high	F1
869	Layla	Layla	Sch��rze, DJ R	Schürze, DJ R	2,022	2022	artist list	high	F1
881	c��mo dormi	cómo dormiste?	Rels B	Rels B	2,022	2022	catalog	high	F1
885	Si Te La Encuentras Por Ah�	Si Te La Encuentras Por Ahí	Feid	Feid	2,022	2022	catalog	high	F1
887	ALIEN SUPERSTAR	ALIEN SUPERSTAR	Beyonc�	Beyoncé	2,022	2022	artist list	high	F1
913	XQ Te Pones As�	XQ Te Pones Así	Yandel, Feid	Yandel, Feid	2,022	2022	official video	medium	F1
915	Sin Se��	Sin Señal	Ovy On The Drums, Quevedo	Ovy On The Drums, Quevedo	2,022	2022	official video	medium	F1
918	THE LONELIEST	THE LONELIEST	M��ne	Måneskin	2,022	2022	catalog	high	F1

What we found. 109 songs arrived with damaged names. Evidence repaired 71: Se��o is Señorita again, Don���t Bl is Don't Blame Me, and ROSAL� is ROSALÍA. The other 38 stay as they are, because no source settles them. One release year, 1930 for a modern phonk track, is now marked unknown rather than guessed. Analyst decision: apply the approved fixes, keep every original value beside its repair, and flag each changed row so any later table or chart can account for it.

SIDE 0 · PART 10

Saving, with a provenance record

The clean files, and the record of where they came from and how they changed.

Show the code

songs.to_csv("data/songs_2023.csv", index=False)
catalog.to_csv("data/catalog.csv", index=False)


def sha256(path):
    return hashlib.sha256(Path(path).read_bytes()).hexdigest()


provenance = f"""# Provenance

Pulled {pulled_at:%Y-%m-%d %H:%M} UTC by the data notebook.

## data/songs_2023.csv
- Source: Most Streamed Spotify Songs 2023, Nidula Elgiriyewithana, Kaggle
  ({KAGGLE_URL})
- Raw file: data/raw/{member.filename}, dated {file_dated:%Y-%m-%d} in the archive,
  sha256 {sha256(raw / member.filename)}
- Collection date: not stated by the publisher; between the latest release
  ({songs['release_date'].max():%Y-%m-%d}) and the file date.
- Read with encoding_errors="replace": {damaged.sum()} damaged names, not recoverable.
- streams converted to millions; one text value and one impossible value (2,762) set to missing.
- Duplicate track and artist pairs: kept the row with more streams. Rows: {len(songs_raw)} -> {len(songs)}.

## data/catalog.csv
- Source: Spotify Tracks Dataset, Maharshi Pandya, Hugging Face, BSD licence
  ({HF_URL})
- Raw file: data/raw/spotify-tracks-dataset.csv, sha256 {sha256(raw / 'spotify-tracks-dataset.csv')}
- One row per track per genre searched: {len(catalog)} rows, {catalog['track_id'].nunique()} unique tracks,
  {catalog['genre'].nunique()} genres of 1,000 rows each. Dropped 1 row with no name.
- {unmeasured.sum()} rows whose rhythm analysis returned nothing (danceability, valence,
  speechiness and tempo all 0): those four features set to missing; the rows are kept.
- Audio features converted from 0-1 to 0-100; duration from ms to s; mode 1/0 to Major/Minor.
"""
Path("data/PROVENANCE.md").write_text(provenance)
print(provenance)

# Provenance

Pulled 2026-10-10 12:01 UTC by the data notebook.

## data/songs_2023.csv
- Source: Most Streamed Spotify Songs 2023, Nidula Elgiriyewithana, Kaggle
  (https://www.kaggle.com/api/v1/datasets/download/nelgiriyewithana/top-spotify-songs-2023)
- Raw file: data/raw/spotify-2023.csv, dated 2023-08-26 in the archive,
  sha256 26fd1292465877c24a9f5bfc6bf933c0715356a9c7d3197f2c4bc8b1a668a215
- Collection date: not stated by the publisher; between the latest release
  (2023-07-14) and the file date.
- Read with encoding_errors="replace": 109 damaged names, not recoverable.
- streams converted to millions; one text value and one impossible value (2,762) set to missing.
- Duplicate track and artist pairs: kept the row with more streams. Rows: 953 -> 949.

## data/catalog.csv
- Source: Spotify Tracks Dataset, Maharshi Pandya, Hugging Face, BSD licence
  (https://huggingface.co/datasets/maharshipandya/spotify-tracks-dataset/resolve/main/dataset.csv)
- Raw file: data/raw/spotify-tracks-dataset.csv, sha256 b202fa49909b2d5cef71a04b1d21243cfeb36414535f2ca9272aa646721177bd
- One row per track per genre searched: 113999 rows, 89740 unique tracks,
  114 genres of 1,000 rows each. Dropped 1 row with no name.
- 157 rows whose rhythm analysis returned nothing (danceability, valence,
  speechiness and tempo all 0): those four features set to missing; the rows are kept.
- Audio features converted from 0-1 to 0-100; duration from ms to s; mode 1/0 to Major/Minor.

Show the code

# add the approved fixes to the provenance record, so the saved files say how they differ from the raw ones.
note = "\n## Approved fixes\n" + "".join(
    f"- {row.id}: {row.issue}. {row.proposal} (approved {row.approved.removeprefix('yes, ')})\n"
    for row in fixes.itertuples()) + "- Song-by-song name repairs, with evidence: data/name_repairs.csv\n"
with open("data/PROVENANCE.md", "a") as f:
    f.write(note)
print(note)

## Approved fixes
- F1: Damaged names: accented letters lost and many names cut off before publishing (109 songs). Fill damaged letters and cut-off endings from evidence, keeping the file's own text: 44 high-confidence repairs (catalog match or catalog artist list), 28 medium (official YouTube video title + channel). 37 stay as they are. (approved 2026-10-09)
- F2: "Agudo M��gi" (Styrx, utku INC, Thezth) is listed as released in 1930, the oldest song on the list. Set its release year to unknown and leave it out of the release-year groups, instead of guessing a year (approved 2026-10-09)
- F3: Names with spaces before or after them (6 song titles, 2 song artist lists, 8 catalog artist lists). Trim the spaces (approved 2026-10-09)
- F4: The same title and artists appear under several track IDs (4,657 titles), sometimes with very different popularity (773 differ by 50+ points). No change. Document it: these are re-releases and versions. Matching already takes each song's most popular version. (approved 2026-10-09)
- F5: Extreme lengths: 81 tracks over 20 minutes (DJ mixes up to 87 minutes), 16 under 30 seconds. No change. Document them as real recordings; they matter only for the duration feature in the Question 2 models. (approved 2026-10-09)
- F6: Some artist lists are cut off too (3 end in '&'; 1 song's artist count disagrees with its list). No change beyond F1. Document it: the publisher cut long text fields. (approved 2026-10-09)
- F7: "traitor" (1.23 billion streams, 2021) is credited to Juan Cruz Toledo, Huilen Toledo; it is almost certainly Olivia Rodrigo's song from SOUR. No change (her call). Document the likely wrong credit. (approved 2026-10-09 (document only))
- F8: 36 songs list the same artists as the catalog in a different order, so the match on title + first artist misses them. No change to the matching method. Flag them in a new column, artist_order_differs_from_catalog, and report the count where matching is used. (approved 2026-10-09 (flag and document))
- Song-by-song name repairs, with evidence: data/name_repairs.csv

SIDE 0 · PART 11

Writing the data card

The card shown at the top, saved as data/DATA_CARD.md so it travels with the files.

Show the code

# write the data card to a file, for anyone who reads the data without this notebook.
from data_card import CARD, build

CARD.write_text(build())
print(f"wrote {CARD}")

wrote data/DATA_CARD.md