Learning Python and DuckDB with Japan Travel Data, Part 3: Accommodation Data, Scoring, and Validation

After calculating the weather results, I tried a combined score. I thought that subtracting points for months with higher accommodation demand might help identify places with favorable weather outside their busiest season.

Then Kyoto (京都) in August moved from 47th to 19th while its weather match rate stayed at 0%. The calculation was doing what I asked: other destinations had lost more points. But a reader looking at the ranking could easily take that jump as an improvement in the travel conditions. For the article, I decided to rank by the weather match rate and show accommodation demand alongside it.

This part covers the accommodation data, the regional comparisons, and the checks that led to that decision. The starting point is the table from Part 2: 56 destinations × 12 months, or 672 rows.

The project code, saved datasets, and analysis outputs are on GitHub, under bin/analytics/japan-travel-weather/.

The examples use the same working directory as the first two parts. I added openpyxl to read Excel files and pypdf to read PDFs.

Add the libraries

cd bin/analytics/japan-travel-weather
uv add openpyxl pypdf

These are also scripts written for this project. Where I show an excerpt, I explain which variables and earlier steps it relies on.

Download the accommodation statistics

I used the Japan Tourism Agency’s (観光庁) Accommodation Travel Statistics Survey (宿泊旅行統計調査). I downloaded the Excel workbooks for the final 2023–2025 results from the official page and saved them in data/accommodation/.

I wanted to identify the busier months within each prefecture. The measure is guest-nights: one person staying for three nights counts as three guest-nights. It includes business travel and other overnight stays, not just tourism.

I wrote inspect_accommodation.py to look at the workbook structure. This shortened example prints the sheet names and January’s prefecture table for one file.

inspect_accommodation.py

from pathlib import Path
from openpyxl import load_workbook

path = Path("data/accommodation/001750679.xlsx")
wb = load_workbook(path, read_only=True, data_only=True)
try:
    print(wb.sheetnames)
    sheet = wb["第2表(1月)"]
    print("Year and month:", sheet["A7"].value)
    for area, guest_nights in sheet.iter_rows(
        min_row=8, max_row=54, max_col=2, values_only=True
    ):
        print(area, guest_nights)
finally:
    wb.close()

The monthly sheets run from 第2表(1月) to 第2表(12月), meaning Table 2 for January through December. Rows 8–54 contain the 47 prefectures. Column A contains the prefecture code and name, and column B contains total guest-nights. I don’t import the regional subtotals below those rows.

Read the table with openpyxl

I wrote load_accommodation.py to import all the monthly prefecture values using openpyxl. This is a shortened excerpt of the reading code. An outer loop sets path to the source workbook’s Path and month to the month being read.

load_accommodation.py

import re
from openpyxl import load_workbook

wb = load_workbook(path, read_only=True, data_only=True)
sheet = wb[f"第2表({month}月)"]

for area, guest_nights in sheet.iter_rows(
    min_row=8,
    max_row=54,
    max_col=2,
    values_only=True,
):
    area = str(area).strip()
    area_match = re.fullmatch(r"(\d{2})(.+)", area)
    if not area_match:
        raise ValueError(f"Invalid prefecture label: {area}")
    prefecture_code = area_match.group(1)
    prefecture = area_match.group(2)

data_only=True reads the saved value of a formula cell. It doesn’t calculate the formula. The full implementation closes the workbook after reading it and stops on nonnumeric, negative, or noninteger guest-night values. It doesn’t turn blanks or symbols into zero stays.

I also check the year and month in cell A7 instead of trusting the filename alone. A label such as 令和5年1月 means January of Reiwa (令和) year 5. Adding 2018 to the Reiwa (令和) year gives the Gregorian year, which I compare with the expected year and month. I store prefecture codes as strings to preserve the leading zero in codes such as 01.

Run load_accommodation.py

# Run from bin/analytics/japan-travel-weather/
uv run python load_accommodation.py

The output is data/processed/accommodation_monthly_2023_2025.parquet: 47 prefectures × 12 months × 3 years, or 1,692 rows. I check the year/month/prefecture keys and retain the source filename and sheet name on each row. After saving, I read the Parquet file back and compare all rows and columns with the input table.

Adjust for the number of days in a month

