Analyze Global Dev Trends with DuckDB

Query GitHub's Innovation Graph data with SQL to map the global AI boom.

Introduction

30 Second Summary

Every year, millions of new developers start writing code around the world. Some countries are growing their tech workforces dramatically faster than others, and AI is reshaping what those developers build in real time.

In this project, you will query GitHub's published dataset covering 215+ countries of developer activity using Python and DuckDB. You will create data visualizations of global trends and use Gemini to auto-generate a professional executive summary from your findings.

What You'll Build

When you run your finished script, it produces three publication-quality charts and an AI-written executive summary that reads like a professional analyst report.

By the end of this project, you'll have:

  • SQL-powered analysis through DuckDB that ranks countries by developer growth rate, compares programming language popularity, and surfaces AI adoption trends from raw data files.
  • Three matplotlib visualizations saved as shareable PNG files. A bar chart of the fastest-growing countries, a line chart tracking language popularity over time, and an area chart of the global AI development boom.
  • An AI-generated executive summary produced by Gemini that references your specific data points and ties them into a polished, stakeholder-ready narrative.
  • An optional Secret Mission to push your skills further.

Are there any prerequisites?

This project assumes basic Python, SQL, and pandas knowledge. You should also be familiar with pip for installing packages.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Your Analysis Environment

Your analysis pipeline depends on four Python packages working together. DuckDB lets you run SQL queries directly on CSV files without setting up a database server. pandas handles data manipulation. matplotlib creates chart visualizations. The google-genai SDK connects to Gemini for AI-generated summaries.

In this step, you'll install all four packages with a single pip command and download the real-world dataset you'll query throughout the project.

In this step, get ready to:
  • Install DuckDB, pandas, matplotlib, and google-genai with pip.
  • Download the GitHub Innovation Graph dataset.
  • Verify the packages and data files are ready.
Install the Python packages

