我有大量的 json 數(shù)據(jù)被讀入 python 數(shù)據(jù)幀，并為每行創(chuàng)建一個字典列表。我需要將其轉(zhuǎn)換為不同格式的數(shù)據(jù)。數(shù)據(jù)格式如下：{ "data": [{ "item": [{ "value": 0, "type": "a" }, { "value": 0, "type": "b" }, { "value": 70, "type": "c" } ], "timestamp": "2019-01-12T04:52:06.669Z" }, { "item": [{ "value": 30, "type": "a" }, { "value": 0, "type": "b" } ], "timestamp": "2019-01-12T04:53:06.669z" } ]}將數(shù)據(jù)轉(zhuǎn)換為以下形式的數(shù)據(jù)幀的最有效方法是什么：時間戳-----------------------------a-------b------c2019-01-12T04:52:06.669Z------0-------0------702019-01-12T04:53:06.669Z------30- -----0------0到目前為止，我已經(jīng)設(shè)法使用 for 循環(huán)來做到這一點，但它非常低效且緩慢。到目前為止我所擁有的是這個。with open('try.json') as f: data = json.load(f)df_data = pandas.DataFrame(data['data'])df_formatted = pandas.DataFrame(columns=['a','b','c'])for d, timestamp in zip(df_data['item'], df_data['timestamp']): row = dict() for entry in d: category = entry['type'] value = entry['value'] row[category] = value row['timestamp'] = timestamp df_formatted = df_formatted.append(row, ignore_index=True)df = df_formatted.fillna(0)列表中的項目數(shù)通常為數(shù)千。有關(guān)如何高效執(zhí)行此操作的任何指示或示例？
2 回答
holdtom
TA貢獻1805條經(jīng)驗 獲得超10個贊
您可以通過迭代對象來解壓嵌套的 json 對象。嘗試
import pandas as pd
a=[
{
"item": [
{
"value": 0,
"type": "a"
},
{
"value": 0,
"type": "b"
},
{
"value": 70,
"type": "c"
},
],
"timestamp": "2019-01-12T04:52:06.669Z"
},
{
"item": [
{
"value": 30,
"type": "a"
},
{
"value": 0,
"type": "b"
}
],
"timestamp": "2019-01-12T04:53:06.669z"
}
]
cols = ['value', 'type', 'timestamp']
rows = []
for data in a:
data_row = data['item']
timestamp = data['timestamp']
for row in data_row:
row['timestamp']=timestamp
rows.append(row)
df = pd.DataFrame(rows)
df =df.pivot_table(index='timestamp',columns=['type'],values=['value']).reset_index()
df.columns=['timestamp','a','b','c']
如果您正在尋找緊湊的解決方案，請使用json_normalize
from pandas.io.json import json_normalize
df =pd.DataFrame()
for i in range(len(a)):
df =pd.concat([df,json_normalize(a[i]['item'])])
df =df.pivot_table(index='timestamp',columns=['type'],values=['value']).reset_index()
df.columns=['timestamp','a','b','c']
最終輸出
timestamp a b c
2019-01-12T04:52:06.669Z 0.0 0.0 70.0
2019-01-12T04:53:06.669z 30.0 0.0 NaN
素胚勾勒不出你
TA貢獻1827條經(jīng)驗 獲得超9個贊
您可以從 json 中提取字典列表并將其提供給數(shù)據(jù)幀。代碼可以是：
df = pd.DataFrame([dict([('timestamp', d['timestamp']), ('a', 0),
('b', 0), ('c', 0)]
+ [(item['type'], item['value'])
for item in d['item']])for d in data['data']],
columns=['timestamp', 'a', 'b', 'c'])
print(df)
按預(yù)期輸出：
timestamp a b c
0 2019-01-12T04:52:06.669Z 0 0 70
1 2019-01-12T04:53:06.669z 30 0 0
這里的技巧是首先構(gòu)建一個具有默認值的對列表，然后在從中構(gòu)建字典之前用實際值擴展它。由于保留了最后看到的值，您實際上構(gòu)建了一個包含所有相關(guān)值的字典。
columns 參數(shù)僅用于確保列的預(yù)期順序。
添加回答
舉報
0/150
提交
取消