代码之家 › 专栏 › 技术社区 › frank

将字典列表中的列转换为单独的列

pandas python

frank · 技术社区 · 2 年前

我有:

grp = ["A","B","C","A","C","C","B"]
dictl = ["[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:30.957'}]",
"[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:30.957'}]",
"[]","[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:31.504'}]",
"[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:31.504'}]",
"[]","[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:31.504'}]"]
df = pd.DataFrame({'grp':grp,'dictl':dictl})

我想将其转换为:

pd.DataFrame({'grp':["A","B","C","A","C","C","B"],
              'TypeID':["0","0","","0","0","","0"],
              'Description':["blah","blah","","blah","blah","","blah"],
              'DateCreated':["2018-08-09T14:00:30.957","2018-08-09T14:00:30.957","","2018-08-09T14:00:31.504","2018-08-09T14:00:31.504","","2018-08-09T14:00:31.504"]})

我尝试了来自的建议 Change a column containing list of dict to columns in a DataFrame ,并存在以下问题:

for grp, dictl in df:
    rec = {'Name': grp}
    rec.update(x for d in dictl for x in d.items())
    records.append(rec)

错误: ValueError: too many values to unpack (expected 2)

和

df['dictl'].apply(lambda c:
                                  pd.Series({next(iter(x.keys())).strip(':'):
                                             next(iter(x.values())) for x in c})
                                  )

给出错误: AttributeError: 'str' object has no attribute 'keys'

我有>2m行,所以如果可能的话,希望这种方法快速

2 回复 | 直到 2 年前

9769953 2 年前

根据字符串输入,您可以执行以下操作:

groups = ["A","B","C","A","C","C","B"]
strings = ["[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:30.957'}]",
"[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:30.957'}]",
"[]","[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:31.504'}]",
"[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:31.504'}]",
"[]","[{'TypeID': 0, 'Description': 'blah', 'DateCreated': '2018-08-09T14:00:31.504'}]"]

import ast

dictl = [ast.literal_eval(string) for string in strings]

# In case of missing data
defaults = {'TypeID': 0, 'Description': '', 'DateCreated': ''}

# Some fancy list comprehension-ternary if-dict creation, because why not?
dictl_grp = [{**item[0], 'grp': group} if len(item)
             else {'grp': group, **defaults} 
             for group, item in zip(groups, dictl)]

import pandas as pd

df = pd.DataFrame.from_records(dictl_grp)
print(df)

产生

   TypeID Description              DateCreated grp
0       0        blah  2018-08-09T14:00:30.957   A
1       0        blah  2018-08-09T14:00:30.957   B
2       0                                        C
3       0        blah  2018-08-09T14:00:31.504   A
4       0        blah  2018-08-09T14:00:31.504   C
5       0                                        C
6       0        blah  2018-08-09T14:00:31.504   B

(为了清楚起见,我重命名了几个变量。)

treuss 2 年前

如果要转换数据,在将其加载到数据帧之前,一种方法是首先从第一个非空项中提取密钥

>>> keys = [k for k in eval(next(i for i in dictl if i != "[]"))[0]]
>>> keys
['TypeID', 'Description', 'DateCreated']

然后通过从记录中读取它们来创建您的新dict:

>>> newdict = {key: [eval(it)[0].get(key) if it != "[]" else "" for it in dictl] for key in keys}
>>> newdict
{'TypeID': [0, 0, '', 0, 0, '', 0], 'Description': ['blah', 'blah', '', 'blah', 'blah', '', 'blah'], 'DateCreated': ['2018-08-09T14:00:30.957', '2018-08-09T14:00:30.957', '', '2018-08-09T14:00:31.504', '2018-08-09T14:00:31.504', '', '2018-08-09T14:00:31.504']}

注意,我正在使用 eval 这里为了简单起见, ast.literal_eval 通常是更安全的选择。