A 31-day month has more days to accumulate stays than a 28-day month. And a prefecture having more stays than another prefecture doesn’t tell me whether it’s in its own busy season.

For each year and prefecture, I divide the month’s daily average by the year’s daily average.

Accommodation demand ratio

Demand ratio for a given year and month
  = (monthly guest-nights / days in the month)
    / (annual guest-nights / days in the year)

Monthly summary ratio
  = simple average of that month's ratios for 2023, 2024, and 2025

A ratio of 1.2 means daily accommodation demand is 20% above that prefecture’s annual average. It doesn’t mean the prefecture is 20% more crowded than another one.

In analyze_accommodation_weather.py, I make the input Parquet available as lodging and calculate the ratios for each year with this SQL.

analyze_accommodation_weather.py: ratios by year

WITH totals AS (
    SELECT *,
        day(last_day(make_date(year, month, 1))) AS days_in_month,
        make_date(year + 1, 1, 1) - make_date(year, 1, 1) AS days_in_year,
        sum(guest_nights) OVER (
            PARTITION BY year, prefecture_code
        ) AS annual_guest_nights
    FROM lodging
)
SELECT *,
    (guest_nights * 1.0 / days_in_month)
        / nullif(annual_guest_nights * 1.0 / days_in_year, 0)
        AS demand_ratio
FROM totals

I calculate the number of days from dates rather than maintaining a fixed lookup table. February 2024 gets 29 days, and that year gets 366. A later check stops the analysis if the annual denominator is zero or missing.

For each prefecture and month, I then take the simple average of the three annual demand_ratio values. Adding up all three years’ guest-nights first would give more influence to the years with more stays, so it would be a different calculation. I also save the minimum and maximum ratios to show the variation across years. That range contains the three observed annual values; it isn’t a confidence interval.

There is a useful check on the formula: multiply each monthly ratio by that month’s number of days, add up all 12 months, and divide by the number of days in the year. The result should be 1.

analyze_accommodation_weather.py: check the ratios

SELECT year, prefecture_code
FROM yearly_ratio
GROUP BY year, prefecture_code
HAVING abs(
    sum(demand_ratio * days_in_month) / max(days_in_year) - 1
) > 1e-10

yearly_ratio is the temporary table holding the annual calculations. If this query returns any rows, they fail that relationship. This gives me a check on the calculation as well as the row counts.

Join destinations to prefectures

The weather data is organized by destination; the accommodation data is organized by prefecture. I take the distinct combinations of destination_id, destination name, and prefecture from the daily Parquet file to create a one-row-per-destination mapping. Joining the full 200,000-plus daily rows to monthly data would repeat the same monthly result for each day.

After checking that each destination has one mapping, I join the monthly demand ratios to the 672 weather rows. This excerpt uses views and temporary tables created earlier in the script.

analyze_accommodation_weather.py: join the tables

SELECT w.*, d.destination_name, d.prefecture,
    r.prefecture_code, r.years_count,
    r.demand_ratio_mean, r.demand_ratio_min, r.demand_ratio_max
FROM weather AS w
LEFT JOIN destinations AS d USING (destination_id)
LEFT JOIN monthly_ratio AS r
    ON d.prefecture = r.prefecture AND w.start_month = r.month

LEFT JOIN keeps the weather rows. I then treat a missing accommodation ratio as an error. I also check that every destination/month pair still appears exactly once and that the join hasn’t changed the original weather columns.

Run analyze_accommodation_weather.py

uv run python analyze_accommodation_weather.py

The combined output is data/processed/weather_accommodation_56_regions.csv. I save the 1,692 annual-ratio rows and 564 monthly-ratio rows as separate CSVs too.

This join compares seasonal patterns from different periods: weather from 2016–2025 and accommodation demand from 2023–2025. It doesn’t measure the weather and guest-nights during the same four-day visits. Prefecture-wide accommodation totals also don’t tell me how crowded a particular street will be.

Check local differences in Hokkaido

I wanted to know how well the prefecture-level measure described individual destinations. For Hokkaido (北海道), I added municipal data from the Hokkaido visitor statistics reports (北海道観光入込客数調査報告書).

