Convert dict of array of dict to DataFrame

Question

Here is my timeseries. Timestamp may be either the same or different. I need to convert data to single DataFrame:

{'param1': [
    {'ts': 1669246574000, 'value': '6.06'}, 
    {'ts': 1669242973000, 'value': '6.5'}
   ],
'param2': [
    {'ts': 1669246579000, 'value': '7'}, 
    {'ts': 1669242973000, 'value': '5'}
   ],
}

Update 1: format of DataFrame

          ts param1 param2
1669246574000  6.06  1
1669242973000   6.5  2
1669246579000     7  3
1669242973000     5  4

Update 2: Timestamp (ts) should be index

          ts param1 param2
1669242973000   6.5  5
1669246574000  6.06  Nan
1669246579000   Nan  7

Update 3: my solution

data_frames = []
for key, values in data.items():
    df = pd.DataFrame(values).set_index('ts').rename(columns={'value': key})
    data_frames.append(df)
data_frame = pd.concat(data_frames, axis=1)

Andrej Kesely · Accepted Answer · 2022-12-13T17:25:18.227

Try:

import pandas as pd

data = {
    "param1": [
        {"ts": 1669246574000, "value": "6.06"},
        {"ts": 1669242973000, "value": "6.5"},
    ],
    "param2": [
        {"ts": 1669246579000, "value": "7"},
        {"ts": 1669242973000, "value": "5"},
    ],
}

df = pd.concat([pd.DataFrame(v).assign(param=k) for k, v in data.items()])
print(df)

Prints:

              ts value   param
0  1669246574000  6.06  param1
1  1669242973000   6.5  param1
0  1669246579000     7  param2
1  1669242973000     5  param2

EDIT: With updated question:

import pandas as pd
from itertools import count

data = {
    "param1": [
        {"ts": 1669246574000, "value": "6.06"},
        {"ts": 1669242973000, "value": "6.5"},
    ],
    "param2": [
        {"ts": 1669246579000, "value": "7"},
        {"ts": 1669242973000, "value": "5"},
    ],
}


c = count(1)
df = pd.DataFrame(
    [
        {"ts": d["ts"], "param1": d["value"], "param2": next(c)}
        for v in data.values()
        for d in v
    ]
)
print(df)

Prints:

              ts param1  param2
0  1669246574000   6.06       1
1  1669242973000    6.5       2
2  1669246579000      7       3
3  1669242973000      5       4

Sorry for that but could you please look again at question's description? I added the desired format — devaskim, Dec 13 '22 at 17:21
Twice my bad and the last I hope. Timestamp should be index. If you have time, please, look at Update 2 — devaskim, Dec 14 '22 at 06:52
Finally, I end up with `pd.concat(frames, axis=1)` where each item in `frames` I got in [this way](https://stackoverflow.com/questions/74794636/convert-array-of-dicts-to-timestamp-indexed-dataframe). — devaskim, Dec 14 '22 at 08:04

Convert dict of array of dict to DataFrame

1 Answers1