How to Create Pandas Dataframe from a complex List

Question

I have the following result set from a query:

[{'192804': [{},
   [(1652994300000, 590.0),
    (1652994420000, 560.0),
    (1652996220000, 560.0),
    (1652996520000, 440.0),
    (1652997000000, 180.0),
    (1652997300000, 0.0)]]},
 {'192805': [{},
   [(1652995800000, 0.0),
    (1652997600000, 0.0),
    (1652999400000, 5.0),
    (1653001200000, 5.0),
    (1653003000000, 5.0),
    (1653048000000, 5.0)]]}]

I would like to display the results in a pandas dataframe to look like the following:

               '192804' '192805'
1652994300000     590   
1652994420000     560   
1652995800000                 0
1652996220000     560   
1652996520000     440   
1652997000000     180   
1652997300000       0   
1652997600000                 0
1652999400000                 5
1653001200000                 5
1653003000000                 5
1653048000000                 5

Would anyone know how to do that?

I have tried:

import pandas as pd
    
Data = [{'192804': [{},
   [(1652994300000, 590.0),
    ... .... ......  .......
    (1653048000000, 5.0)]]}]

df = pd.DataFrame(list(Data))

It gets me a result, but not the format I am striving for.

Shubham Sharma · Accepted Answer · 2022-05-20 12:20:52Z

2

Flatten the complex list with dict comprehension then create a dataframe and optionally sort the index:

pd.DataFrame({k: dict(v[1]) for d in data for k, v in d.items()}).sort_index()

               192804  192805
1652994300000   590.0     NaN
1652994420000   560.0     NaN
1652995800000     NaN     0.0
1652996220000   560.0     NaN
1652996520000   440.0     NaN
1652997000000   180.0     NaN
1652997300000     0.0     NaN
1652997600000     NaN     0.0
1652999400000     NaN     5.0
1653001200000     NaN     5.0
1653003000000     NaN     5.0
1653048000000     NaN     5.0

answered May 20, 2022 at 12:20

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Create Pandas Dataframe from a complex List

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related