Those workbooks use fiscal years, with months running from April through the following March. To cover calendar years 2023–2025, I needed four fiscal-year workbooks: 2022–2025. In load_hokkaido_tourism.py, I convert the fiscal year and month into a calendar year. This is the relevant excerpt; fiscal_year comes from the source, and MONTHS follows the order of the workbook’s columns.

load_hokkaido_tourism.py

MONTHS = [4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3]
for j, month in enumerate(MONTHS):
    year = fiscal_year if month >= 4 else fiscal_year + 1
    if not 2023 <= year <= 2025:
        continue
    # Next, read the values at month position j and save them with year and month.

Three calendar years of monthly data for 179 municipalities gives 6,444 rows. I retain the original fiscal year, filename, sheet, row, column, and source-file hash.

I compared total visits, day visits, and guest-nights for Sapporo (札幌), Otaru (小樽), Hakodate (函館), Furano (富良野), Kushiro (釧路), and the Utoro (ウトロ) area of Shiretoko (知床), all of which appear in the nationwide analysis. The first comparison script, compare_hokkaido_seasonality.py, selects total visits and day visits as separate measures. In this SQL, tourism holds the imported municipal data and mapping connects destinations to municipalities.

compare_hokkaido_seasonality.py

SELECT m.destination_id, t.*, 'visitors' AS metric,
    t.visitors_thousands AS monthly_count_thousands
FROM tourism t JOIN mapping m USING (municipality)
UNION ALL
SELECT m.destination_id, t.*, 'day_visitors' AS metric,
    t.day_visitors_thousands AS monthly_count_thousands
FROM tourism t JOIN mapping m USING (municipality)

UNION ALL stacks the two result sets. Total visits already include day visits, so I don’t add the two measures together.

I added guest-nights in compare_hokkaido_guest_nights.py by extending that SQL with this fragment.

compare_hokkaido_guest_nights.py

UNION ALL
SELECT m.destination_id, t.*, 'guest_nights' AS metric,
    t.guest_nights_thousands AS monthly_count_thousands
FROM tourism t JOIN mapping m USING (municipality)

This fragment continues the preceding query; it won’t run by itself. Guest-nights are in thousands of person-nights, while the other two measures are in thousands of visitors. I adjust each measure for the days in the month and year, then compare its ratio to its own annual daily average.

For Shari (斜里) in August, the three-year average guest-night ratio was 1.935, compared with 1.313 for Hokkaido’s (北海道) prefecture-wide accommodation ratio. Even when I focused on overnight stays, the size of the seasonal swing differed. Shari’s (斜里) totals also cover places outside Utoro (ウトロ), and the two surveys use different methods.

Furano (富良野) gave me another reason to be careful. Total visits and day visits peaked in July in all three years, but the guest-night peak changed from year to year. A peak in the three-year average doesn’t mean the same month led every year. I used these local comparisons to explain the limits of the nationwide measure, without adding them to the score.

Check which version of the Kyoto data to use

For Kyoto (京都), I wanted to see whether months with fewer overnight visitors also had fewer day visitors. I used pypdf in load_kyoto_tourism.py to extract monthly tables from the Kyoto Tourism Survey (京都観光総合調査) PDFs. This excerpt gets the text from a page. reader is an open PDF reader, and page_number counts pages starting at 1.

load_kyoto_tourism.py

import re
import unicodedata

text = reader.pages[page_number - 1].extract_text(extraction_mode="layout")
text = unicodedata.normalize("NFKC", text)
compact = re.sub(r"\s+", "", text)

The layout option considers the arrangement of text on the page. I normalize full-width and half-width characters in text before extracting monthly rows. I use the whitespace-free compact string to check table titles, units, and years. The excerpt doesn’t produce a numeric table on its own; the full script continues with row parsing and count checks. I matched the table titles, years, units, and page numbers in the 2025 and 2024 reports.

The report version mattered as much as the extracted numbers. Kyoto City (京都市) says it revised past survey results but did not update the old reports. See the city’s revision notice.

The two reports agreed on the 48 monthly values and four annual totals for 2024 that appeared in both. That didn’t establish that the older report’s 2023 values reflected the revisions. For the regional discussion in the article, I used the 2024–2025 comparison in the 2025 report. I kept the three-year comparison for research.

