Learning Python and DuckDB with Japan Travel Data, Part 1: From Weather CSVs to Parquet

AI has made learning to code feel a little strange to me.

I still need to understand the code well enough to ask useful questions and make decisions about how to build things. But when I can ask for Rust or Go and get working-looking code back, I sometimes feel less motivated to learn another language from scratch.

Then I realized something about the books and tutorials I kept putting down: I didn’t particularly care about what I was building, I mean, those ready made tutorial projects. With AI, I could work on something I was curious about with NEW language and tech stack, and ask questions as I went.

And when I got tired or stuck, I could ask to see a finished version. A bit like a cooking show: “And here’s one we baked earlier.” And ask even more how it works.

So I tried it. It was fun enough that I wanted to write up both the process and the results.

I wanted to get more comfortable with Python and DuckDB. I wasn’t starting from zero with Python or SQL, but I hadn’t built a practical project with them from start to finish. This seemed like a good way to get moving.

The subject was Japan travel. I wanted to compare destinations and months using past weather observations. How much does the choice of month change your chances of spending a few days outside without too much heat or rain?

This series follows the technical work. Part 1 covers downloading and preparing the data. Part 2 builds and evaluates four-day itineraries in SQL. Part 3 adds accommodation statistics, looks at scoring choices, and checks the results. I’m writing for readers who want to follow a public-data project through its Python and SQL code.

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

Compare destinations by departure month

I used a destination and a departure month as the unit of comparison. Monthly average temperatures wouldn’t tell me what a four-day visit might have looked like, so I built consecutive four-day windows from the historical record and counted how often they met a set of temperature and rainfall conditions.

The weather data runs from January 1, 2016, through December 31, 2025. I ended up with 56 destinations, 3,653 days per destination, and 204,568 daily rows. The resulting percentages describe that historical period. They aren’t forecasts for a future trip.

For the nationwide analysis, I used daily maximum temperature and daily precipitation. I started with a short stretch of Tokyo (東京) data, expanded to ten years, added Sapporo (札幌) and Naha (那覇), and then worked through the larger set. I looked at other measurements during the early exercises, but kept these two for the nationwide version.

Give Python and DuckDB different jobs

I used Python to retrieve and read files and check their contents. I used DuckDB to query the prepared tables with SQL. For the analysis files, I chose Parquet, a format that stores data by column and preserves types such as dates and numbers. I kept the original CSVs separately.

I put the Python project inside the same repository as this blog. The examples use bin/analytics/japan-travel-weather/ as the working directory.

I used uv to manage the Python version and dependencies. With uv already installed, these commands set up a new project at that path. They show the initial setup; don’t rerun the initialization over an existing project.

Environment setup

mkdir -p bin/analytics/japan-travel-weather
cd bin/analytics/japan-travel-weather

uv init --bare
uv python pin 3.11.14
uv add duckdb
uv run python -c "import duckdb; print(duckdb.sql('SELECT 42 AS answer').fetchall())"

The final command prints [(42,)]: a list containing one tuple, which represents the one row returned by SQL. Early on, I spent time working through outputs like this and the Python syntax around them.

uv records the dependencies and their versions in its configuration and lock files. Later, I added openpyxl for Excel and pypdf for PDFs. The examples describe the project as it stood on October 19, 2026.

The .py filenames below refer to scripts written for this project. They aren’t built-in Python or DuckDB commands. I include complete code for the short scripts and label excerpts from the larger ones. The paths match the project’s directory layout.

Read the JMA CSVs

I downloaded daily observations from the Japan Meteorological Agency’s (気象庁) historical weather data page. In the interface, I selected the station, date range, and measurements, along with the quality information and homogeneity numbers. I used a single date column and numeric values.

Some of the original CSVs use CP932, a Japanese character encoding. The later loader expects UTF-8, so I wrote convert_to_utf8.py. This is the complete script with the longer comments removed. Save it under that name and pass it the path to a CSV.

convert_to_utf8.py

import argparse
from pathlib import Path


