DEV Community

Japan Open Data Lab
Japan Open Data Lab

Posted on AI-assisted

Analyzing 40 Years of Japanese Public Open Data in 3 Minutes with DuckDB (No API Keys, In-Memory Parquet)

#python #datascience #duckdb #ai

The Hidden Goldmine (and Nightmare) of Japan's Open Data

Japan’s government ministries publish massive amounts of open public data:

  • Multi-billion-dollar national procurement contracts (Digital Agency / GEPS)
  • 44-year historical nationwide land valuation benchmarks (MLIT)
  • Over 5 million corporate registry records (National Tax Agency)
  • 470,000+ registered medical and eldercare facilities (MHLW)

For quantitative hedge funds, real estate investors, and AI researchers, this is institutional-grade economic intelligence.

However, working with raw Japanese government datasets is notoriously painful:

  1. Shift_JIS Encodings: Instant mojibake (garbled characters) in standard Python environments.
  2. Imperial Eras: Dates like Reiwa 5 or Heisei 28 break standard time-series models.
  3. Merged Cells & Non-Standard Layouts: Handcrafted Excel tables that crash pandas readers.
  4. Disparate Identifiers: Cross-ministry entity matching usually requires painful entity resolution.

At Japan Open Data Lab, we mechanically eliminated all of these quirks. We converted 155+ official datasets (10.16M+ records) into US Federal & SEC 24-column snake_case schema, unified under the official 13-digit National Tax Agency Corporate Number and 5-digit JIS Municipal Code.

We published lightweight showcase samples on Hugging Face and built a 3-Minute Google Colab Recipe so you can query them right now in your browser.

Open In Colab

Zero-Download In-Memory Queries with DuckDB

Using DuckDB's HTTP Parquet reader, you can analyze multi-megabyte remote datasets in milliseconds without local storage overhead.

Setup 

pip install duckdb plotly pandas
Enter fullscreen mode Exit fullscreen mode

1. Top Government Contractors (Digital Agency GEPS)

Query all central government procurement awards (2020-2026, ~285,000 records) in 1 line:

import duckdb

con = duckdb.connect()
url = "https://huggingface.co/datasets/japan-opendata/japan-official-data-megabank-showcase/resolve/main/data/japan_public_procurement_sample_5k.parquet"

df = con.execute(f"""
    SELECT 
        legal_name AS vendor_name,
        corporate_number,
        COUNT(*) AS total_contracts,
        ROUND(SUM(capital_amount_jpy) / 1e8, 2) AS total_award_100m_yen
    FROM read_parquet('{url}')
    WHERE legal_name IS NOT NULL
    GROUP BY legal_name, corporate_number
    ORDER BY total_award_100m_yen DESC
    LIMIT 5
""").df()

print(df)
Enter fullscreen mode Exit fullscreen mode

2. Cross-Table JOIN on 13-Digit Corporate Key

Cross-link government tender winners with the National Tax Agency corporate registry using standard SQL JOINs:

proc_url = "https://huggingface.co/datasets/japan-opendata/japan-official-data-megabank-showcase/resolve/main/data/japan_public_procurement_sample_5k.parquet"
corp_url = "https://huggingface.co/datasets/japan-opendata/japan-official-data-megabank-showcase/resolve/main/data/japan_corporate_invoices_sample_5k.parquet"

query = f"""
    SELECT 
        p.legal_name,
        p.corporate_number,
        c.prefecture_en AS hq_prefecture,
        c.city_ja AS hq_city,
        ROUND(SUM(p.capital_amount_jpy) / 1e6, 2) AS total_million_jpy
    FROM read_parquet('{proc_url}') p
    JOIN read_parquet('{corp_url}') c
        ON p.corporate_number = c.corporate_number
    GROUP BY p.legal_name, p.corporate_number, c.prefecture_en, c.city_ja
    ORDER BY total_million_jpy DESC
    LIMIT 5
"""

print(con.execute(query).df())
Enter fullscreen mode Exit fullscreen mode

3. Historical Benchmark Land Valuations (MLIT 44-Year Archive)

Query official land evaluation points across Japan from 1983 to 2026 (~1.1M records):

land_url = "https://huggingface.co/datasets/japan-opendata/japan-official-data-megabank-showcase/resolve/main/data/japan_land_prices_sample_5k.parquet"

land_df = con.execute(f"""
    SELECT 
        area_name_local AS location,
        city,
        current_value AS land_price_yen_per_sqm,
        yoy_change_pct,
        event_date AS valuation_year
    FROM read_parquet('{land_url}')
    WHERE current_value IS NOT NULL
    ORDER BY current_value DESC
    LIMIT 5
""").df()

print(land_df)
Enter fullscreen mode Exit fullscreen mode

Interactive Playground (Google Colab)

Run the full interactive benchmark with Plotly charts in under 3 minutes directly in your browser:

👉 Open In Google Colab

Production API & Full Datasets

Looking for complete production feeds (10.16M+ rows, 44-year depth, and daily sync)?

Top comments (0)