Some monthly values and breakdowns didn’t sum to the published totals because the tables round counts to thousands. During import, I checked whether the discrepancies fell within the rounding allowance. I didn’t adjust values to force the totals to match. When comparing the same value across report versions, though, I required an exact match rather than applying that allowance.

Checking the arithmetic and checking the scope of a revision answer different questions. Passing the first check still leaves me with a decision about which years I can compare in the article.

Try different weights for a combined score

I tested an accommodation-demand penalty with this formula.

Combined score formula

Penalty = coefficient × max(accommodation demand ratio − 1, 0)
Combined score = weather match percentage − penalty

Months at or below annual-average demand receive no bonus. I subtract points only for the amount above average. In compare_score_weights.py, I apply coefficients of 0, 10, 20, and 30. combined is the table containing the joined weather and accommodation results.

compare_score_weights.py

SELECT c.*, k.coefficient,
    k.coefficient * greatest(demand_ratio_mean - 1, 0) AS demand_penalty,
    matching_trips_pct - demand_penalty AS total_score
FROM combined AS c
CROSS JOIN (VALUES (0), (10), (20), (30)) AS k(coefficient)

CROSS JOIN pairs each row with all four coefficients, producing 672 × 4 results.

Run compare_score_weights.py

uv run python compare_score_weights.py

With a coefficient of 20, rankings changed for 273 of the 672 destination/month combinations. Fifteen combinations with a 0% weather match rate moved up, and 53 scores became negative. Kyoto (京都) in August, from the opening example, was among the destinations that moved up despite a 0% match rate. Its relative rank changed from 47th to 19th; its weather result didn’t change.

Clamping the score at zero would remove negative values but create more ties. Requiring a minimum weather match rate would introduce another cutoff to justify. Neither choice tells me how many percentage points of weather match rate I should subtract for a given increase in accommodation demand.

I ended up ranking by weather match rate and displaying accommodation demand separately. I kept the penalty experiments in the project. The article’s rankings use weather percentages rounded to one decimal place, with equal values sharing ranks in the form 1, 1, 3. I don’t use accommodation demand to break ties, and I retain all 672 rows.

Add holidays as itinerary information

I also added national holidays from the Cabinet Office’s (内閣府) holiday CSV. In build_trip_calendar.py, I read the published dates and count the holidays and weekend days in each four-day window. In this excerpt, start is the departure date and holidays is a dictionary with dates as keys and holiday names as values.

build_trip_calendar.py

from datetime import timedelta

days = [start + timedelta(days=j) for j in range(4)]
holiday_days = [d for d in days if d in holidays]
counts = {
    "holiday_days": len(holiday_days),
    "weekend_days": sum(d.weekday() >= 5 for d in days),
    "weekend_or_holiday_days": sum(
        d.weekday() >= 5 or d in holidays for d in days
    ),
}

weekday() returns 0 for Monday through 6 for Sunday, so a value of 5 or more identifies a weekend. I include these counts as scheduling information, without treating them as measured crowding or subtracting them from a score.

Adding the holiday and weekend counts would double-count a holiday that falls on a Sunday. I save the individual counts and a combined count that removes the overlap. For holidays, I use the dates in the source rather than inferring them from a recurring rule. That also accounts for the moved holidays in 2020 and 2021.

The historical calendar uses the same 3,650 start dates as the weather analysis. For the 2026–2027 planning calendar, I created 727 itineraries, with the last departure on December 28, 2027. In the source available at the time, the 2028 holidays hadn’t been published. I left out itineraries that extended into 2028 rather than treating an unpublished date as an ordinary weekday.

The calendar contains no future weather predictions. It also doesn’t cover all school breaks, local festivals, or the Obon (お盆) holiday period.

Generate the files for the article

I keep the article’s tables and figures in publication/, separate from the analysis outputs.

build_publication.py assigns ranks and prepares the article data. This excerpt shows the ranking calculation. rows contains the 672 records, including weather match rates rounded to one decimal place.

build_publication.py

for r in rows:
    peers = [p for p in rows if p["start_month"] == r["start_month"]]
    r["weather_rank"] = 1 + sum(
        p["matching_trips_pct"] > r["matching_trips_pct"] for p in peers
    )