def main():
    parser = argparse.ArgumentParser(description="Convert a CP932 CSV to UTF-8")
    parser.add_argument("source", type=Path, help="Path to the CSV to convert")
    args = parser.parse_args()
    source = args.source
    target = source.with_name(source.stem + "_utf8" + source.suffix)

    try:
        text = source.read_bytes().decode("cp932")
        with target.open("xb") as output:
            output.write(text.encode("utf-8"))
    except (OSError, UnicodeError) as error:
        parser.exit(1, f"Conversion failed: {error}\n")

    print(f"Saved: {target}")


if __name__ == "__main__":
    main()

source holds the input path. The script reads the bytes, decodes them as CP932, and writes the text as UTF-8. It saves the new file in the same directory, adding _utf8 before the extension. Opening the output with xb creates a new file and fails if that file already exists.

For example, with the Tokyo (東京) source file at the following path, run this from the project directory.

Run convert_to_utf8.py

uv run python convert_to_utf8.py data/interim/jma_tokyo_daily_2025.csv
# Output: data/interim/jma_tokyo_daily_2025_utf8.csv

The CSV doesn’t start with observations on its first line. It has several header rows for the download timestamp, station name, measurement names, and supplementary fields. Precipitation can also have a “no phenomenon” information column. The JMA (気象庁) CSV documentation explains those fields and when they appear.

Before writing a loader, I wanted to see where the dates and values started. I wrote inspect_csv.py to print the first nine rows of the converted Tokyo (東京) file. This is the complete code with the comments trimmed.

inspect_csv.py

from pathlib import Path
import csv

csv_path = Path("data/interim/jma_tokyo_daily_2025_utf8.csv")

with csv_path.open(encoding="utf-8", newline="") as csv_file:
    reader = csv.reader(csv_file)
    for line_number, row in zip(range(1, 10), reader):
        print(
            f"line={line_number:>2}",
            f"columns={len(row):>2}",
            repr(row[:8]),
        )

columns shows the number of columns in a row; the last value shows the first eight fields. Change csv_path to inspect another file. In the first learning exercises, I specified the column positions. For the nationwide version, I switched to locating columns by their headers.

The post-download quality checker can read both CP932 and UTF-8. The conversion step is needed for the nationwide loader that comes afterward.

Keep quality flags separate from homogeneity numbers

A numeric value isn’t enough to make an observation usable. For both maximum temperature and precipitation, I required a quality flag of 8 and a non-NULL value. In the JMA (気象庁) data, 8 identifies a normal value.

Homogeneity numbers serve a different purpose. I kept them to help identify changes in observation conditions. Periods with different homogeneity numbers may not be suitable for a straightforward comparison. The numbers also aren’t quality scores you can compare across stations. I needed to read the definitions of both fields separately.

I retained dates even when measurements were missing or had poor quality flags. Later, I would build four-day windows. If I deleted rows first, four consecutive rows could span more than four calendar days. I waited until after constructing each itinerary to decide whether its observations were usable.

Choose 56 destinations from 60 candidates

I kept a CSV mapping each destination to a weather station. The names don’t always match: I used Saitama (さいたま) observations for Kawagoe (川越), Tsujido (辻堂) for Kamakura (鎌倉), and Oita (大分) for Beppu (別府).

I started with 60 candidates. I left Nikko (日光) out of the download set because the observation site changed during the study period. On November 16, 2022, the Imaichi (今市) station moved and became Nikko-Higashimachi (日光東町). JMA (気象庁) states that it discontinued the old statistical series at the move, so the observations before and after it should not be treated as one continuous series. See the JMA (気象庁) relocation notice.

Joining the two records could mix changes in weather from year to year with differences caused by the observation site. I wanted ten years of observations for the same location, so I didn’t combine Imaichi (今市) and Nikko-Higashimachi (日光東町) into one destination record.

I also left Hakone (箱根) and the Kumano Hongu area (熊野本宮周辺) out of the download set because I hadn’t settled on representative stations for them. I downloaded the remaining 57 station records, then held Nara (奈良) out of the main analysis because its maximum-temperature data had more than one homogeneity number.

