2 Tabular data and summary statistics with pandas

2.1 What is pandas?

The standard Python library for working with tabular data

Read data from CSV, Excel, SQL…
Store data in DataFrames
Select, filter, transform, and summarise data
Perform fast operations
Create simple plots

2.2 DataFrames

A DataFrame is like an Excel sheet.

Rows = observations
Columns = variables
Each column has a name and a type

In this class, we’ll only consider tabular data. So no images, audio, or video.

Reading a file. Use pd.read_csv() to load a DataFrame from a CSV file.

Note

There are many options to customise the reading process, but the defaults are usually good enough, see docs

import pandas as pd

url = "https://raw.githubusercontent.com/rfordatascience/tidytuesday/main/data/2020/2020-01-21/spotify_songs.csv"
df = pd.read_csv(url)

df.head()

	track_id	track_name	track_artist	track_popularity	track_album_id	track_album_name	track_album_release_date	playlist_name	playlist_id	playlist_genre	...	key	loudness	mode	speechiness	acousticness	instrumentalness	liveness	valence	tempo	duration_ms
0	6f807x0ima9a1j3VPbc7VN	I Don't Care (with Justin Bieber) - Loud Luxur...	Ed Sheeran	66	2oCs0DGTsRO98Gh5ZSl2Cx	I Don't Care (with Justin Bieber) [Loud Luxury...	2019-06-14	Pop Remix	37i9dQZF1DXcZDD7cfEKhW	pop	...	6	-2.634	1	0.0583	0.1020	0.000000	0.0653	0.518	122.036	194754
1	0r7CVbZTWZgbTCYdfa2P31	Memories - Dillon Francis Remix	Maroon 5	67	63rPSO264uRjW1X5E6cWv6	Memories (Dillon Francis Remix)	2019-12-13	Pop Remix	37i9dQZF1DXcZDD7cfEKhW	pop	...	11	-4.969	1	0.0373	0.0724	0.004210	0.3570	0.693	99.972	162600
2	1z1Hg7Vb0AhHDiEmnDE79l	All the Time - Don Diablo Remix	Zara Larsson	70	1HoSmj2eLcsrR0vE9gThr4	All the Time (Don Diablo Remix)	2019-07-05	Pop Remix	37i9dQZF1DXcZDD7cfEKhW	pop	...	1	-3.432	0	0.0742	0.0794	0.000023	0.1100	0.613	124.008	176616
3	75FpbthrwQmzHlBJLuGdC7	Call You Mine - Keanu Silva Remix	The Chainsmokers	60	1nqYsOef1yKKuGOVchbsk6	Call You Mine - The Remixes	2019-07-19	Pop Remix	37i9dQZF1DXcZDD7cfEKhW	pop	...	7	-3.778	1	0.1020	0.0287	0.000009	0.2040	0.277	121.956	169093
4	1e8PAfcKUYoKkxPhrHqw4x	Someone You Loved - Future Humans Remix	Lewis Capaldi	69	7m7vv9wlQ4i0LFuJiE2zsQ	Someone You Loved (Future Humans Remix)	2019-03-05	Pop Remix	37i9dQZF1DXcZDD7cfEKhW	pop	...	1	-4.672	1	0.0359	0.0803	0.000000	0.0833	0.725	123.976	189052

5 rows × 23 columns

Viewing the data. Use .head()/.tail() to see the top/bottom of your DataFrame

df.tail()

	track_id	track_name	track_artist	track_popularity	track_album_id	track_album_name	track_album_release_date	playlist_name	playlist_id	playlist_genre	...	key	loudness	mode	speechiness	acousticness	instrumentalness	liveness	valence	tempo	duration_ms
32828	7bxnKAamR3snQ1VGLuVfC1	City Of Lights - Official Radio Edit	Lush & Simon	42	2azRoBBWEEEYhqV6sb7JrT	City Of Lights (Vocal Mix)	2014-04-28	♥ EDM LOVE 2020	6jI1gFr6ANFtT8MmTvA2Ux	edm	...	2	-1.814	1	0.0936	0.076600	0.000000	0.0668	0.2100	128.170	204375
32829	5Aevni09Em4575077nkWHz	Closer - Sultan & Ned Shepard Remix	Tegan and Sara	20	6kD6KLxj7s8eCE3ABvAyf5	Closer Remixed	2013-03-08	♥ EDM LOVE 2020	6jI1gFr6ANFtT8MmTvA2Ux	edm	...	0	-4.462	1	0.0420	0.001710	0.004270	0.3750	0.4000	128.041	353120
32830	7ImMqPP3Q1yfUHvsdn7wEo	Sweet Surrender - Radio Edit	Starkillers	14	0ltWNSY9JgxoIZO4VzuCa6	Sweet Surrender (Radio Edit)	2014-04-21	♥ EDM LOVE 2020	6jI1gFr6ANFtT8MmTvA2Ux	edm	...	6	-4.899	0	0.0481	0.108000	0.000001	0.1500	0.4360	127.989	210112
32831	2m69mhnfQ1Oq6lGtXuYhgX	Only For You - Maor Levi Remix	Mat Zo	15	1fGrOkHnHJcStl14zNx8Jy	Only For You (Remixes)	2014-01-01	♥ EDM LOVE 2020	6jI1gFr6ANFtT8MmTvA2Ux	edm	...	2	-3.361	1	0.1090	0.007920	0.127000	0.3430	0.3080	128.008	367432
32832	29zWqhca3zt5NsckZqDf6c	Typhoon - Original Mix	Julian Calor	27	0X3mUOm6MhxR7PzxG95rAo	Typhoon/Storm	2014-03-03	♥ EDM LOVE 2020	6jI1gFr6ANFtT8MmTvA2Ux	edm	...	5	-4.571	0	0.0385	0.000133	0.341000	0.7420	0.0894	127.984	337500

5 rows × 23 columns

You can also look at the shape of the DataFrame and the column names.

print(df.columns)
print(df.shape)
print(df.dtypes)

Index(['track_id', 'track_name', 'track_artist', 'track_popularity',
       'track_album_id', 'track_album_name', 'track_album_release_date',
       'playlist_name', 'playlist_id', 'playlist_genre', 'playlist_subgenre',
       'danceability', 'energy', 'key', 'loudness', 'mode', 'speechiness',
       'acousticness', 'instrumentalness', 'liveness', 'valence', 'tempo',
       'duration_ms'],
      dtype='str')
(32833, 23)
track_id                        str
track_name                      str
track_artist                    str
track_popularity              int64
track_album_id                  str
track_album_name                str
track_album_release_date        str
playlist_name                   str
playlist_id                     str
playlist_genre                  str
playlist_subgenre               str
danceability                float64
energy                      float64
key                           int64
loudness                    float64
mode                          int64
speechiness                 float64
acousticness                float64
instrumentalness            float64
liveness                    float64
valence                     float64
tempo                       float64
duration_ms                   int64
dtype: object

To select a single column, use the column name in brackets:

df["track_name"]

0        I Don't Care (with Justin Bieber) - Loud Luxur...
1                          Memories - Dillon Francis Remix
2                          All the Time - Don Diablo Remix
3                        Call You Mine - Keanu Silva Remix
4                  Someone You Loved - Future Humans Remix
                               ...                        
32828                 City Of Lights - Official Radio Edit
32829                  Closer - Sultan & Ned Shepard Remix
32830                         Sweet Surrender - Radio Edit
32831                       Only For You - Maor Levi Remix
32832                               Typhoon - Original Mix
Name: track_name, Length: 32833, dtype: str

This returns a Series, which is like a single-column DataFrame. If the column name has spaces or special characters, you can also use the dot notation:

df.track_name

0        I Don't Care (with Justin Bieber) - Loud Luxur...
1                          Memories - Dillon Francis Remix
2                          All the Time - Don Diablo Remix
3                        Call You Mine - Keanu Silva Remix
4                  Someone You Loved - Future Humans Remix
                               ...                        
32828                 City Of Lights - Official Radio Edit
32829                  Closer - Sultan & Ned Shepard Remix
32830                         Sweet Surrender - Radio Edit
32831                       Only For You - Maor Levi Remix
32832                               Typhoon - Original Mix
Name: track_name, Length: 32833, dtype: str

To select multiple columns:

df[["track_name", "key", "tempo"]]

	track_name	key	tempo
0	I Don't Care (with Justin Bieber) - Loud Luxur...	6	122.036
1	Memories - Dillon Francis Remix	11	99.972
2	All the Time - Don Diablo Remix	1	124.008
3	Call You Mine - Keanu Silva Remix	7	121.956
4	Someone You Loved - Future Humans Remix	1	123.976
...	...	...	...
32828	City Of Lights - Official Radio Edit	2	128.170
32829	Closer - Sultan & Ned Shepard Remix	0	128.041
32830	Sweet Surrender - Radio Edit	6	127.989
32831	Only For You - Maor Levi Remix	2	128.008
32832	Typhoon - Original Mix	5	127.984

32833 rows × 3 columns

Variable types. In tabular data, a variable is either categorical or numerical. Different statistical methods apply to each type.

If a variable has a small number of possible values, we call it categorical: with/without kids, smoker/non-smoker, country of residence, etc. Also called qualitative or nominal variables (because they name categories).

Roughly, a variable is numerical if it can take many possible values, all of which are numbers. Also called quantitative or continuous variables.

A couple of remarks:

A good rule of thumb is that if you can compute an average, the variable is quantitative. If you want to count how many individuals fall into each category, the variable is categorical.
Some variables are numbers but can be treated as categorical. For example, a variable recording the number of children in a family can be categorical if we group families by that number (families with 0 children, families with 1 child, families with 2 children, etc.). Being categorical or quantitative depends on what we want to do with the variable, not on the values themselves.

An identifier variable is one where there are as many values as there are individuals. In general, identifiers are not useful for analysis. Even if they are numbers, we don’t compute averages of student IDs, and we don’t count how many students have each ID.

Ordinal variables are not numbers but have a natural order: e.g. “low”, “medium”, “high” or “freshman”, “sophomore”, “junior”, “senior”. Can be treated as categorical, but we can also compute averages of ordinal variables by assigning numbers to the categories (e.g. 1, 2, 3, 4 for school years). It all depends on the context.

Note

Assigning the correct type to each column helps you understand your data

Example. Convert a column with .astype(), e.g. key (currently a number) really is a category:

df["key"] = df["key"].astype("category")
df.dtypes

track_id                         str
track_name                       str
track_artist                     str
track_popularity               int64
track_album_id                   str
track_album_name                 str
track_album_release_date         str
playlist_name                    str
playlist_id                      str
playlist_genre                   str
playlist_subgenre                str
danceability                 float64
energy                       float64
key                         category
loudness                     float64
mode                           int64
speechiness                  float64
acousticness                 float64
instrumentalness             float64
liveness                     float64
valence                      float64
tempo                        float64
duration_ms                    int64
dtype: object

Let pandas guess the easy types for every column at once with .convert_dtypes():

df.convert_dtypes().dtypes

track_id                      string
track_name                    string
track_artist                  string
track_popularity               Int64
track_album_id                string
track_album_name              string
track_album_release_date      string
playlist_name                 string
playlist_id                   string
playlist_genre                string
playlist_subgenre             string
danceability                 Float64
energy                       Float64
key                         category
loudness                     Float64
mode                           Int64
speechiness                  Float64
acousticness                 Float64
instrumentalness             Float64
liveness                     Float64
valence                      Float64
tempo                        Float64
duration_ms                    Int64
dtype: object

You can get the top k largest or smallest values of a column with .nlargest() and .nsmallest():

df.nlargest(5, "danceability")[["track_name", "track_artist", "danceability"]]

	track_name	track_artist	danceability
31627	If Only I Could (feat. Steve Lucas) - Liem Remix	Fusion Groove Orchestra	0.983
27330	Mega Run - Downtown Jump	DJ ZsuZsu	0.981
806	Ice Ice Baby	Vanilla Ice	0.979
19253	Enseñame a Soñar - Original Mix	DJ Goozo	0.979
21220	Cha Cha Slide - Original Live Platinum Band Mix	DJ Casper	0.978

df.nsmallest(5, "tempo")[["track_name", "track_artist", "tempo"]]

	track_name	track_artist	tempo
11363	Hi, How're You Doin'?	DREAMS COME TRUE	0.000
1204	Knowing	Davina Leone	35.477
13764	Still Crazy After All These Years	Paul Simon	37.114
5629	autumn daze	charlie toØ human	38.985
25793	The Christmas Song	Nat King Cole	46.169

2.3 Statistics

Calculate the mean value for each column:

df.mean(numeric_only=True)

track_popularity        42.477081
danceability             0.654850
energy                   0.698619
loudness                -6.719499
mode                     0.565711
speechiness              0.107068
acousticness             0.175334
instrumentalness         0.084747
liveness                 0.190176
valence                  0.510561
tempo                  120.881132
duration_ms         225799.811622
dtype: float64

Works also for other statistics, e.g. the standard deviation:

df.std(numeric_only=True)

track_popularity       24.984074
danceability            0.145085
energy                  0.180910
loudness                2.988436
mode                    0.495671
speechiness             0.101314
acousticness            0.219633
instrumentalness        0.224230
liveness                0.154317
valence                 0.233146
tempo                  26.903624
duration_ms         59834.006182
dtype: float64

The function describe() gives a summary of statistics for each column:

df.describe()

	track_popularity	danceability	energy	loudness	mode	speechiness	acousticness	instrumentalness	liveness	valence	tempo	duration_ms
count	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000	32833.000000
mean	42.477081	0.654850	0.698619	-6.719499	0.565711	0.107068	0.175334	0.084747	0.190176	0.510561	120.881132	225799.811622
std	24.984074	0.145085	0.180910	2.988436	0.495671	0.101314	0.219633	0.224230	0.154317	0.233146	26.903624	59834.006182
min	0.000000	0.000000	0.000175	-46.448000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	4000.000000
25%	24.000000	0.563000	0.581000	-8.171000	0.000000	0.041000	0.015100	0.000000	0.092700	0.331000	99.960000	187819.000000
50%	45.000000	0.672000	0.721000	-6.166000	1.000000	0.062500	0.080400	0.000016	0.127000	0.512000	121.984000	216000.000000
75%	62.000000	0.761000	0.840000	-4.645000	1.000000	0.132000	0.255000	0.004830	0.248000	0.693000	133.918000	253585.000000
max	100.000000	0.983000	1.000000	1.275000	1.000000	0.918000	0.994000	0.994000	0.996000	0.991000	239.440000	517810.000000

Finally, for categorical variables, we can count how many individuals fall into each category with value_counts():

df["playlist_genre"].value_counts()

playlist_genre
edm      6043
rap      5746
pop      5507
r&b      5431
latin    5155
rock     4951
Name: count, dtype: int64

Or simply the number of unique values in a column with nunique():

df["playlist_genre"].nunique()

2.4 Learning resources

10 minutes to pandas

Data Science - NumPy, Matplotlib, and Pandas in Python (video)

Python Data Science Handbook