Introduction

I carried out this project independently for a university Data Engineering course. I rescued and standardized historical climate data from the KNMI archives, specifically from the Caribbean part of the Kingdom of the Netherlands and Suriname. The archives contain weather tables dating back to 1896 and spanning up to 1962, covering six countries, with one Excel or TXT file per year. While a portion of this data had already been manually transcribed, it required further processing to integrate into modern systems.

My objectives were:

1. Convert data to SEF (Station Exchange Format): I prepared the data for submission to the upcoming ERA6 Reanalysis by converting it into SEF files, a TSV format with metadata headers (station location, height) followed by recordings of a single variable, such as air pressure.
2. Create a training dataset for machine learning: I developed a dataset of transcribed tables paired with their corresponding images, which will train ML models to automate transcription of handwritten or printed tables, accelerating future data rescue efforts.

To expand the scope and robustness of the project, I incorporated two additional historical datasets:

• Dr. Holyoke’s Dataset: weather records from Salem, Massachusetts, spanning 1786–1829, with pressure and temperature readings taken three times daily.
• Prof. Wigglesworth’s Dataset: weather records from Cambridge, Massachusetts, spanning 1786–1789, with indoor/outdoor temperature (three times daily) and pressure (twice daily).

I also converted both datasets to SEF format to ensure consistency and usability across the project.

I made all code and workflows open-source and available on GitHub.

Data Sources

I used the following datasets:

• KNMI Archives: historical climate records for the Caribbean and Suriname in Excel and TXT formats, including temperature, precipitation, and other meteorological observations spanning 1896–1962 across six countries.
• Dr. Holyoke’s Dataset: climate observations from Salem, Massachusetts (1786–1829), complementing the KNMI data with regional pressure and temperature readings.
• Prof. Wigglesworth’s Dataset: historical records from Cambridge, Massachusetts (1786–1789), expanding temporal and geographical coverage with indoor/outdoor temperature and pressure data.

Methodology

Step 1: Conversion to CSV

To streamline the data processing pipeline, I first converted all raw files into CSV format for easier loading into DuckDB. This step was critical due to the diverse formatting of the source files (Excel, TXT, and .dat).

KNMI Data

For the KNMI datasets, I varied the conversion process based on the structure of each source file:

• Aruba, Bonaire, Curaçao, Saba, and Statia: these datasets followed a consistent format, with Excel files containing sheets named Regen (rain) and Overig (other). Each sheet had a single header row followed by data entries, so I converted them straightforwardly using Pandas.

for sheet in ['Regen', 'Overig']:
    name, ext = file.split('.')
    df = pd.DataFrame(
        pd.read_excel(
            f"digitized/{dir}/{file}", sheet_name=sheet)
    )
    df.to_csv(f'CSVs/{dir}/{name}_{sheet}.csv')

• Suriname: this dataset had a single sheet with a multiline header and some rows containing 10-daily totals, so I adjusted the extraction function to account for the multiline header, ensuring accurate conversion to CSV.
• Willemstad: the data for Willemstad was provided as CSV-formatted TXT files, which I extracted using Pandas. However, the files were encoded in latin1 (ISO/IEC 8859-1), so I adjusted the code to handle the encoding.

Holyoke & Wigglesworth Data

The Holyoke and Wigglesworth datasets came as .dat files with a regular but unique structure:

• Each file began with a line containing the year, followed by a row for the month names (3 per line), and then 31 lines of data for each day of the month.
• Months with fewer than 31 days resulted in NULL values for the extra days.

To convert these files to CSV, I:

1. Read the file line by line, grouping them into sets of 33 lines (1 year + 1 month names + 31 data lines).
2. Extracted the year, month names, and daily data for each group and stored them as tuples in a list.
3. Converted the tuples into a Pandas DataFrame, with strings for year, month, and day converted to datetime objects.

# Convert Holyoke .dat files to CSV
def convert_holyoke(file_path):
    with open(file_path) as f:
        lines = f.readlines()
    
    datalist = []
    for offset in range(140):
        year, *months = lines[offset*33].strip(), lines[offset*33+1].split()
        for i in range(3):
            for day in range(31):
                date_str = f'{year}_{months[i]}_{day+1}'
                datalist.append((date_str, lines[offset*33+day+2].split()[2*i+1:2*i+3]))
    
    return pd.DataFrame(datalist).to_csv(file_path + '.csv', index=False)

Step 2: Loading and Joining Datasets in DuckDB

After converting the files to CSV, I loaded and joined the datasets in DuckDB for efficient processing and quality assessment.

KNMI Data

For the KNMI datasets, I loaded the CSV files for Regen (rain) and Overig (other) into DuckDB and joined them on common keys, such as station ID and date. I applied special handling for:

• Suriname: the CSV files had a multiline header, so I skipped the first two lines during loading, assigned custom column names, and loaded the data with null_padding and ignore_errors enabled to handle inconsistencies.

SELECT
    column01 AS "Datum",
    column02 AS "Luchtdruk_8",
    ...
    column32 AS "Regen_wrong",
