输入DataFrame“df”如下(请注意'id'列中的值):
| id | name |
|-------|---------------------------------------------------------------------------------------|
| a1xy | [ { "event": "sports", "start": "100"}, { "event": "lunch", "start": "121" } ] |
| a7yz | [ { "event": "lunch", "start": "109"}, { "event": "movie", "start": "97" } ] |
| bx4y | [ { "event": "dinner", "start": "78"}, { "event": "sleep", "start": "25" } ] |
我想展平 JSON 数组元素,以便我的结果输出为:
| id | name.event | name.start |
|-------|------------|------------|
| a1xy | sports | 100 |
| a1xy | lunch | 121 |
| a7yz | lunch | 109 |
| a7yz | movie | 97 |
| bx4y | dinner | 78 |
| bx4y | sleep | 25 |
“id”列中的值需要正确映射。我如何在 Python 中执行此操作?
我试过:
k = df.name.map(json.loads).apply(pd.DataFrame).tolist()
final_df = pd.concat(k)
但我无法映射“id”列中的值。
请您参考如下方法:
您可以使用列表理解和扁平化,并通过 id 值更新每个字典,最后调用 DataFrame 构造函数:
df['name'] = df['name'].map(json.loads)
df = pd.DataFrame([dict(y, id=i) for i, x in zip(df['id'],df['name']) for y in x])
print (df)
event id start
0 sports a1xy 100
1 lunch a1xy 121
2 lunch a7yz 109
3 movie a7yz 97
4 dinner bx4y 78
5 sleep bx4y 25
但是如果输入是json,最好使用json_normalize .
时间:
df=pd.DataFrame([
['a1xy',[{ "event": "sports", "start": "100"}, { "event": "lunch", "start": "121" } ]],
['a7yz',[{ "event": "lunch", "start": "109"}, { "event": "movie", "start": "97" } ]],
['bx4y',[{ "event": "dinner", "start": "78"}, { "event": "sleep", "start": "25" } ]]],
columns=['id','name'])
print (df)
#3k rows
df = pd.concat([df] * 1000, ignore_index=True)
In [276]: %%timeit
...: pd.DataFrame([dict(y, id=i) for i, x in zip(df['id'],df['name']) for y in x])
9.49 ms ± 230 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
In [277]: %%timeit
...: finalArray=[]
...: df.apply(lambda x: addtoArray(x,finalArray),axis=1)
...: pd.DataFrame(finalArray,columns=['col1','event','start'])
...:
1.81 s ± 33.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
列表理解解决方案 180x 更快。