You need a terminal inside VS Code to run all the setup commands.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.
  • Open the integrated terminal by pressing Ctrl+` (backtick key, same shortcut on macOS and Windows).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir dev-trends-analysis
cd dev-trends-analysis
  • Confirm the folder was created by running:
ls ~/Desktop

You should see dev-trends-analysis listed in the output.

  • Install all four packages in one command by running:
pip install duckdb pandas matplotlib google-genai

You'll see download progress bars for each package. The install finishes with a line starting with Successfully installed.

What are these packages?

  • DuckDB is an in-process SQL database engine. It can query CSV files directly with SQL, no server setup required.
  • pandas provides DataFrames for reshaping, filtering, and transforming tabular data in Python.
  • matplotlib creates publication-quality charts and visualizations.
  • google-genai is Google's official SDK for the Gemini API. It replaced the older google-generativeai package.

Seeing a pip error?

  • If your terminal says pip: command not found, try pip3 instead. Some systems map the command to pip3 by default.
  • If you see a permissions error, try adding --user to the end of the command.

Still stuck? Help me fix my pip install error

Download the GitHub Innovation Graph dataset

GitHub publishes quarterly data on developer activity across 215+ countries in a public repository called the Innovation Graph. The dataset includes CSV files covering developer counts, programming languages, topics, and cross-country collaboration.

  • Clone the Innovation Graph repository by running:
git clone https://github.com/github/innovationgraph.git

This downloads the full repository into a folder called innovationgraph inside your dev-trends-analysis folder. The CSV data files live in the innovationgraph/data subfolder.

  • Confirm the data files are in place by running:
ls innovationgraph/data

You should see CSV files listed including developers.csv, languages.csv, topics.csv, economy_collaborators.csv, and more.

What's in this dataset?

The Innovation Graph contains 8 CSV files of GitHub activity metrics, aggregated by economy and reported quarterly from Q1 2020 onward. Each file shares common columns: iso2_code (a two-letter country code like US or IN), year, and quarter.

Metrics are only reported for an economy when 100+ unique developers performed the relevant activity in that period. This means smaller economies may not appear in every quarter.

git command not found?

If git is not installed, you can download the repository as a ZIP file instead.

  • Go to https://github.com/github/innovationgraph in your browser.
  • Click the green Code button, then click Download ZIP.
  • Unzip the downloaded file into your dev-trends-analysis folder. Make sure the CSV files are at the path innovationgraph/data/.

Need more help? Help me download the GitHub Innovation Graph dataset without git

Verify the setup

Before moving on, you'll confirm that all four packages import correctly and the data files are accessible.

What do you think will happen when you try to import all four packages? Will all of them load successfully, or will one fail?

  • Test that all packages are installed by running:
python -c "import duckdb; import pandas; import matplotlib; from google import genai; print('All set!')"

You should see All set! printed in your terminal.

Seeing an ImportError?

  • If the error mentions a specific package, re-run pip install for just that package (e.g. pip install duckdb).
  • If you used pip3 to install, use python3 to run the verify command instead of python. The pip and python commands need to point to the same Python installation.
  • If you see an error about google-generativeai vs google-genai, make sure you installed the newer google-genai package.

Still not working? Help me fix my Python import error

Your environment is ready with all the tools and data you need. Next up, you'll start querying the dataset to see what's inside those CSV files.

Load and Explore the Dataset with DuckDB

Step 2: Load and Explore the Dataset with DuckDB

Your analysis environment is ready with DuckDB, pandas, matplotlib, and the Gemini SDK installed. Now it's time to dig into the data.

You have six CSV files in front of you, each packed with GitHub activity data. But raw files full of cryptic ISO country codes and quarterly aggregations don't tell a clear story on their own. Before you can analyze trends, you need to see what the data actually looks like.

In this step, get ready to:
  • Query each CSV file with DuckDB and print row counts and date range.
  • Create a country name mapping dictionary for readable output.
  • Find the top 10 countries by developer count in the latest quarter.
Query each CSV file for row counts

DuckDB can query CSV files directly with SQL. You don't need to import data into a database first. Just point a SELECT statement at a file path and DuckDB reads it on the fly.

  • In VS Code, create a new file called analyze.py in the same folder where the innovationgraph directory is located.
  • Paste the following code into analyze.py:
import duckdb
import pandas as pd
import matplotlib.pyplot as plt
import os

# --- Configuration ---
DATA_DIR = "innovationgraph/data"

# --- Step 2: Load and Explore ---
# Open an in-memory DuckDB connection
con = duckdb.connect()

# List the CSV files to explore
files = ["developers.csv", "repositories.csv", "git_pushes.csv",
         "languages.csv", "topics.csv", "economy_collaborators.csv"]

# Query each CSV directly and print its row count
print("=== Dataset Overview ===")
for f in files:
    path = os.path.join(DATA_DIR, f)
    result = con.execute(f"SELECT COUNT(*) as rows FROM '{path}'").fetchone()
    print(f"  {f}: {result[0]:,} rows")

What does this code do?

The imports bring in the four libraries you installed. You'll use all of them by the end of the project, but this step only uses duckdb and os.

  • duckdb.connect() creates an in-memory database connection. No files are created on disk.
  • The for loop runs SELECT COUNT(*) directly against each CSV file path. DuckDB auto-detects the headers and column types.
  • fetchone() returns the single result row, and {result[0]:,} formats the number with commas for readability.
  • Save analyze.py.
  • Run the script by entering this command in your terminal:
python analyze.py

You should see a row count printed for each of the six CSV files, formatted with commas.

Seeing a file not found error?

  • Make sure analyze.py is saved in the same folder that contains the innovationgraph directory. The path innovationgraph/data is relative, so the script needs to be one level above it.
  • Confirm the CSV files exist by running ls innovationgraph/data/ in your terminal. You should see six .csv files listed.
  • My script can't find the CSV files.

Row counts tell you the volume of data, but you also need to know the time period it covers.

  • In analyze.py, add this code below the for loop to query the date range of the dataset:
# Date range
date_range = con.execute(f"""
    SELECT MIN(year || '-Q' || quarter) as earliest,
           MAX(year || '-Q' || quarter) as latest
    FROM '{DATA_DIR}/developers.csv'
""").fetchone()
print(f"  Date range: {date_range[0]} to {date_range[1]}")

What does this query do?

The SQL concatenates the year and quarter columns into a readable format like 2020-Q1. Then MIN and MAX find the earliest and latest data points across the entire dataset.

  • Save analyze.py and run it again:
python analyze.py

You should now see the same row counts as before, plus a new line showing the date range from Q1 2020 to Q1 2026.

Create a country name mapping

The dataset identifies countries using ISO 2-letter codes like US, IN, and BR. A table full of two-letter codes is hard to read at a glance. A Python dictionary can map each code to a full country name.

  • In analyze.py, find the line that says DATA_DIR = "innovationgraph/data".
  • Add the following dictionary below that line, above the # --- Step 2: Load and Explore --- comment:
# Map ISO 2-letter country codes to readable names
COUNTRY_NAMES = {
    "US": "United States", "CN": "China", "IN": "India", "BR": "Brazil",
    "GB": "United Kingdom", "DE": "Germany", "FR": "France", "JP": "Japan",
    "CA": "Canada", "RU": "Russia", "KR": "South Korea", "AU": "Australia",
    "ID": "Indonesia", "NG": "Nigeria", "MX": "Mexico", "ES": "Spain",
    "IT": "Italy", "NL": "Netherlands", "PL": "Poland", "TR": "Turkey",
    "SE": "Sweden", "PK": "Pakistan", "BD": "Bangladesh", "PH": "Philippines",
    "AR": "Argentina", "CO": "Colombia", "UA": "Ukraine", "EG": "Egypt",
    "KE": "Kenya", "ZA": "South Africa", "IL": "Israel", "SG": "Singapore",
    "TW": "Taiwan", "VN": "Vietnam", "TH": "Thailand", "MY": "Malaysia",
    "CL": "Chile", "RO": "Romania", "CZ": "Czechia", "HK": "Hong Kong",
}

Why not map every country?

The dataset covers 215+ economies, but this dictionary covers the 40 most prominent ones. Any country code not in the dictionary falls back to showing the raw ISO code, thanks to the .fillna() call you'll add in the next substep.

  • Save analyze.py. You'll use this mapping in the next substep to make the output human-readable.
Find the top 10 countries by developer count

Now use SQL to find which countries have the most developers in the latest quarter, then map those cryptic ISO codes to the readable names you just added.

  • In analyze.py, scroll to the bottom of the file and add this code below the date range print statement:
# Top 10 countries by developers (latest quarter)
top_countries = con.execute(f"""
    SELECT iso2_code, developers
    FROM '{DATA_DIR}/developers.csv'
    WHERE (year, quarter) = (
        SELECT year, quarter FROM '{DATA_DIR}/developers.csv'
        ORDER BY year DESC, quarter DESC LIMIT 1
    )
    ORDER BY developers DESC
    LIMIT 10
""").df()

# Map country codes to readable names and print
top_countries["country"] = top_countries["iso2_code"].map(COUNTRY_NAMES).fillna(top_countries["iso2_code"])
print("\n=== Top 10 Countries by Developers (Latest Quarter) ===")
print(top_countries[["country", "developers"]].to_string(index=False))

What does this query do?

The subquery SELECT year, quarter ... ORDER BY year DESC, quarter DESC LIMIT 1 finds the most recent quarter in the dataset. The outer query then filters to only that quarter and ranks countries by developer count.

The .df() method converts the DuckDB result into a pandas DataFrame. Then .map(COUNTRY_NAMES) replaces each ISO code with its full name from the dictionary. The .fillna() keeps the raw code for any country not in the mapping.

Before you run the script, which countries do you think will rank in the top 10 for most developers on GitHub?

  • Save analyze.py and run the full script:
python analyze.py

You should now see three sections of output: the dataset overview with row counts, the date range (Q1 2020 to Q1 2026), and a top 10 countries table showing country names and developer counts.

Seeing a NameError for COUNTRY_NAMES?

  • Make sure the COUNTRY_NAMES dictionary is placed above the # --- Step 2: Load and Explore --- comment, not below it. Python reads top-to-bottom, so the dictionary must be defined before the code that uses it.
  • Check that the dictionary ends with a closing } on its own line. A missing brace causes a syntax error.
  • Help me fix this NameError.

✔️ Awesome, I've got everything!

Great. Double check you've saved analyze.py and your terminal shows all three output sections.

ⓧ I'd like to double check the full code

Your analyze.py should contain these sections in order. Scroll through your file and confirm each one is present:

  • Imports (duckdb, pandas, matplotlib, os) at the top.
  • DATA_DIR = "innovationgraph/data" configuration.
  • The COUNTRY_NAMES dictionary with 40 country mappings.
  • duckdb.connect() and the files list.
  • The for loop printing row counts.
  • The date_range query and print statement.
  • The top_countries query, .map(COUNTRY_NAMES) call, and final print.

You've mapped the shape of the dataset: six CSV files spanning Q1 2020 to Q1 2026, with country-level metrics. Next up, you'll dig deeper into how developer populations and programming language preferences shifted over that period.

Analyze Developer Growth and Language Trends

Your script can read the dataset and show which countries have the most developers right now. That answers "who's big today" but misses a much more interesting question: who's growing fastest?

A country with 5,000 developers that triples in five years tells a more compelling story than a country with 500,000 that barely budges. In this step, you'll write SQL queries that compute growth rates across countries and rank programming languages over time.

In this step, get ready to:
  • Calculate developer growth rates from Q1 2020 to the latest quarter for every country.
  • Rank programming languages by global developer count and track how the top 5 shift over time.
  • Store all results as pandas DataFrames ready for visualization.
Calculate developer growth rates by country

To find the fastest-growing developer communities, you need to compare each country's developer count in Q1 2020 against its count in the latest quarter. A CTE (Common Table Expression) lets you define the "earliest" and "latest" snapshots as named subqueries, then join them to calculate a percentage growth.

  • In analyze.py, scroll to the bottom of the file and find this line:
print(top_countries[["country", "developers"]].to_string(index=False))
  • Add the following code below that line to compute growth rates for the top 15 fastest-growing countries:
# --- Step 3: Growth and Language Trends ---
print("\n=== Developer Growth Rates (Q1 2020 to Latest) ===")
growth_df = con.execute(f"""
    WITH earliest AS (
        SELECT iso2_code, developers as dev_start
        FROM '{DATA_DIR}/developers.csv'
        WHERE year = 2020 AND quarter = 1
    ),
    latest AS (
        SELECT iso2_code, developers as dev_end, year, quarter
        FROM '{DATA_DIR}/developers.csv'
        WHERE (year, quarter) = (
            SELECT year, quarter FROM '{DATA_DIR}/developers.csv'
            ORDER BY year DESC, quarter DESC LIMIT 1
        )
    )
    SELECT l.iso2_code,
           e.dev_start,
           l.dev_end,
           ROUND(((l.dev_end - e.dev_start) * 100.0 / e.dev_start), 1) as growth_pct
    FROM latest l
    JOIN earliest e ON l.iso2_code = e.iso2_code
    WHERE e.dev_start >= 1000
    ORDER BY growth_pct DESC
    LIMIT 15
""").df()

growth_df["country"] = growth_df["iso2_code"].map(COUNTRY_NAMES).fillna(growth_df["iso2_code"])
print(growth_df[["country", "dev_start", "dev_end", "growth_pct"]].to_string(index=False))

What does this SQL query do?

  • The WITH earliest AS CTE grabs each country's developer count from Q1 2020 as the baseline.
  • The latest CTE grabs each country's count from the most recent quarter in the dataset.
  • The main query joins them on iso2_code and calculates percentage growth. The WHERE e.dev_start >= 1000 filter keeps out tiny populations that would show misleading growth percentages.
  • The .df() call converts the result straight into a pandas DataFrame for later plotting.
  • Save analyze.py.
  • Run the script by running this command in your terminal:
python analyze.py

Below the dataset overview from earlier, you should now see a table titled === Developer Growth Rates (Q1 2020 to Latest) === with 15 countries ranked by growth_pct.

Seeing a SQL error?

  • Check that the triple-quoted f-string f"""...""" has matching triple quotes at both ends. A missing quote will cause a syntax error.
  • Confirm your DATA_DIR path points to the folder that contains developers.csv. DuckDB throws a file-not-found error if the path is wrong.
  • help me debug a DuckDB SQL error in my growth rate query
Rank programming languages globally

Growth rates tell you where developers are appearing. The next question is: what are they writing? The languages.csv file tracks how many developers pushed code in each language per country per quarter.

  • Add the following code to the bottom of analyze.py to rank the top 10 programming languages by global developer count:
# Language trends
print("\n=== Top 10 Programming Languages (Latest Quarter) ===")
lang_latest = con.execute(f"""
    SELECT language, SUM(num_pushers) as total_pushers
    FROM '{DATA_DIR}/languages.csv'
    WHERE (year, quarter) = (
        SELECT year, quarter FROM '{DATA_DIR}/languages.csv'
        ORDER BY year DESC, quarter DESC LIMIT 1
    )
    AND language_type = 'programming'
    GROUP BY language
    ORDER BY total_pushers DESC
    LIMIT 10
""").df()
print(lang_latest.to_string(index=False))

What does this query do?

  • The SUM(num_pushers) aggregates developers across all countries to get a global count per language.
  • The language_type = 'programming' filter excludes markup and data languages like HTML, CSS, and JSON so the ranking focuses on actual programming languages.
  • Save analyze.py and run the script again:
python analyze.py

Scroll to the bottom of the output. You should see a table titled === Top 10 Programming Languages (Latest Quarter) === with languages ranked by total_pushers.

Not seeing the language table?

  • Make sure the new code is placed after the growth rate section, not inside it. Indentation matters.
  • Check that the file path includes languages.csv. A typo in the filename will cause a file-not-found error.
  • help me debug my DuckDB language ranking query
Prepare language trend data for visualization

A snapshot ranking tells you what's popular now. To see how languages are shifting over time, you need quarterly data for the top 5 languages across all available quarters.

  • Add the following code to the bottom of analyze.py to query quarterly trend data for the top 5 languages:
# Language trends over time (top 5)
top_5_langs = lang_latest["language"].head(5).tolist()
lang_trends = con.execute(f"""
    SELECT language, year, quarter, SUM(num_pushers) as total_pushers
    FROM '{DATA_DIR}/languages.csv'
    WHERE language IN ({','.join(f"'{l}'" for l in top_5_langs)})
    AND language_type = 'programming'
    GROUP BY language, year, quarter
    ORDER BY year, quarter
""").df()

lang_trends["period"] = lang_trends["year"].astype(str) + "-Q" + lang_trends["quarter"].astype(str)

What does this code do?

  • top_5_langs grabs the top 5 language names from the ranking you just computed. This avoids hardcoding language names that might change as the dataset updates.
  • The SQL query fetches quarterly developer counts for only those 5 languages across every quarter in the dataset.
  • The period column (e.g. 2020-Q1) creates a human-readable time label that matplotlib can use for chart axes in the next step.

Why are the results already DataFrames?

Every query in this step ends with .df(). That's a DuckDB shortcut that converts the SQL result directly into a pandas DataFrame.

You now have three DataFrames ready for visualization: growth_df (country growth rates), lang_latest (current language rankings), and lang_trends (quarterly language data over time).

Before you run the script, do you think this new code will produce any new printed output? Why or why not?

  • Save analyze.py and run the script one more time to confirm everything works together:
python analyze.py

The output looks the same as before. This chunk prepared data silently. The three printed sections (dataset overview, growth rates, language rankings) should all appear without errors. The lang_trends DataFrame is stored in memory, ready for the charts you'll build next.

Seeing a NameError or KeyError?

  • If you see NameError: name 'lang_latest' is not defined, make sure the language ranking code block (from the previous substep) appears before this trend code in your file.
  • If you see a KeyError about the language column, double-check that the column name matches exactly. Column names in languages.csv are lowercase.
  • help me debug a NameError or KeyError in my language trends code

✔️ Awesome, I've got everything!

Great. Double check you've saved analyze.py and that the script runs without errors.

ⓧ I'd like to double check the full code

Your analyze.py is now over 30 lines, so scroll through and confirm these sections appear in order:

  • Imports (duckdb, pandas, matplotlib, os) and the DATA_DIR and COUNTRY_NAMES configuration at the top.
  • The Step 2 section: DuckDB connection, dataset overview loop, date range, and top-10 countries query.
  • The # --- Step 3: Growth and Language Trends --- comment, followed by the growth rate CTE query that produces growth_df.
  • The language ranking query that produces lang_latest.
  • The language trends query that produces lang_trends with the period column at the very bottom.

You now have the numbers that tell the story: which countries are growing fastest and which languages dominate globally. Tables of numbers are hard to scan, though. In the next step, you'll turn these DataFrames into visualizations that make the growth story jump off the screen.

Visualize the Trends

Your growth tables and language rankings from the last step are packed with insight. But scanning rows of numbers to spot patterns is slow and error-prone.

In this step, you'll turn those DataFrames into matplotlib charts that make the growth story jump off the screen. A horizontal bar chart will rank countries by developer growth at a glance, and a multi-line chart will show how the top programming languages shifted quarter by quarter.

In this step, get ready to:
  • Create a horizontal bar chart of the top 15 countries by developer growth rate.
  • Create a multi-line chart of the top 5 programming languages over time.
  • Save both charts as PNG files and review them.
Create the developer growth bar chart

The growth_df DataFrame you built in the last step already has every country ranked by growth percentage. A horizontal bar chart is the perfect fit here because it lets you compare 15 country names side by side without cramped labels.

  • In analyze.py, scroll to the very bottom of the file (after the lang_trends["period"] line).
  • Add the following code to create the bar chart and save it as a PNG:
# --- Step 4: Visualize ---
fig, ax = plt.subplots(figsize=(10, 8))
bars = ax.barh(growth_df["country"][::-1], growth_df["growth_pct"][::-1], color="#2ea44f")
ax.set_xlabel("Developer Growth (%)")
ax.set_title("Top 15 Countries by Developer Growth (Q1 2020 to Latest)")
ax.bar_label(bars, fmt="%.0f%%", padding=3)
plt.tight_layout()
plt.savefig("developer_growth.png", dpi=150)
print("\nSaved developer_growth.png")

What does this code do?

  • plt.subplots(figsize=(10, 8)) creates a figure and axes object sized to fit 15 horizontal bars comfortably.
  • ax.barh() draws horizontal bars. The [::-1] reversal puts the highest growth country at the top of the chart instead of the bottom.
  • ax.bar_label() prints the percentage directly on each bar so readers can see exact values without checking the axis.
  • plt.savefig("developer_growth.png", dpi=150) saves the chart as a high-quality PNG file in your project folder.
  • Save analyze.py.
  • Run the script by entering this command in your terminal:
python analyze.py

At the very end of the output (after the tables from previous steps), you should see Saved developer_growth.png printed.

  • Confirm the file was created by running:
ls developer_growth.png

You should see developer_growth.png listed.

Don't see developer_growth.png?

  • Make sure you saved analyze.py before running it.
  • Check that the new visualization code is placed after the lang_trends["period"] line, not inside any existing block.
  • If you see an error about growth_df not being defined, the Step 3 code may be missing. Scroll up and confirm the growth query block is present.

Help me debug why my matplotlib chart isn't saving.

Chart the language trends over time

The bar chart ranks countries at a single point in time. A line chart will show how programming language popularity changed quarter by quarter, revealing whether today's top languages were always dominant.

  • In analyze.py, add the following code directly below the print("\nSaved developer_growth.png") line:
fig, ax = plt.subplots(figsize=(12, 6))
for lang in top_5_langs:
    lang_data = lang_trends[lang_trends["language"] == lang]
    ax.plot(lang_data["period"], lang_data["total_pushers"], marker="o", label=lang, linewidth=2)

ax.set_xlabel("Quarter")
ax.set_ylabel("Global Developers (pushers)")
ax.set_title("Top 5 Programming Languages - Quarterly Trend")
ax.legend()
ax.tick_params(axis="x", rotation=45)
plt.tight_layout()
plt.savefig("language_trends.png", dpi=150)
print("Saved language_trends.png")

What does this code do?

  • The for lang in top_5_langs loop plots one line per language. Each line gets its own color, marker dots, and label so you can compare them at a glance.
  • ax.tick_params(axis="x", rotation=45) rotates the quarterly labels (like 2023-Q2) so they don't overlap on the x-axis.
  • ax.legend() adds a legend mapping each color to a language name so the chart is self-explanatory.
  • Save analyze.py.

Before you run the script again, what do you expect to see printed at the end of the output this time?

  • Run the script again:
python analyze.py

At the bottom of the output, you should now see both Saved developer_growth.png and Saved language_trends.png printed.

Only seeing one chart saved?

  • Make sure the language trends code starts on a new line after print("\nSaved developer_growth.png"). If it's indented inside the bar chart block, only the first chart runs.
  • Check that top_5_langs and lang_trends are defined earlier in your script. If either is missing, the line chart loop has nothing to plot.

Help me figure out why only one chart is saving.

Open and review your charts

Both charts are now saved as high-resolution PNGs in your project folder. Time to see the story your data is telling.

  • Open developer_growth.png by double-clicking it in the VS Code Explorer sidebar, or by opening it in Finder (macOS) or File Explorer (Windows).
  • You should see a horizontal bar chart with 15 countries, each bar colored green with a percentage label on the right.
  • Open language_trends.png. You should see five colored lines tracking each language's global developer count across every quarter.

What do the charts reveal?

The growth chart shows that some countries grew dramatically faster than the global average. The language chart shows whether the dominant languages held steady or if newer languages gained ground over the past few years.

Notice what's missing from both charts: they show who is coding and what language they're using, but not what they're actually building. That's the layer you'll add next.

✔️ Awesome, I've got everything!

Great. Double check you've saved analyze.py and that both PNG files are in your project folder.

ⓧ I'd like to double check the full code

The full file is long, so scroll through analyze.py and confirm these sections are present, in order:

  • Imports (duckdb, pandas, matplotlib.pyplot, os) and DATA_DIR / COUNTRY_NAMES configuration.
  • Step 2 block: dataset overview, date range, and top 10 countries query.
  • Step 3 block: growth rate query (growth_df), language ranking (lang_latest), and language trends query (lang_trends).
  • Step 4 block: the horizontal bar chart saved to developer_growth.png, followed by the multi-line chart saved to language_trends.png.

You now have two publication-quality charts showing where developers are growing and which languages they're using. But the most interesting question is still unanswered: what are all these developers actually building? Next up, you'll dig into the topics data to uncover the AI development boom.

Uncover the AI Development Boom

Your growth charts revealed which countries and languages are surging. But they only show where developers are and what tools they use. The bigger question is: what are all these developers actually building?

The topics.csv file tracks exactly that. By filtering for AI-related topics, you can chart the global AI development boom quarter by quarter and identify which countries are leading the wave.

In this step, get ready to:
  • Query topics.csv for AI-related development activity globally.
  • Create an area chart showing the AI adoption explosion over time.
  • Rank the top 10 countries leading in AI development.
Query topics.csv for AI-related topics

GitHub's topics.csv records which topics developers tag their repositories with. Topics like machine-learning, deep-learning, and pytorch signal real AI development activity, not just interest.

  • In analyze.py, find the line that says print("Saved language_trends.png") at the bottom of your file.
  • Add the following code below it to define your AI topics and query topics.csv:
# --- Step 5: AI Boom Analysis ---
print("\n=== AI Development Boom ===")
ai_topics = [
    "machine-learning", "deep-learning", "artificial-intelligence",
    "neural-network", "nlp", "computer-vision", "tensorflow",
    "pytorch", "transformers", "llm"
]

ai_global = con.execute(f"""
    SELECT year, quarter, SUM(num_pushers) as ai_developers
    FROM '{DATA_DIR}/topics.csv'
    WHERE topic IN ({','.join(f"'{t}'" for t in ai_topics)})
    GROUP BY year, quarter
    ORDER BY year, quarter
""").df()

ai_global["period"] = ai_global["year"].astype(str) + "-Q" + ai_global["quarter"].astype(str)

What does this code do?

ai_topics is a curated list of 10 GitHub topic tags that represent AI and machine learning development. These are the exact strings developers use when tagging their repositories.

The SQL query filters topics.csv for rows matching any of these 10 topics, then groups by year and quarter. The SUM(num_pushers) counts the total number of developers pushing code to AI-related repos each quarter across all countries.

  • Save analyze.py.
  • Run the script by running:
python analyze.py

You should see === AI Development Boom === printed at the end of the output, after your existing charts are saved.

Seeing a KeyError or empty results?

  • Make sure you pasted the code below the existing print("Saved language_trends.png") line, not inside the chart-creation block.
  • Check that the topic strings are in double quotes and separated by commas inside the list.
  • help me debug my AI topics query in DuckDB
Create the AI boom area chart

Now you have quarterly AI developer counts sitting in ai_global. A filled area chart is the best way to show an adoption curve because the shaded region makes the acceleration impossible to miss.

  • Add this chart code directly below the ai_global["period"] line you just added:
# AI boom area chart
fig, ax = plt.subplots(figsize=(12, 6))
ax.fill_between(range(len(ai_global)), ai_global["ai_developers"], alpha=0.3, color="#6f42c1")
ax.plot(range(len(ai_global)), ai_global["ai_developers"], color="#6f42c1", linewidth=2, marker="o")
ax.set_xticks(range(len(ai_global)))
ax.set_xticklabels(ai_global["period"], rotation=45)
ax.set_xlabel("Quarter")
ax.set_ylabel("AI-Related Developers (pushers)")
ax.set_title("The Global AI Development Boom (2020-2026)")
plt.tight_layout()
plt.savefig("ai_boom.png", dpi=150)
print("Saved ai_boom.png")

What does this chart do?

  • fill_between() draws a semi-transparent purple fill from zero up to the data points. This makes the growth curve feel like a rising wave.
  • plot() draws a solid line with markers on top of the fill so you can still read individual quarter values.
  • The x-axis uses integer positions with period labels because fill_between() needs numeric x-values to shade correctly.
  • Save analyze.py and run the script again:
python analyze.py

You should see Saved ai_boom.png printed at the end of the output.

  • Open ai_boom.png from your project folder. You should see a purple area chart with a steep upward curve, especially in the 2023-2026 range.

Chart looks blank or has no fill?

  • Check that you used range(len(ai_global)) as the x-values in both fill_between() and plot(). Using the period strings directly causes the fill to break.
  • Make sure the column name ai_developers matches the alias in your SQL query exactly.
  • help me fix my AI boom area chart in matplotlib
Rank the top countries in AI development

The global trend is clear, but which countries are driving it? One more SQL query reveals the top 10 countries by AI-related developer activity in the latest quarter.

  • Add this code directly below the print("Saved ai_boom.png") line:
# Top 10 countries in AI development
ai_countries = con.execute(f"""
    SELECT iso2_code, SUM(num_pushers) as ai_developers
    FROM '{DATA_DIR}/topics.csv'
    WHERE topic IN ({','.join(f"'{t}'" for t in ai_topics)})
    AND (year, quarter) = (
        SELECT year, quarter FROM '{DATA_DIR}/topics.csv'
        ORDER BY year DESC, quarter DESC LIMIT 1
    )
    GROUP BY iso2_code
    ORDER BY ai_developers DESC
    LIMIT 10
""").df()

ai_countries["country"] = ai_countries["iso2_code"].map(COUNTRY_NAMES).fillna(ai_countries["iso2_code"])
print("\nTop 10 Countries in AI Development (Latest Quarter):")
print(ai_countries[["country", "ai_developers"]].to_string(index=False))

What does this query do?

  • The query reuses the same ai_topics filter but groups by iso2_code instead of by time. This gives you a country-level breakdown of AI development.
  • The subquery (SELECT year, quarter ... LIMIT 1) dynamically picks the most recent quarter so the ranking stays current as GitHub publishes new data.
  • The .map(COUNTRY_NAMES) call translates ISO codes into readable names, just like you did for the growth analysis in the previous step.
  • Save analyze.py.

Before you run the script, which countries do you think will appear in the top 10 for AI development? Do you expect the same countries that lead in overall developer count, or different ones?

  • Run the script to find out:
python analyze.py

At the bottom of your output, you should see a table titled Top 10 Countries in AI Development (Latest Quarter): listing country names alongside their ai_developers counts.

Not seeing the countries table?

  • Make sure the country ranking code comes after the chart-saving code. If it's placed before plt.savefig("ai_boom.png"), the chart code might interfere.
  • Scroll up in your terminal output. The countries table prints after the chart messages, so it may be above your scroll position.
  • help me debug my AI countries ranking query

Time to double check your full file matches what it should at this point.

✔️ Awesome, I've got everything!

Great. Make sure you've saved analyze.py and that your terminal output shows both Saved ai_boom.png and the top 10 countries table.

ⓧ I'd like to double check the full code

Your analyze.py should now have all the sections from previous steps, plus the three new blocks you added in this step. Scroll through your file and confirm these sections appear in order:

  • Imports and COUNTRY_NAMES dictionary at the top.
  • # --- Step 2: Load and Explore --- section with dataset overview and top 10 countries.
  • # --- Step 3: Growth and Language Trends --- section with growth rates and language rankings.
  • # --- Step 4: Visualize --- section saving developer_growth.png and language_trends.png.
  • # --- Step 5: AI Boom Analysis --- section with the ai_topics list, the ai_global query, the area chart saving ai_boom.png, and the ai_countries ranking query.

Your analysis now covers developer growth, programming language shifts, and the AI adoption explosion. You have three charts and two ranking tables telling a complete story. Next up, you'll feed all of these findings to Gemini and let it write a professional executive summary from your data.

Generate an AI-Powered Executive Summary

Your analysis now covers developer growth, programming language shifts, and the AI boom across 215+ countries. You have three publication-quality charts telling a clear visual story.

Charts and tables are powerful, but a data analyst who can also produce a polished, stakeholder-ready narrative from raw findings is worth more than one who just makes charts. In this step, you will feed your key metrics to Gemini and get back a professional executive summary that ties together all your findings.

In this step, get ready to:
  • Compile your key findings into a structured prompt for Gemini.
  • Send the prompt to Gemini and generate an executive summary.
  • Save the summary to a text file for your portfolio.
Compile your key findings into a structured prompt

A vague prompt like "summarize my data" produces vague output. Feeding Gemini the exact numbers you computed gives it the raw material to write a report grounded in real findings.

  • In analyze.py, scroll to the very bottom of the file.
  • Find this line (the last print statement from Step 5):
print(ai_countries[["country", "ai_developers"]].to_string(index=False))
  • Add the following code below that line to import the Gemini SDK, extract your key metrics, and build a structured prompt:
# --- Step 6: Gemini Executive Summary ---
from google import genai

client = genai.Client(api_key="your-api-key-here")

# Build the prompt with real data
ai_start = int(ai_global.iloc[0]["ai_developers"])
ai_end = int(ai_global.iloc[-1]["ai_developers"])
ai_growth = round(((ai_end - ai_start) / ai_start) * 100, 1)

top_grower = growth_df.iloc[0]
top_ai_country = ai_countries.iloc[0]

prompt = f"""You are a senior data analyst. Write a 3-5 paragraph executive summary based on
these findings from analyzing GitHub's Innovation Graph dataset (2020-2026, 215+ countries):

1. DEVELOPER GROWTH: The top country by growth rate is {top_grower['country']} at {top_grower['growth_pct']}%.
   Top 5 fastest growing: {', '.join(growth_df['country'].head(5).tolist())}.

2. PROGRAMMING LANGUAGES: The top 5 languages globally are: {', '.join(top_5_langs)}.

3. AI BOOM: AI-related developer activity grew from {ai_start:,} to {ai_end:,} developers
   ({ai_growth}% growth). The top country for AI development is {top_ai_country['country']}
   with {int(top_ai_country['ai_developers']):,} active AI developers.
   Top 5 AI countries: {', '.join(ai_countries['country'].head(5).tolist())}.

Write a professional executive summary with specific numbers. Focus on actionable insights
about where software development and AI are heading globally."""

What does this code do?

The first few lines import the google-genai SDK and create a client using your API key. This client handles all communication with the Gemini API.

Next, the code extracts concrete numbers from the DataFrames you built in earlier steps. ai_start and ai_end capture the first and last quarter's AI developer counts, and ai_growth calculates the percentage change between them.

The f-string prompt then weaves these numbers into a structured brief with three sections: developer growth, programming languages, and the AI boom. Giving Gemini specific numbers instead of vague instructions produces a summary grounded in your actual findings.

  • Replace your-api-key-here with your actual Gemini API key.

Keep your key out of version control

Pasting the key directly into the script is fine for local experimentation. If you plan to push this project to GitHub, move the key to an environment variable or a .env file and add it to your .gitignore.

Send the prompt to Gemini and save the summary

With the prompt built, you can now call the Gemini API, print the result, and save it to a file for your portfolio.

  • Add this code directly below the prompt you just created in analyze.py:
response = client.models.generate_content(
    model="gemini-2.0-flash",
    contents=prompt
)

summary = response.text
print("\n=== AI-Generated Executive Summary ===")
print(summary)

with open("executive_summary.txt", "w") as f:
    f.write(summary)
print("\nSaved executive_summary.txt")

con.close()

What does this code do?

  • client.models.generate_content() sends your structured prompt to the gemini-2.0-flash model and returns the generated text in response.text.
  • The summary is printed to the terminal and saved to executive_summary.txt so you have a permanent copy.
  • con.close() shuts down the DuckDB connection cleanly now that all queries are done.

Before you run the script, think about what you expect the summary to contain. Will Gemini reference specific growth percentages and country names, or produce something generic?

  • Save analyze.py.
  • Run your script by executing this command in the terminal:
python analyze.py

After all the data tables and chart-save messages from earlier steps, you should see a new section headed === AI-Generated Executive Summary === followed by 3 to 5 paragraphs of professional prose. The summary should reference specific numbers from your analysis: growth percentages, top countries, and AI adoption rates.

You should also see Saved executive_summary.txt printed at the very end. Confirm the file exists by running:

ls executive_summary.txt

You should see executive_summary.txt listed in the output.

Seeing an API error?

  • Double-check that you replaced your-api-key-here with your actual Gemini API key. The key should be inside the quotes, with no extra spaces.
  • Make sure you are using the google-genai package (not the deprecated google-generativeai package). The import should be from google import genai.
  • If you see a quota or rate-limit error, wait a minute and run the script again. The Gemini free tier has per-minute request limits.
  • help me debug my Gemini API call in analyze.py

✔️ Awesome, I've got everything!

Great. Double check you have saved analyze.py.

ⓧ I'd like to double check the full code

The full analyze.py file is longer than a quick glance, so scroll up through your file and confirm each section is present in this order:

  • Imports (duckdb, pandas, matplotlib, os) and COUNTRY_NAMES dictionary at the top.
  • Step 2: Load and Explore (DuckDB connection, row counts, date range, top 10 countries).
  • Step 3: Growth and Language Trends (growth_df query, lang_latest query, lang_trends query).
  • Step 4: Visualize (developer growth bar chart, language trends line chart).
  • Step 5: AI Boom (ai_topics list, ai_global query, AI boom chart, ai_countries query).
  • Step 6: Gemini Executive Summary (from google import genai, client creation, metric extraction, prompt, API call, print, save to executive_summary.txt, con.close()).

Why structured prompts beat open-ended ones

Notice how the prompt is organized into numbered sections with specific data points injected via f-string variables. This structure gives Gemini clear anchors to reference in its output.

A vague prompt like "summarize my GitHub data" would produce a generic response. By feeding in exact growth percentages, country names, and developer counts, you get a summary that reads like a professional analyst wrote it from your specific findings.

Secret mission

Build a Country Comparison Tool

Your analysis covered global trends, but what if you could zoom in on any two countries and compare them head to head? In this secret mission, you'll build a reusable comparison tool that queries both countries' data, generates a 3-panel chart, and uses Gemini to narrate the differences.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing cloud costs or paid services to worry about. The Gemini API free tier covers the usage in this project.

Decide whether to keep your project files for your portfolio, or delete them to free up disk space.

Resources you used:

  • Local Python scripts (analyze.py, compare_countries.py).
  • Generated chart images (developer_growth.png, language_trends.png, ai_boom.png, and any comparison PNGs from the Secret Mission).
  • Generated report (executive_summary.txt).
  • Cloned dataset directory (innovationgraph/).
  • Installed Python packages (duckdb, pandas, matplotlib, google-genai).

Keep everything running

No action needed. Choose this if you plan to extend the analysis, add more countries, or push the project to GitHub as a portfolio piece.

  • Your scripts, charts, and generated summaries are all local files safe on your machine.
  • The installed Python packages (duckdb, pandas, matplotlib, google-genai) remain available for future projects.
  • The Gemini API free tier covers the usage in this project, so there is nothing accumulating charges.

Pause - I'll come back to this later

Keep your project files but reclaim some disk space by removing the large dataset directory.

  • The innovationgraph/ directory is the largest item. You can delete it and re-clone it later when you return.
  • Keep analyze.py and compare_countries.py so you can pick up where you left off.
  • Your chart PNGs and executive_summary.txt are small files worth keeping as references.

Delete - I don't want to use this again

Remove all project files and uninstall the Python packages you added during this project.

  • Navigate to the folder that contains analyze.py in your terminal.

Delete all project files and the dataset directory.

macOS

  • Remove the project files and dataset by running:
rm analyze.py compare_countries.py
rm developer_growth.png language_trends.png ai_boom.png
rm -f comparison_*.png
rm executive_summary.txt
rm -rf innovationgraph/
  • Uninstall the Python packages by running:
pip uninstall duckdb pandas matplotlib google-genai -y

Windows

  • Remove the project files and dataset by running:
del analyze.py, compare_countries.py
del developer_growth.png, language_trends.png, ai_boom.png
del comparison_*.png
del executive_summary.txt
rmdir /s /q innovationgraph
  • Uninstall the Python packages by running:
pip uninstall duckdb pandas matplotlib google-genai -y
  • Confirm the files are gone by running ls (macOS) or dir (Windows). You should no longer see any of the project files.

Nice Work!

Nice Work!

You just built a complete data analysis pipeline that turns raw GitHub activity data into visualizations and an AI-generated executive summary.

You've learned how to:

  • Query real-world CSV datasets with SQL using DuckDB to compute developer growth rates, rank programming languages, and join multiple files covering 215+ countries.
  • Create publication-quality data visualizations with matplotlib showing developer growth by country, language popularity shifts, and the global AI development boom.
  • Integrate Gemini AI to auto-generate a professional executive summary from raw analysis findings, turning numbers into stakeholder-ready narrative.
  • Secret Mission: Took on an optional challenge to push your skills further.

Ready to quiz yourself?