CODEWITHSUNDEEP
pythonpython-3.xpandas
Bob
Bob

Reputation: 415

Converting lists of lists of tuples to MultiIndex pandas dataframe

I have a structure like this: 

[
  [
    ('2019-12-01', '0.03555', '0.03', '0.03', '0.03'), 
    ('2019-12-02', '0.03', '0.03', '1', '0.03')
  ],
  [
    ('2019-12-01', '0.111', '0.02', '0.03', '0.03'), 
    ('2019-12-02', '0.03', '0.03', '0.03', '0.03')
  ]
]

I would like each list entry to be an index in a pandas dataframe, with the tuples being rows in the df. Something like this: 

                         LIST_1                      LIST_2
         date      p1    p2     p3    p4    |   p1    p2     p3    p4
0   2019-12-01  0.03555  0.03  0.03   0.03  | 0.03  0.03  0.03   0.03
1   2019-12-02     0.03  0.03     1   0.03  | 0.03  0.03  0.03   0.03

I know this is messy, to be honest, I'm unsure the best way to structure it in Pandas as I'm new to it, so any advice would be appreciated.

I have tried to flatten the strucutre using:

d = pd.DataFrame([t for lst in a for t in lst])

But then I just end up with a df as expect like this: 

        0          1     2     3      4
0   2019-12-01  0.03555  0.03  0.03   0.03
1   2019-12-02     0.03  0.03     1   0.03
2   2019-12-01    0.111  0.02  0.03   0.03
3   2019-12-02     0.03  0.03  0.03   0.03

But this isn't suitable

Upvotes: 1

Views: 348

Answers (1)

jezrael
jezrael

Reputation: 862581

Use list comprehension for create first level of MultiIndex by range with length of list lst with f-strings.

Then use main list comprehension by all values of list with convert inner list to DateFrames, create index by first column by DataFrame.set_index, then rename columns by DataFrame.add_prefix.

Last join all list of DataFrames by concat with keys parameter for first level of MultiIndex and remove index name 0 by DataFrame.rename_axis:

L = [f'LIST_{i}' for i in range(1, len(lst)+1)]
df = (pd.concat([pd.DataFrame(x).set_index(0).add_prefix('p') for x in lst], axis=1, keys=L)
        .rename_axis(None))
print (df)
             LIST_1                   LIST_2                  
                 p1    p2    p3    p4     p1    p2    p3    p4
2019-12-01  0.03555  0.03  0.03  0.03  0.111  0.02  0.03  0.03
2019-12-02     0.03  0.03     1  0.03   0.03  0.03  0.03  0.03

Upvotes: 1

Related Questions