我有以下帶有 json 列的 csv 檔案data。json 資料有點復雜，我無法按照我想要的方式展開它。我的目標是將一些資料從configuration列中獲取，并將相應的值從readings行中獲取為值。我在這里找到了一些東西，但我無法深入嵌套資料以使用針對我的資料定制的解決方案來提取 id 和值。
我擁有的資料：
system;datetime;data;
ACD;19.10.2021 11:24:00,000;{"id":"device1","type":"dataValues","payload":{"deviceId":"device1","start":"2021-10-19 11:23:00.000","end":"2021-10-19 11:24:00.000","configuration":[{"name":"DD51","id":1},{"name":"VV22","id":2}],"detectors":[{"readings":[{"reading":{"count":8}}],"id":1},{"readings":[{"reading":{"count":2}}],"id":2}]}};
ACD;19.10.2021 11:25:00,000;{"id":"device1","type":"dataValues","payload":{"deviceId":"device1","start":"2021-10-19 11:24:00.000","end":"2021-10-19 11:25:00.000","configuration":[{"name":"DD51","id":1},{"name":"VV22","id":2}],"detectors":[{"readings":[{"reading":{"count":7}}],"id":1},{"readings":[{"reading":{"count":4}}],"id":2}]}};
我想要的是：
system;datetime;data;DD51;VV22;
ACD;19.10.2021 11:24:00,000;8;2;
ACD;19.10.2021 11:25:00,000;7;4;
uj5u.com熱心網友回復：
如果your_file.csv包含：
system;datetime;data;
ACD;19.10.2021 11:24:00,000;{"id":"device1","type":"dataValues","payload":{"deviceId":"device1","start":"2021-10-19 11:23:00.000","end":"2021-10-19 11:24:00.000","configuration":[{"name":"DD51","id":1},{"name":"VV22","id":2}],"detectors":[{"readings":[{"reading":{"count":8}}],"id":1},{"readings":[{"reading":{"count":2}}],"id":2}]}};
ACD;19.10.2021 11:25:00,000;{"id":"device1","type":"dataValues","payload":{"deviceId":"device1","start":"2021-10-19 11:24:00.000","end":"2021-10-19 11:25:00.000","configuration":[{"name":"DD51","id":1},{"name":"VV22","id":2}],"detectors":[{"readings":[{"reading":{"count":7}}],"id":1},{"readings":[{"reading":{"count":4}}],"id":2}]}};
然后：
from ast import literal_eval
df = pd.read_csv("your_file.csv", sep=";")[["system", "datetime", "data"]]
df = pd.concat(
[
df,
df.pop("data")
.apply(literal_eval)
.apply(
lambda x: pd.Series(
{
c["name"]: d["readings"][0]["reading"]["count"]
for c, d in zip(
x["payload"]["configuration"], x["payload"]["detectors"]
)
}
)
),
],
axis=1,
)
印刷：
system datetime DD51 VV22
0 ACD 19.10.2021 11:24:00,000 8 2
1 ACD 19.10.2021 11:25:00,000 7 4
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/519753.html
標籤：Pythonjson熊猫
上一篇：資料被覆寫如何解決