A Comparison of U.S. and Global TV Show Popularity: Regional Performance vs. US Weekly #1 Rankings, July 2021–December 2022
This report details an analysis derived from the all_weeks_countries SQL table, which tracks the weekly top 10 TV show rankings across various countries from July 2021 to December 2022. The study is designed to achieve three specific objectives:
Regional Performance of the US's Top Show: See how the top US show of the week ranks in major global regions during the same week.
Average Regional Rank: See average world region rankings over a 17-month period for shows that achieved a weekly number-one rank in the U.S.
Peak Performance Identification: Highlight the US number one show of the week that performed best and worst in the weekly regional rankings.
Load and view the original dataset
SELECT *
FROM all_weeks_countries
Wrangle the data
The initial phase of this analysis involves creating a pandas DataFrame containing a distinct list of countries from the SQL all_weeks_countries table.
-- Explore the data in the table
SELECT DISTINCT country_name
FROM all_weeks_countries;I then merge the dataset containing distinct countries with World Bank data to add a regional label for each country. The regional classifications will be refined to create new labels, including splitting 'America' into North, Central, and South, and separating 'Europe' into 'Europe' and 'East Europe'.
import pandas as pd
#merge the world bank region data to the pandas dataframe showing countries included in the tv ranking query. Only keep the country and regions column.
columns_to_keep = ['Entity', 'World regions according to WB']
# Source: WORLD BANK. "Our World in Data." https://ourworldindata.org/grapher/world-regions-according-to-the-world-bank?tab=table. Accessed on 02 OCT 2025
regions = pd.read_csv('regions.csv', usecols=columns_to_keep)
merged_df = pd.merge(df, regions, how='left', left_on='country_name', right_on='Entity')
# drop the country column
merged_df = merged_df.drop('Entity', axis='columns')
# relabel these countres as East Europe
east_europe_countries = [
'Romania', 'Serbia', 'Lithuania', 'Bulgaria', 'Croatia', 'Poland',
'Ukraine', 'Latvia', 'Slovenia', 'Estonia', 'Slovakia', 'Russia'
]
merged_df.loc[merged_df['country_name'].isin(east_europe_countries), 'World regions according to WB'] = 'East Europe'
# relabel these countries as South America
south_american_countries = [
'Uruguay', 'Argentina', 'Chile', 'Peru', 'Ecuador', 'Paraguay', 'Brazil', 'Bolivia'
]
merged_df.loc[merged_df['country_name'].isin(south_american_countries), 'World regions according to WB'] = 'South America'
# Add the Europe label to these countries
europe = ['Czech Republic', 'Réunion']
merged_df.loc[merged_df['country_name'].isin(europe), 'World regions according to WB'] = 'Europe'
# relabel Mexico as North America
merged_df.loc[merged_df['country_name'] == 'Mexico', 'World regions according to WB'] = 'North America'
# simplify column names for regions
merged_df['World regions according to WB'] = merged_df['World regions according to WB'].replace({
'Europe and Central Asia (WB)': 'Europe',
'Latin America and Caribbean (WB)': 'Central America and Caribbean'
})
merged_df['World regions according to WB'] = merged_df['World regions according to WB'].str.replace('(WB)', '')
merged_df['World regions according to WB'] = merged_df['World regions according to WB'].str.strip()
merged_df.head(100)
I created a nested SQL query that returns the weekly rank by country for the weekly top US TV show over a 17-month period.
-- Create a CTE that queries the number one US weekly show
WITH A AS (SELECT week, show_title
FROM all_weeks_countries
WHERE weekly_rank = 1
AND country_name = 'United States'
AND category = 'TV')
--Use this CTE to get all country rankings for the number one US show that week
SELECT week, show_title, country_name, weekly_rank
FROM all_weeks_countries
WHERE (week, show_title) IN
(SELECT week, show_title
FROM A)I convert this nested query into a pandas DataFrame, which is then merged with a DataFrame containing regional information for each country. The country_names column is dropped, and the data is pivoted to show the relative rankings of the top U.S. TV show by world regions.
Regional Rankings of the U.S. Weekly #1 Television Show
This table presents a weekly comparison of regional performance for the show that ranked first in the United States. Viewers can use this data to see how the top-ranked show's popularity varies across different regions of the world.
# merge region data to the dataset showing the country rankings for the weekly number one US show. Drop the country column
region_rank = pd.merge(df1, merged_df, how='inner', on='country_name', left_index=False, right_index=False)
region_rank.drop('country_name', axis=1, inplace=True)
region_rank.head()
#Pivot the data to show average rankings across world regions for weekly US number one show
grouped_region = region_rank.groupby(['World regions according to WB', 'week','show_title'])['weekly_rank'].mean().reset_index()
# Rename columns for clarity
grouped_region.rename(columns={'World regions according to WB': 'region', 'weekly_rank': 'avg_weekly_rank'}, inplace=True)
# Now we can pivot the DataFrame
pivoted_df = grouped_region.pivot_table(index=['week', 'show_title'], columns='region', values='avg_weekly_rank')
# Reset the index to make 'week' and 'show_title' regular columns again
pivoted_df = pivoted_df.reset_index()
# add a column showing that the ranking is 1 for the US
pivoted_df['United_States'] = 1
pivoted_df.iloc[:, 2:12] = pivoted_df.iloc[:, 2:12].round(1)
#strip white space from columns
cols = pivoted_df.columns
for col in cols:
pivoted_df[col] = pivoted_df[col].astype(str)
pivoted_df[col] = pivoted_df[col].str.strip()
pivoted_df[col] = pivoted_df[col].str.lstrip()
pivoted_df[col] = pivoted_df[col].str.rstrip()
columns_to_convert = pivoted_df.columns[2:12] # Slice up to 13 to include index 12
# Convert the selected columns to float
pivoted_df[columns_to_convert] = pivoted_df[columns_to_convert].astype(float)
# rename and reorder the columns
pivoted_df = pivoted_df.rename(columns={'Central America and Caribbean': 'C.America', 'East Asia and Pacific': 'E.Asia_Pacific', 'East Europe': 'E.Europe', 'Middle East, North Africa, Afghanistan and Pakistan': 'Middle East', 'North America': 'N.America', 'South America': 'S.America', 'South Asia': 'S.Asia',})
new_order = ['week', 'show_title', 'United_States', 'N.America', 'C.America', 'S.America', 'Europe', 'E.Europe', 'Middle East', 'Sub-Saharan Africa', 'S.Asia', 'E.Asia_Pacific']
pivoted_df = pivoted_df[new_order]
pivoted_df['show_title'] = pivoted_df['show_title'].str.strip()
pivoted_dfAverage Global Ranking of Top US Shows (2021–2022)
An analysis of weekly rankings for shows that achieved the number one spot in the United States between 2021 and 2022 reveals significant regional variations in average performance. This corresponding pandas table shows that, on average, these US top-ranked shows achieved the most favorable rankings in Europe and the least favorable rankings in Central and South America.
AVG_RATINGS = pivoted_df.iloc[:, 2:12] # Select region columns as a DataFrame
# creates columns with average ratings across world regions and spanning 17 months for shows that were ranked number one in the US every week
column_means_series = AVG_RATINGS.mean(axis=0)
average_ratings_country = column_means_series.to_frame().T
average_ratings_countryRegional popularity lags for U.S. weekly #1 shows Virgin River and Ozark.
The U.S. weekly #1 shows Virgin River and Ozark ranked among the most poorly performing titles across global regions, as detailed in the table below..