如何将字典列表拆分为多个保留相同索引的列? [英] How to split a list of dictionaries into multiple columns keeping the same index?

问题描述

我有一个数据帧，该数据帧的时间戳记为时间戳，并且其列包含字典列表:

I have a data frame that has as index a timestamp and a column that has list of dictionaries:

    index                   var_A

    2019-08-21 09:05:49    [{"Date1": "Aug 21, 2017 9:09:51 AM","Date2": "Aug 21, 2017 9:09:54 AM","Id": "d5e665e5","num_ins": 108,"num_del": 0, "time": 356} , {"Date1": "Aug 21, 2017 9:09:57 AM","Date2": "Aug 21, 2017 9:09:59 AM","Id": "d5e665e5","num_ins": 218,"num_del": 5, "time": 166}]
    2019-08-21 09:05:59    [{"Date1": "Aug 21, 2017 9:10:01 AM","Date2": "Aug 21, 2017 9:11:54 AM","Id": "d5e665e5","num_ins": 348,"num_del": 72, "time": 3356} , {"Date1": "Aug 21, 2017 9:19:57 AM","Date2": "Aug 21, 2017 9:19:59 AM","Id": "d5e665e5","num_ins": 69,"num_del": 5, "time": 125}, {"Date1": "Aug 21, 2017 9:20:01 AM","Date2": "Aug 21, 2017 9:21:54 AM","Id": "f9e775f9","num_ins": 470,"num_del": 0, "time": 290} ]
    2019-08-21 09:06:04    []

我希望实现的是一个像这样的数据框:

What I wish to achieve is a dataframe like:

    index              Date1                      Date2                    Id      num_ins       num_del    time
2019-08-21 09:05:49   Aug 21, 2017 9:09:51AM   Aug 21, 2017 9:09:54AM   d5e665e5      0           108        356
2019-08-21 09:05:49   Aug 21, 2017 9:09:57AM   Aug 21, 2017 9:09:59AM   d5e665e5      218           5        166
2019-08-21 09:05:59   Aug 21, 2017 9:10:01AM   Aug 21, 2017 9:11:54AM   d5e665e5      348          72       3356
2019-08-21 09:05:59   Aug 21, 2017 9:19:57AM   Aug 21, 2017 9:19:59AM   d5e665e5      69            5        125
2019-08-21 09:05:59   Aug 21, 2017 9:20:01AM   Aug 21, 2017 9:21:54AM   f9e775f9      470           0        290
2019-08-21 09:06:04     NAN                         NAN                    NAN        NAN         NAN        NAN

推荐答案

按每个值使用 enumerate 进行循环，因为重复的inex值会创建 DataFrame ，然后创建DataFrame用于空列表和最后一个 concat :

Loop by each value with enumerate, because duplicated inex values and create DataFrames, then create DataFrame for empty lists and last concat together:

import ast

out = {}
for i, (k, v) in enumerate(df['var_A'].items()):
    df = pd.DataFrame(v)
    if df.empty:
        out[(i, k)] = pd.DataFrame(index=[0], columns=['Id'])
    else:
        out[(i, k)] = df

df = pd.concat(out, sort=True).reset_index(level=[0,2], drop=True)
print (df)
                                       Date1                    Date2  \
2019-08-21 09:05:49  Aug 21, 2017 9:09:51 AM  Aug 21, 2017 9:09:54 AM   
2019-08-21 09:05:49  Aug 21, 2017 9:09:57 AM  Aug 21, 2017 9:09:59 AM   
2019-08-21 09:05:59  Aug 21, 2017 9:10:01 AM  Aug 21, 2017 9:11:54 AM   
2019-08-21 09:05:59  Aug 21, 2017 9:19:57 AM  Aug 21, 2017 9:19:59 AM   
2019-08-21 09:05:59  Aug 21, 2017 9:20:01 AM  Aug 21, 2017 9:21:54 AM   
2019-08-21 09:05:59                      NaN                      NaN   

                           Id  num_del  num_ins    time  
2019-08-21 09:05:49  d5e665e5      0.0    108.0   356.0  
2019-08-21 09:05:49  d5e665e5      5.0    218.0   166.0  
2019-08-21 09:05:59  d5e665e5     72.0    348.0  3356.0  
2019-08-21 09:05:59  d5e665e5      5.0     69.0   125.0  
2019-08-21 09:05:59  f9e775f9      0.0    470.0   290.0  
2019-08-21 09:05:59       NaN      NaN      NaN     NaN  

这篇关于如何将字典列表拆分为多个保留相同索引的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！