Dendrology · Archives · Writing CSV Files

Writing CSV Files: The Field Notebook Every Tool Can Read

A forecast pulled from an API is perishable — useful today, gone tomorrow unless you write it down. For historical weather data, the notebook nearly everyone writes in is CSV: comma-separated values, plain text arranged in rows and columns. Spreadsheets open it, databases import it, pandas reads it, and a scientist in 2050 will still be able to make sense of it with any text editor. This entry covers reading and writing CSV in Python, the pitfalls that quietly corrupt weather datasets, and where the world’s historical weather CSVs come from.

The Lingua Franca: Why Plain Rows and Columns Won

Think of CSV as a shared field notebook. Every page is ruled the same way: the top line names the columns, and each line below is one observation. No binary format, no special instrument needed to read it back — which is precisely why it became the common language of data exchange. When two tools that have never heard of each other need to trade a decade of daily temperatures, CSV is the notebook they both already know how to read.

date,tmax_c,tmin_c,precip_mm
2026-08-01,31.2,17.8,0.0
2026-08-02,28.4,16.1,4.6

The notebook’s simplicity is also its weakness: the page records only text. It does not know that 2026-08-01 is a date, that 31.2 is a number, or that the temperatures are Celsius. Whoever writes the notebook carries that responsibility — which is where the pitfalls below come from.

Writing and Reading with Python

Python’s standard library ships a csv module; pandas adds a heavier-duty reader. A tight round trip using both:

import csv

rows = [
    {"date": "2026-08-01", "tmax_c": 31.2, "tmin_c": 17.8, "precip_mm": 0.0},
    {"date": "2026-08-02", "tmax_c": 28.4, "tmin_c": 16.1, "precip_mm": 4.6},
]

# newline="" lets the csv module manage line endings itself
with open("daily_weather.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["date", "tmax_c", "tmin_c", "precip_mm"])
    writer.writeheader()
    writer.writerows(rows)

import pandas as pd
df = pd.read_csv("daily_weather.csv", parse_dates=["date"])
print(df.dtypes)   # date is now datetime64, temperatures are float64

DictWriter maps dictionaries to rows and writes the header for you; its counterpart DictReader maps rows back to dictionaries. On the pandas side, read_csv pulls the file into a DataFrame and DataFrame.to_csv writes one back out. Note the two arguments to open: the csv documentation is explicit that newline="" should always be passed (otherwise extra carriage returns can sneak into files on some platforms), and naming the encoding avoids surprises we will meet shortly.

Five Ways the Notebook Betrays You

Missing or misleading headers. A file without a header row is a notebook with unlabeled columns — guesswork forever after. Always write headers, and put units in the names: tmax_c and precip_mm answer the questions tmax and precip leave open.
Everything is text. CSV stores strings; types are reconstructed on read. Dates are the classic casualty — stick to ISO format (2026-08-01) and parse explicitly with parse_dates. Where a column’s type matters, pin it with read_csv’s dtype parameter rather than trusting inference.
Missing values in disguise. By default pandas treats blanks and strings like NaN, N/A, and NULL as missing. But weather archives sometimes mark absent readings with sentinel numbers instead — check the dataset’s documentation, and declare any sentinels via the na_values parameter, or a season of “-9999 degree” days will wreck your averages.
Encoding surprises. Degree symbols and accented station names are where mystery characters appear. Write and read with an explicit encoding="utf-8" (both open and pandas accept it) instead of inheriting whatever the operating system defaults to.
Timezone traps. A timestamp column with no timezone is a field note with no location. Open-Meteo, for example, returns timestamps in GMT unless you request a timezone — so a naive “daily maximum” computed from those rows can land on the wrong local day. Record the timezone in the data or its documentation, and convert deliberately, not accidentally.

Where Historical Weather CSVs Come From

Two dependable starting points. NOAA’s Climate Data Online provides free access to NCEI’s archive of global historical weather and climate data — daily, monthly, seasonal, and yearly station records you can search by station, ZIP code, city, or country. And Open-Meteo’s Historical Weather API serves reconstructed hourly weather from 1940 to the present for any coordinates: pass latitude, longitude, start_date, and end_date (as yyyy-mm-dd) to its /v1/archive endpoint, and add format=csv to receive the response as a ready-made CSV instead of JSON.

In Action at Dendrology

The archive behind the forecasts. The genetic and heuristic algorithm entries both learn by comparing predictions against what actually happened. That comparison needs seasons of history — CSVs accumulated day by day from the API pulls described in Importing from APIs.
Irrigation lookbacks. The irrigation rigging entry leans on precipitation forecasts; tuning those schedules over time means holding predicted rain next to measured rain across whole growing seasons — exactly the kind of long, tidy table CSV is built for.
A pipeline, not a pile. Dendrology’s data layer is designed around this one-two: APIs bring the weather in, CSVs write it down, and every model downstream reads from the same well-labeled notebook.

Beyond the Basics

When the notebook fills up, the next steps are worth knowing:

Columnar formats. Parquet and friends store types and compress far better than CSV once files reach millions of rows — CSV for exchange, columnar for scale.
Schema validation. Tools that check every incoming file against a declared schema catch a renamed column before it poisons a training run.
Append strategies. A daily fetch should add rows without duplicating them; deciding how to key and deduplicate is a small design task with large payoff.
Compression. Both the csv module’s files and pandas’ readers handle gzipped CSVs, trading a little CPU for a lot of disk.

A model is only as good as its notebook. Keep the columns labeled, the dates unambiguous, and the gaps honest, and every algorithm in Dendrology’s archive has solid ground to learn from.