That doesn’t establish that Nara’s (奈良) data is unusable. It means I kept 57 downloaded station records and used 56 destinations in this analysis. For destinations represented by a nearby station, I also wouldn’t assume that one station captures the weather across the whole visitor area.

The table lists all 60 candidates. “Included” means included in the main analysis.

Download the nationwide data

I downloaded daily observations for 57 JMA (気象庁) stations as CSV files.

I saved one source file per station. I kept missing and poor-quality values in those files and decided whether to use them later. Alongside each CSV, I saved the request conditions, retrieval timestamp, station information, and a SHA-256 hash for checking the file’s contents.

After downloading, I checked the station name, required columns, and coverage of all 3,653 dates. Having a row for each date doesn’t mean that every measurement is usable. The loader and the itinerary checks in Part 2 handle that distinction.

To check the saved CSVs again, run this from the project directory.

Run check_weather_coverage.py

uv run python check_weather_coverage.py \
  --input-dir data/raw/jma_daily_57_auto \
  --output data/processed/coverage_daily_check_auto.csv

Match columns by name and save to Parquet

I prepared UTF-8 CSVs for the 56 selected destinations in data/interim/56_region_to_analyze/. The loader doesn’t create that directory’s contents from the 57-station source folder. Selecting the files, leaving out Nara (奈良), and converting the encoding are separate preparation steps. The existing project has those inputs ready.

In load_weather_56.py, I identify columns using the measurement name and the supplementary field name together. This excerpt defines the mapping. The last two values in each tuple are the output column name and its data type.

load_weather_56.py

FIELDS = [
    ("最高気温(℃)", "", "temp_max_c", "DOUBLE"),
    ("最高気温(℃)", "品質情報", "temp_max_quality", "INTEGER"),
    ("最高気温(℃)", "均質番号", "temp_max_homogeneity", "INTEGER"),
    ("降水量の合計(mm)", "", "precipitation_mm", "DOUBLE"),
    ("降水量の合計(mm)", "現象なし情報", "precipitation_no_event", "INTEGER"),
    ("降水量の合計(mm)", "品質情報", "precipitation_quality", "INTEGER"),
    ("降水量の合計(mm)", "均質番号", "precipitation_homogeneity", "INTEGER"),
]

The Japanese strings here are the actual CSV headers, so they need to stay in Japanese. For example, the loader finds the column labeled 降水量の合計(mm) plus 品質情報 and stores it as precipitation_quality. Those labels mean total precipitation in millimeters and quality information. If the optional 現象なし情報 column is absent, the loader writes NULL. A missing required column stops the import.

The script also checks the input file list for missing or unexpected files. Each output row includes the destination ID, prefecture, station number, date, and source filename. I store station numbers as strings because they are identifiers, dates as DATE, temperatures and precipitation as DOUBLE, and quality flags as INTEGER.

Run load_weather_56.py

uv run python load_weather_56.py

The output is data/processed/jma_56_regions_daily_2016_2025.parquet. Before saving, I check for duplicate destination/date pairs, the date range, and row counts. I then write a temporary Parquet file, read it back, and compare it with the original table in both directions.

load_weather_56.py: check the saved data

(SELECT * FROM weather_daily EXCEPT ALL SELECT * FROM saved)
UNION ALL
(SELECT * FROM saved EXCEPT ALL SELECT * FROM weather_daily)
LIMIT 1

This query runs after the loader has created weather_daily and saved. A one-way comparison could miss added or lost rows, depending on which direction I checked. If both comparisons return no differences, the loader moves the temporary file to the final output path.

The data is ready for itinerary checks

At this point, I had 204,568 daily rows for 56 destinations, with explicit types for dates and measurements. Each row retained the quality flags, homogeneity numbers, and source CSV name. I had checked the data’s structure; I hadn’t yet decided whether a given stretch of weather met the travel conditions.

In Part 2, I use this Parquet file to build four-day windows starting on each date. I separate the percentage of itineraries I can evaluate from the percentage that meet the weather conditions.