FROM read_csv('CSVs/Suriname/*.csv', union_by_name = true, dateformat = '\%Y\%m\%d', skip = 2, null_padding = true, ignore_errors = true);

• Willemstad: similar to Suriname, I also skipped headers and handled encoding issues.
• NULL Value Handling: the original files used placeholders like '-' (for most KNMI data) or '//////' (for Willemstad) to represent NULL values, so I replaced these with actual NULLs using a Python loop and SQL updates.

UPDATE Aruba
SET col = NULL
WHERE TRIM(col) = '-';

For Willemstad, I targeted the placeholder '//////' instead.

Joining Tables: I joined the Regen and Overig tables for each location using a FULL JOIN on station (Stn) and date (Datum):

CREATE TABLE IF NOT EXISTS Aruba AS
SELECT * FROM
(SELECT * FROM read_csv('CSVs/Aruba/*Regen.csv', union_by_name = true, dateformat = '\%Y\%m\%d'))
FULL JOIN 
(SELECT * FROM read_csv('CSVs/Aruba/*Overig.csv', union_by_name = true, dateformat = '\%Y\%m\%d'))
USING (Stn, Datum);

Holyoke & Wigglesworth Data

The CSV conversion produced four separate files (temperature and pressure for both Holyoke and Wigglesworth). I loaded them into DuckDB and addressed NULL placeholders:

• Holyoke: pressure data used 9999.9 as a NULL placeholder, while temperature data used -99.0.
• Wigglesworth: pressure data used 100 as a NULL placeholder, while temperature data used -99.9.

After setting the NULL values, I joined the pressure and temperature tables to create a single table for each dataset (Holyoke and Wigglesworth).

Step 3: Data Exploration and Quality Assessment

After loading the datasets into DuckDB, I performed data exploration to validate the integrity and accuracy of the data.

KNMI Data

• NULL Values: I discovered that joining yearly datasets introduced additional NULL values. All rows contained at least the date and station number, but the variety of measured variables resulted in a high percentage of NULLs across the datasets. The table below summarizes the dataset sizes and NULL percentages:

• Column Validation: I used DuckDB’s SUMMARIZE to inspect columns for minimum, maximum, unique entries, and averages. I further inspected columns with unusual characteristics by checking distinct entries and comparing them to expected ranges, such as temperature in Curaçao should not drop below 0°C. This confirmed that all tables, except for Willemstad, were valid.
• Willemstad Precipitation Issue: I found that the Willemstad data was recorded without decimals, requiring manual inference for decimal placement. For most columns, this was straightforward, but precipitation data posed a challenge.

• When I divided by 10, I found unrealistic values, such as greater than 500 mm/day, which did not align with historical records.
• Dividing by 100 was inconsistent with other variables and measurement instruments of the era.
• My investigation revealed that pre-1922 data was consistent with division by 10, but post-1922 data still had issues. After discussing with KNMI, I excluded Willemstad precipitation data from further processing.

Time Zone Conversion: I discovered that times were recorded in local time, with time zones varying by country and year. Automatic conversion using DuckDB’s IANA Time Zone Database (TZDB) failed due to historical inaccuracies, so I manually set time zones based on historical data:

-- Adjust timestamps for Curaçao's historical timezone change (1912)
ALTER TABLE Curacao_Temp ADD COLUMN timestamp TIMESTAMP WITH TIME ZONE;

UPDATE Curacao_Temp AS a
SET timestamp = (
    CASE
        WHEN a.Tijd IS NULL OR a.Tijd = 24 THEN
            (a.Datum + INTERVAL '24 hours') AT TIME ZONE 'UTC'
        WHEN a.Datum < '1912-02-12' THEN
            (a.Datum + to_hours(a.Tijd/100) + to_minutes(a.Tijd%100) +
             INTERVAL '4 hours 35 minutes 24 seconds') AT TIME ZONE 'UTC'
        ELSE
            (a.Datum + to_hours(a.Tijd/100) + to_minutes(a.Tijd%100) +
             INTERVAL '4 hours 30 minutes') AT TIME ZONE 'UTC'
    END
);

For Saba, St. Eustatius, and Bonaire, historical time zone data was unavailable, so I used the closest available time zone as an approximation.

Holyoke & Wigglesworth Data

• Column Validation: I applied the same workflow as for KNMI data, inspecting columns for reasonable ranges. I found no issues.
• Time Zone Adjustment: I used the America/New_York time zone, which DuckDB handled correctly without cross-links or historical discrepancies.

Step 4: Reshaping and Wrangling

I designed the reshaping and wrangling step to prepare the data for two downstream tasks:

1. SEF Conversion: report all observations of one variable from one weather station in a single row per timestamp.
2. Training Data Preparation: include observations of all variables from one weather station during one month in a single row per date.

To balance these requirements, I structured the database to join observations by time and weather station, keeping separate tables for each station. This approach ensures flexibility for both use cases.

KNMI Data

