Handling a Weather API With Nested Arrays of Measurements

Some JSON APIs return a row-oriented list of objects. Weather and time series APIs often return something else: a column-oriented object, where each measurement is an array and the position in the array is the hour. It is compact, because the keys appear once, and it is awkward, because nothing in the structure says that temperature_2m[17] and precipitation[17] belong to the same hour. The conversion to rows is simple, but it has three or four traps, and none of them throws an error by default.

I used a response modelled on the layout of open weather APIs (an hourly block, a daily block and a unit map), generated for 30 days at one location. The values are random; the structure is what I wanted.

The shape

{"latitude": 10.8, "longitude": 106.7, "utc_offset_seconds": 25200, "timezone": "Asia/Bangkok",
 "hourly_units": {"time": "iso8601", "temperature_2m": "°C", "precipitation": "mm", "wind_speed_10m": "km/h"},
 "hourly": {"time": ["2026-06-01T00:00", "2026-06-01T01:00", ...],
            "temperature_2m": [27.1, null, 24.5, ...],
            "precipitation": [0.0, 0.3, ...],
            "wind_speed_10m": [8.2, ...]},
 "daily": {"time": ["2026-06-01", ...], "temperature_2m_max": [33.1, ...], "sunrise": ["2026-06-01T05:31", ...]}}

Each key in hourly is a column, with 720 values (30 days times 24 hours). The daily block has 30 values per column. Two blocks, two different row counts, one document. The first decision is to keep them as two tables, not to force them into one.

With pandas

A dictionary of equal-length lists is exactly what a DataFrame takes:

h = pd.DataFrame(doc["hourly"])
h.shape                      # (720, 5)
h["time"] = pd.to_datetime(h["time"])

720 rows and five columns, with temperature_2m as float64 and weather_code as int64. The null in the array became NaN: there were 8 missing temperature readings out of 720. That is correct and has a consequence. h.temperature_2m.mean() skips the NaNs, which gave 27.04, and .sum() skips them too, so a sum over a column with holes understates the total unless you note how many values are missing. Report the count of nulls next to any aggregate.

pd.json_normalize is the usual first reach for nested JSON and it is the wrong tool here:

pd.json_normalize(doc)["hourly.temperature_2m"].map(type).iloc[0]     # list

It flattened the keys (hourly.temperature_2m) and left each array as a Python list inside a single cell. Arrays of scalars that are parallel columns are not records, so build the frame from the dictionary directly.

If one array is shorter than the others, pandas stops you:

bad = dict(doc["hourly"]); bad["precipitation"] = bad["precipitation"][:-3]
pd.DataFrame(bad)
# ValueError: All arrays must be of the same length

That error is a good one. A response truncated by a network fault, or one where the API returns fewer values for a metric it did not compute, would otherwise misalign every column after the gap.

With DuckDB

DuckDB's unnest accepts several arrays in one select list and zips them by position:

select unnest(hourly.time) as time, unnest(hourly.temperature_2m) as temp,
       unnest(hourly.precipitation) as precip, unnest(hourly.wind_speed_10m) as wind
from read_json('weather.json');

720 rows in 0.012 s. This is concise, and it has the opposite behavior on unequal lengths:

select unnest([1,2,3]) a, unnest([10,20]) b;
-- (1, 10), (2, 20), (3, NULL)

The short array is padded with NULL and no warning is given. If the API sent a truncated column, the last rows would carry a NULL that looks like missing data, not a broken response. Check the lengths before unnesting:

select len(hourly.time) = len(hourly.temperature_2m)
   and len(hourly.time) = len(hourly.precipitation) as lengths_match
from read_json('weather.json');

Put that in the load step and fail the job if it is false. For an explicit position you can index each array with a generated series, hourly.time[i] and hourly.temperature_2m[i] over range(1, n+1), which makes the alignment visible, and gives you the index as a column if you want to keep it.

Units and zones are in other fields

A table with temp = 27.1 is missing information that the response did provide: the unit lives in hourly_units (°C, mm, km/h) and the time zone in timezone and utc_offset_seconds. The time strings are 2026-06-01T00:00, with no zone, so they are local times in Asia/Bangkok, 25200 seconds (7 hours) ahead of UTC. pd.to_datetime made a timezone-naive column, and the offset stayed behind in the JSON.

Carry them forward. One approach is to put the unit in the column name (temperature_2m_c, wind_speed_10m_kmh). Another is a one-row metadata table with the coordinates, zone and units. If you combine responses from several places, add the location and offset as columns, and convert times to UTC (pd.Timestamp(t).tz_localize(zone).tz_convert("UTC")) before mixing them. A request for a different unit system changes every number, and a table that does not record which one it asked for cannot be trusted a month later.

The daily block works the same way (pd.DataFrame(doc["daily"])), and its sunrise strings are local timestamps, so parse them with the same zone. The daily maximum is not the maximum of the hourly values; it is a separate figure the API computed (perhaps over a different window), so do not expect them to agree.

Nulls, weather codes and what to check

Eight null temperatures out of 720 is 1.1 percent. A gap can be filled by interpolation, left alone, or flagged, and the choice should be explicit in your table (an is_missing column, or a count in the report). Weather codes are integers that map to descriptions in the API's documentation (WMO codes, for instance); keep the number and join to a lookup table instead of replacing it with text.

A short checklist for any column-oriented response: the arrays have equal length; the time array is strictly increasing with no gaps (compare successive differences to the expected step); units are recorded; the zone is recorded; nulls are counted. A gap in a time array is the cheapest sign that a response was cut or that a day was skipped.

steps = h["time"].diff().dropna().unique()
len(steps) == 1        # True when every step is the same, one hour

In my data there was a single step, one hour. For a daylight saving transition in a real response, the local time array can repeat or skip an hour, which is another reason to ask for UTC times when the API allows it.

For related shapes see flattening nested JSON with pandas and DuckDB and unnesting JSON arrays into rows in DuckDB, which covers arrays of objects. The DuckDB unnest documentation describes the zip behavior, and the pandas DataFrame constructor takes a dictionary of lists. To read the response by eye first, use the JSON formatter. For fetching such an API with retries and pagination, see reading JSON from API requests.

When the column-oriented shape is already what you need

You are going to plot it. Charting libraries take parallel arrays directly. Converting to rows and back is wasted work.

You compute on whole columns. NumPy and Arrow operate on columns. A column of 720 floats loaded straight into an array is faster than a table of dictionaries.

The data goes into a columnar store. Parquet and DuckDB tables are column-oriented already. Build the table from the arrays and skip the row stage.

You need only one metric. A single array is a list of numbers. doc["hourly"]["temperature_2m"] is all you need, with the matching time array for the labels.