I count the destinations in the same month with a higher match rate and add 1. Equal rates get the same rank. The script then saves CSVs, monthly tables, figures, and the travel article’s Markdown.

run_publication_review.py runs the calendar builder, the article-data generator, the HTML/PNG renderer, and the validation script in sequence. This excerpt shows the command list and execution loop. BASE is the Path of the analysis directory.

run_publication_review.py

import subprocess
import sys

commands = [
    [sys.executable, "build_trip_calendar.py"],  # Calendar
    [sys.executable, "build_publication.py"],    # Article data
    ["node", "render_publication.mjs"],         # HTML and PNG
    [sys.executable, "verify_publication.py"],   # Validation
]
for command in commands:
    result = subprocess.run(
        command, cwd=BASE, text=True,
        stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
    )
    print(result.stdout, end="")
    if result.returncode:
        break

sys.executable is the Python executable running the current script. subprocess.run() starts another program and waits for it to finish. The full implementation also saves logs, records exit codes, and calculates output-file hashes.

With the saved inputs and the project’s Python and Node environments ready, I can regenerate and check the article files with one command.

Run run_publication_review.py

# From the repository root
cd bin/analytics/japan-travel-weather
uv run python run_publication_review.py

This starts from the saved Parquet and CSV inputs. It doesn’t download the original sources or repeat the entire project from scratch, and it normally runs without network access. To update the holiday source, I explicitly enable downloading on the calendar command.

build_trip_calendar.py: update the holiday source

uv run python build_trip_calendar.py --download

The generator inserts monthly tables into a separate Markdown template for the travel article. Edits made directly to that generated article will be overwritten the next time it runs.

Check the results with a different implementation

Running the same SQL twice could reproduce the same mistake twice. For the article’s validation, I used DuckDB to read the Parquet files, then rebuilt the four-day evaluation and accommodation-ratio calculations with Python lists and comparisons.

For example, verify_publication.py checks the failing weather conditions like this. temperatures and rain contain the daily maximum temperatures and precipitation values for four days with valid observations.

verify_publication.py

fails = (
    min(temperatures) < 10
    or max(temperatures) >= 35
    or sorted(temperatures)[-2] >= 30
    or sorted(rain)[-2] >= 1
    or max(rain) >= 30
)

After sorting from smallest to largest, [-2] selects the second-largest value. If that temperature is at least 30°C, at least two days reached 30°C, which violates the “at most one day” condition. This checks the same rule in a different way from the SQL counts.

I recalculate from the 204,568 daily weather rows and 1,692 monthly accommodation rows, then compare the monthly values, ranks, and values used in article tables. I also check exceptional holiday dates and year-end boundaries. For analysis files that existed before the run, I check that their hashes haven’t changed.

The validation results saved on October 19, 2026, record 264,459 passed checks and matching hashes for 352 existing files. I also saved the execution order, exit codes, and command output in the publication directory.

These checks start with the processed daily and monthly data. I didn’t reimport every original weather CSV and accommodation workbook with a second loader. The source-reading checks and provenance records still matter. Passing validation also doesn’t establish that the weather conditions suit everyone or that the results predict future trips accurately.

The match rates depend on which itineraries I could evaluate. If missing observations are concentrated in particular periods or weather conditions, excluding those itineraries could bias the rates. The rankings also depend on the temperature and rainfall cutoffs I chose; changing those cutoffs could change which itineraries qualify and how destinations rank.

What I got out of working through this with AI

I started by checking the lists and tuples returned from SQL. By the time I was working with the nationwide data, I needed checks for column differences, quality flags, consecutive dates, and joins. Then I had to decide how to explain a score I could calculate but wasn’t comfortable presenting on its own.

As I gave AI more of the implementation and execution work, keeping the source files, intermediate tables, conditions, and validation results let me follow the numbers back through the project. I could also revisit my own choices: the weather thresholds for walking trips, the limits of accommodation demand as a measure of crowds, and the decision to show the measures separately after trying a penalty.

Thanks for reading through the series. The destination recommendations are in the travel article.

This was a personal project built around my own interests and assumptions. If you use the results, you’ll need to decide whether those assumptions fit your plans. For me, it was a fun way to get some practice with unfamiliar tools and learn something from public data along the way. I’d like to try more projects like this and come back to improve this one too.