• Aruba, Bonaire, Curaçao, Saba, St. Eustatius, and Willemstad: after quality control, I required no additional wrangling. I joined the pressure and temperature tables, resulting in the desired structure: one row per station per timestamp.
• Suriname: the Suriname dataset required significant wrangling due to its unique structure:

• Measurements for a single variable were split across multiple columns, for example air pressure recorded at 8:00, 12:00, 18:00, and so on.
• The times of measurement varied by year:
  • 1896–1899: measurements at 8:00, 12:00, 18:00.
  • 1899–1904: measurements at 8:00, 14:00, 19:00.
  • 1905 onward: measurements at 8:00, 14:00, 18:00, 19:00.

My wrangling steps:

1. Loaded CSV files for each group of years into separate tables.
2. Assigned column names including the time of measurement, such as Temperature_8 for 8:00 temperature.
3. Joined the tables by including every possible column in each table, using NULL values where a column was not present for a specific group of years.
4. Normalized column names to exclude the time of measurement and set the timestamp column.

This resulted in a table with the same structure as the other KNMI datasets.

After wrangling, I stored the data as Parquet files, with one file per country.

Holyoke & Wigglesworth Data

• The .dat files for Holyoke and Wigglesworth were structured similarly to Suriname, with one column per variable per time of day. However, the measurement times were consistent across all years, simplifying the process.

My wrangling steps:

1. Selected groups of columns with the same measurement time.
2. Added a timestamp column for each group.
3. Joined the groups of columns to create a final table with variables and their measurements at each timestamp.

Step 5: Creating Data Products

SEF Conversion

The Station Exchange Format (SEF) requires all observations of one variable from one weather station to be reported in a standardized TSV format with metadata headers. To convert the wrangled data to SEF, I:

1. Set Up Metadata: defined metadata for each station, including station ID, name, latitude, longitude, altitude, variable (Vbl), statistic (Stat), units (Units), and source information.
2. Extracted Data for One Variable: used DuckDB to select the relevant columns, such as timestamp, precipitation, and quality flag.

df << SELECT timestamp, Precipitation, qPrecipitation FROM aruba.parquet

3. Prepared the Data:
   • Converted timestamps to UTC and formatted them as Year, Month, Day, Hour, and Minute.
   • Added decimal precision, such as dividing precipitation by 10.
   • Included quality control metadata, such as a qc= flag.
4. Wrote to SEF Format: I wrote the metadata header followed by the data table in TSV format.

Training Data Creation

I designed the training data to include all variables from one weather station during one month in a single row per date. This structure is ideal for training machine learning models for automated transcription.

Example query for Bonaire (1920):

-- Create training data: Bonaire 1920
CREATE TABLE Bonaire_1920 AS
SELECT *
FROM (SELECT Date, maxPressure, minPressure, maxTemperature, minTemperature
      FROM 'dbs/bonaire.parquet' WHERE maxPressure IS NOT NULL)
JOIN (SELECT Date, Precipitation AS rainKralendijk
      FROM 'dbs/bonaire.parquet' WHERE Stn == 44) USING (Date)
JOIN (SELECT Date, Precipitation AS rainRincon
      FROM 'dbs/bonaire.parquet' WHERE Stn == 42) USING (Date)
WHERE Date < '1921-01-01'
ORDER BY Date;

Conclusion

I prepared a set of datasets of rescued climate data for two downstream tasks: incorporation in the ERA reanalysis dataset and preparation of a training dataset. Using Python and DuckDB, I created a coherent, easy-to-use dataset from the original Excel and TXT files.

My quality control process primarily involved handling NULL values, converting timestamps, and manually validating column entries. Part of the dataset required significant wrangling due to measurements at different times being split across columns and measurement times varying over the years. By normalizing column names and timestamps, I transformed this into a consistent format, ready for downstream tasks.

I stored the final datasets as Parquet files, ensuring robustness and ease of use for future applications. I will make the datasets accessible through multiple platforms: the ERA reanalysis dataset, the KNMI Data Platform, and a GitHub repository. This ensures that the data is available for downstream use and easy to find.

The methodologies and tools I used in this project can serve as a blueprint for similar initiatives. The workflow I developed can be applied to similar datasets of rescued data annotated into Excel files that need to be structured for SEF conversion. Additionally, the steps I took to prepare this dataset can be investigated to determine best practices for formatting rescued data during the transcription process. Keeping the downstream processing in mind during transcription can speed up the process and increase accuracy by reducing errors and uncertainties in data processing.

Tools and Technologies

References

• KNMI Data Platform
• ERA5 Reanalysis Dataset
• ERA6 Reanalysis
• Station Exchange Format (SEF)
• Pandas Documentation
• DuckDB Documentation
• Apache Parquet
• IANA Time Zone Database (TZDB)
• Schrier, G. van der, et al. (2008). Daily Temperature and Precipitation in Salem, Massachusetts, 1786–1829. Link to paper
• Schrier, G. van der, et al. (2008). Storminess in Cambridge, Massachusetts, 1786–1789. Link to paper

Questions or feedback? Reach out or contribute to my GitHub repository.