· By Wataru Ito · Coding · 8 min read
Learning Python and DuckDB with Japan Travel Data, Part 2: Four-Day Itineraries in SQL
Building four-day windows from ten years of daily observations, checking missing data, and calculating monthly weather match rates with DuckDB.
A little rain on each day of a four-day trip feels different from rain on just one day. A monthly precipitation total won’t tell me which kind of trip I might have had. To compare destinations in Japan, I built consecutive four-day windows from past daily observations and checked each window against the same conditions.
In Part 1, I downloaded the JMA (気象庁) CSVs and saved daily data for 56 destinations from 2016 through 2025 in Parquet. This part covers checking the input, building the itineraries, and summarizing them by month.
The project code, saved datasets, and analysis outputs are on GitHub, under bin/analytics/japan-travel-weather/.
Set the weather conditions
I had walking around towns and cities in mind. I checked whether each four-day window met all of these conditions.
| Condition | Allowed days in a four-day window |
|---|---|
| Daily maximum temperature at or above 35°C | 0 |
| Daily maximum temperature at or above 30°C | At most 1 |
| Daily maximum temperature below 10°C | 0 |
| Daily precipitation at or above 1mm | At most 1 |
| Daily precipitation at or above 30mm | 0 |
A day at or above 35°C also counts toward the 30°C threshold. Counting those conditions separately lets me rule out the hottest days while allowing one day at or above 30°C.
I chose these cutoffs for this project. They don’t define comfortable weather for everyone. The 30mm threshold isn’t a heavy-rain warning criterion, and precipitation can include snow. I didn’t include humidity, wind, sunshine, or whether a typhoon was approaching.
I called the result matching_trips_pct: the percentage of itineraries that met the conditions. It doesn’t rate everything that might make a destination worth visiting.
Query the Parquet file with SQL
The input is data/processed/jma_56_regions_daily_2016_2025.parquet, the file I created in Part 1 by loading the 56 destinations’ CSVs and assigning date and numeric types. This is a shortened version of the input code from my analysis script, analyze_weather_all56region.py. The SQL examples below also come from that script.
analyze_weather_all56region.py
from pathlib import Path
import duckdb
BASE_DIR = Path(__file__).resolve().parent
INPUT_FILE = (
BASE_DIR / "data" / "processed"
/ "jma_56_regions_daily_2016_2025.parquet"
)
con = duckdb.connect()
con.read_parquet(str(INPUT_FILE)).create_view("weather_daily")This example shows a single input file and assumes the Python script lives in the analysis directory. In the full implementation, I pass a list of input files and close the connection at the end.
Path(__file__).resolve().parent gives me the script’s directory. That keeps the input path independent of whichever directory I happen to be in when I run the command. With create_view("weather_daily"), I can refer to the data as weather_daily in the following SQL.
Make sure four rows mean four days
SQL window functions let me aggregate a range of rows around each row. I use the current row and the next three rows, but that only represents four consecutive days if the dates are complete and ordered.
Before aggregating, I check for duplicate destination_id and date pairs.
analyze_weather_all56region.py: duplicate check
SELECT destination_id, date, count(*) AS copies
FROM weather_daily
GROUP BY destination_id, date
HAVING count(*) > 1Then I check the row count, first date, and last date for each destination. I expect 56 destinations, each with 3,653 rows covering January 1, 2016, through December 31, 2025. NULL keys or empty destination IDs also stop the analysis.
With unique DATE values, the expected first and last dates, and a row count equal to the number of days in that period, I can establish that all dates are present. I retain missing measurements, but I don’t let a missing date row pass this check.
The earlier three-city version had a similarly named Parquet file. Checking the destination count also helped me catch the wrong input. A filename containing all wasn’t enough evidence that I had the nationwide data.
Build itineraries with a window function
I use this window definition for each itinerary.
analyze_weather_all56region.py: window definition
WINDOW trip AS (
PARTITION BY destination_id
ORDER BY date
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING
)PARTITION BY keeps destinations separate, and ORDER BY sorts their dates. ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING selects the current row and the next three rows.
Without the destination partition, I could combine the end of one destination’s record with the beginning of another. I don’t partition by month here: a trip starting at the end of a month should still include its days in the next month.
This excerpt counts usable maximum-temperature observations and hot days within each window.
analyze_weather_all56region.py: daily counts
SELECT
destination_id,
date AS start_date,
count(*) OVER trip AS days_in_window,
max(date) OVER trip AS end_date,
count(*) FILTER (
WHERE temp_max_quality = 8 AND temp_max_c IS NOT NULL
) OVER trip AS valid_temp_days,
count(*) FILTER (
WHERE temp_max_quality = 8 AND temp_max_c >= 30
) OVER trip AS hot_days
FROM weather_daily
WINDOW trip AS (
PARTITION BY destination_id
ORDER BY date
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING
)FILTER limits which rows count. With OVER trip, the count applies to each four-day window and appears on the row for its start date. A GROUP BY would combine rows into groups; here, I keep a row for each start date and add the window’s results to it.
In the full implementation, I also count usable precipitation observations, days at or above 35°C, days below 10°C, and days with precipitation at or above 1mm and 30mm. I expose those results through a view named trips_4d.
Separate missing data from unsuitable weather
If I removed poor-quality observations before taking groups of four rows, I could join dates that weren’t consecutive. Keeping the dates lets me count how many days in an itinerary have usable observations.
Once trips_4d contains all the required counts, I add the following checks.
analyze_weather_all56region.py: itinerary evaluation
CREATE TEMP VIEW trip_evaluation AS
SELECT *,
valid_temp_days = 4 AND valid_rain_days = 4 AS is_valid,
extreme_heat_days = 0
AND hot_days <= 1
AND cold_days = 0
AND rain_days <= 1
AND heavy_rain_days = 0 AS meets_conditions
FROM trips_4d
WHERE days_in_window = 4 AND end_date = start_date + 3is_valid tells me whether I have four days of usable observations for both measurements. meets_conditions tells me whether the counted days fit the weather rules. I keep those questions separate so that a day with missing precipitation doesn’t quietly become a day with no rain. An itinerary only counts as a match when both expressions are true.
The final WHERE requires four rows and an end date three days after the start date. December 29–31, 2025, can’t form complete itineraries because the input ends before the following year’s observations. That leaves 3,653 − 3 = 3,650 candidate itineraries per destination.
A window from December 31, 2024, through January 3, 2025, does qualify. I keep trips that cross a month or year boundary and assign them to the month of their start date.
Keep track of the denominator
After building the itineraries, I group them by destination and departure month. This query runs on the connection where I created trip_evaluation.
analyze_weather_all56region.py: monthly summary
SELECT
destination_id,
month(start_date) AS start_month,
count(*) AS candidate_trips,
count(*) FILTER (WHERE is_valid) AS valid_trips,
candidate_trips - valid_trips AS excluded_trips,
round(100.0 * valid_trips / candidate_trips, 1) AS valid_trips_pct,
count(*) FILTER (
WHERE is_valid AND meets_conditions
) AS matching_trips,
round(
100.0 * matching_trips / nullif(valid_trips, 0), 1
) AS matching_trips_pct
FROM trip_evaluation
GROUP BY destination_id, month(start_date)DuckDB allows references to earlier aliases in the same SELECT. That’s why I can use valid_trips and matching_trips in the later expressions.
I keep two percentages in the output.
| Column | Calculation | Meaning |
|---|---|---|
valid_trips_pct | Valid itineraries ÷ candidate itineraries × 100 | How much of the data I could evaluate |
matching_trips_pct | Matching itineraries ÷ valid itineraries × 100 | How often the evaluable itineraries met the weather conditions |
If there are no valid itineraries, nullif(valid_trips, 0) makes the denominator NULL, so the match rate is NULL too. That means I couldn’t evaluate the weather. A rate of 0% means I could evaluate it, but none of the valid itineraries matched.
Each start date has equal weight. I don’t calculate a percentage for each year and average ten annual percentages. Years with more missing observations contribute fewer valid itineraries to the denominator.
Check the output
Run the nationwide analysis from bin/analytics/japan-travel-weather/. This updates the existing quality report and monthly summary files.
Run analyze_weather_all56region.py
uv run python analyze_weather_all56region.pyThe output files are data/processed/weather_quality_56_regions.csv and data/processed/weather_trip_summary_56_regions.csv. The first contains quality information for 56 destinations. The second has 672 rows: 56 destinations × 12 months. In the saved results, 200,083 of the 204,400 candidate itineraries were valid, with 4,317 excluded.
The overall valid rate was about 97.89%, but that number hides differences between months and destinations. For Furano (富良野) in October, the valid rate was 80.3% and the weather match rate was 29.7%. The first percentage tells me how much I could evaluate; the second tells me how much met the conditions.
When I built the nationwide version, I compared its 36 monthly rows for Tokyo (東京), Sapporo (札幌), and Naha (那覇) with the earlier three-city results. They matched. Later, I also rebuilt the four-day windows from the daily Parquet data using Python. I’ll cover that separate implementation in Part 3.
Read the percentages as historical results
I move the start date forward one day at a time. Adjacent four-day windows share three days, so these aren’t thousands of independent experiments.
A 0% match rate also says nothing about whether a place is worth visiting. It means none of the valid four-day windows met the walking-trip conditions I chose. A trip for snow, or a traveler with a different tolerance for heat, calls for different conditions.
Next, I wanted to see how favorable weather months lined up with higher accommodation demand. In Part 3, I join prefecture-level Excel statistics to these 672 rows, try a combined score, and explain why I ended up showing the two measures separately.
