Convert Nested API Time Series with Stringified Datetimes into a Tidy Pandas DataFrame

Turn nested API payloads with stringified datetime-value pairs into a tidy Pandas DataFrame, and prep for SQL Server via a concise reshape pipeline in Python

Turning nested API payloads into tidy tabular data is a routine step before loading into systems like SQL Server. A common stumbling block is when time series arrive not as typed key–value pairs, but as strings that merely look like them. Pandas can’t unpack those, which is why familiar tools like from_dict, concat, unstack or json_normalize feel useless in this case. Here is a minimal path from such a payload to a clean DataFrame you can ship downstream.

Problem setup

The data comes as a dict keyed by metric, each carrying a unit and a "list" that is actually a set of strings resembling datetime-to-value mappings. Pandas can’t treat these strings as a mapping or a proper index.

raw_map = {
    'data.outdoor.temperature': {
        'unit': '℃',
        'list': {
            'datetime.datetime(2025, 4, 23, 10, 0): 22.3',
            'datetime.datetime(2025, 4, 23, 14, 0): 21.3',
            'datetime.datetime(2025, 4, 23, 18, 0): 18.2',
        }
    },
    'data.indoor.temperature': {
        'unit': '℃',
        'list': {
            'datetime.datetime(2025, 4, 23, 10, 0): 23.2',
            'datetime.datetime(2025, 4, 23, 14, 0): 23.5',
            'datetime.datetime(2025, 4, 23, 18, 0): 22.9',
        }
    }
}

The desired output is a flattened table per metric, timestamp, value and unit, ready for SQL ingestion.

outdoor.temperature | 2025_04_20 | 14 | 28.1 | ℃
outdoor.temperature | 2025_04_20 | 18 | 23.8 | ℃
...etc
indoor.temperature  | 2025_04_20 | 14 | 23.5 | ℃
indoor.temperature  | 2025_04_20 | 18 | 23.8 | ℃
...etc

Why this breaks

The core issue is type fidelity. The inner "list" isn’t a dict mapping datetime objects to values; it’s a set of plain strings that merely look like key–value pairs. Pandas can’t expand or stack a set of strings into columns and rows. You first need a real mapping of datetime objects to numeric readings. Once the types are correct, a short pandas pipeline can reshape everything in one pass.

Solution

Construct a proper dictionary where timestamps are actual datetime objects and the readings are values. Then build a DataFrame from the outer dict, keep the unit as part of the index, expand the inner mapping into columns, stack to long form, and finalize with a few renames and light cleanup.

import datetime as dt
import pandas as pd

fixed_payload = {
    'data.outdoor.temperature': {
        'unit': '℃',
        'list': {
            dt.datetime(2025, 4, 23, 10, 0): '22.3',
            dt.datetime(2025, 4, 23, 14, 0): '21.3',
            dt.datetime(2025, 4, 23, 18, 0): '18.2',
        }
    },
    'data.indoor.temperature': {
        'unit': '℃',
        'list': {
            dt.datetime(2025, 4, 23, 10, 0): '23.2',
            dt.datetime(2025, 4, 23, 14, 0): '23.5',
            dt.datetime(2025, 4, 23, 18, 0): '22.9',
        }
    }
}

wide_to_long = (
    pd.DataFrame.from_dict(fixed_payload, orient='index')
      .set_index('unit', append=True)['list']
      .apply(pd.Series)
      .stack()
      .reset_index()
)

# Columns and cleanup
wide_to_long.columns = ['where', 'unit', 'date', 'temp']
wide_to_long['where'] = wide_to_long['where'].str.replace(r'^data\.', '', regex=True)
wide_to_long['temp'] = wide_to_long['temp'].pipe(pd.to_numeric)

This produces a long-form table. The intermediate result before renaming looks like this:

                    level_0 unit             level_2     0
0  data.outdoor.temperature    ℃ 2025-04-23 10:00:00  22.3
1  data.outdoor.temperature    ℃ 2025-04-23 14:00:00  21.3
2  data.outdoor.temperature    ℃ 2025-04-23 18:00:00  18.2
3   data.indoor.temperature    ℃ 2025-04-23 10:00:00  23.2
4   data.indoor.temperature    ℃ 2025-04-23 14:00:00  23.5
5   data.indoor.temperature    ℃ 2025-04-23 18:00:00  22.9

It might look slightly misaligned in monospace because the ℃ symbol is wider than standard characters. After the renames and cleanup, you get columns named where, unit, date, temp, with where values like outdoor.temperature and indoor.temperature, a proper datetime in date, and temp as numeric.

Why this matters

If the dict is coming from another function that parses JSON, inspect that step first. You want actual datetime_object: value pairs, not strings that resemble them. Once the types are correct, pandas can reliably reshape hundreds of records in one pass. If you still receive nested structures, a small routine with a few nested for-loops can convert them to a flat table, but the key is to ensure the inner time series is a true mapping rather than free-form strings. Also, consistently formatting code and data blocks improves readability and helps spot issues like stringified timestamps early.

Takeaways

Get the types right at the source, especially for time series: real datetime keys and numeric values unlock a concise pandas pipeline. With a typed inner mapping, building a tidy long-form DataFrame is a matter of constructing from the outer dict, keeping unit as part of the index, expanding the inner mapping with apply(pd.Series), stacking to long form, and doing minimal column cleanup. That’s all you need to hand a clean dataset to SQL Server or any downstream store.