将字节数据转换为数据

Question

我有以下数据：

{"links":[{"rel":"self","href":"https://api.pjm.com"},
{"rel":"next","href":"https://api.pjm.com"},{"rel":"metadata","href":"https://api.pjm.com/api/v1/ftr_cong_lmp/metadata"}],
"items":[{"effective_day":"2020-12-01T00:00:00","terminate_day":"2020-12-31T00:00:00","pnode_name":"02AMSTED138 KV  TR2","offpeak_clmp":-0.290000,"onpeak_clmp":-0.240000,"24hour_clmp":-0.270000,"lt_sim_offpeak_clmp":-0.240000,"lt_sim_onpeak_clmp":-0.220000,"lt_sim_clmp":-0.240000},{"effective_day":"2020-12-01T00:00:00","terminate_day":"2020-12-31T00:00:00","pnode_name":"02AMSTED138 KV  TR6","offpeak_clmp":-0.290000,"onpeak_clmp":-0.240000,"24hour_clmp":-0.270000,"lt_sim_offpeak_clmp":-0.240000,"lt_sim_onpeak_clmp":-0.220000,"lt_sim_clmp":-0.240000},{"effective_day":"2020-12-01T00:00:00","terminate_day":"2020-12-31T00:00:00","pnode_name":"02CPP_NH138 KV  TR2","offpeak_clmp":0.010000,"onpeak_clmp":1.530000,"24hour_clmp":0.660000,"lt_sim_offpeak_clmp":0.010000,"lt_sim_onpeak_clmp":1.520000,"lt_sim_clmp":0.660000}],"searchSpecification":{"rowCount":25,"sort":"terminate_day","order":"Desc","startRow":1,"isActiveMetadata":true,"fields":["24hour_clmp","effective_day","lt_sim_clmp","lt_sim_offpeak_clmp","lt_sim_onpeak_clmp","offpeak_clmp","onpeak_clmp","pnode_name","terminate_day"],"filters":[{"effective_day":"2020-01-01T00:00:00.0000000 to 2020-12-31T23:59:59.0000000"}]},"totalRows":163378}'

我正试图将上述数据输入数据，因此我正在尝试以下操作：

from io import StringIO    
s=str(bytes_data,'utf-8')    
data = StringIO(s)     
df=pd.read_csv(data)

但是它为我提供了列中包含整个数据的空数据。

编辑：

资料载于此：

{"effective_day":"2020-12-01T00:00:00","terminate_day":"2020-12-31T00:00:00","pnode_name":"02AMSTED138 KV  TR2","offpeak_clmp":-0.290000,"onpeak_clmp":-0.240000,"24hour_clmp":-0.270000,"lt_sim_offpeak_clmp":-0.240000,"lt_sim_onpeak_clmp":-0.220000,"lt_sim_clmp":-0.240000}

也就是说，我试图将上面的列作为上述字典的键放在一个数据中，但是如何从我的原始数据中只提取出这些项，以便将其放入dataframe。

Answer 1

您可以在字典中对字符串数据进行评估，并使用它创建一个dataframe：

pd.DataFrame(eval(s)['items'])

在需要定义表达式中使用的true值之前，例如通过true = True。

结果：

         effective_day        terminate_day  ... lt_sim_onpeak_clmp  lt_sim_clmp
0  2020-12-01T00:00:00  2020-12-31T00:00:00  ...              -0.22        -0.24
1  2020-12-01T00:00:00  2020-12-31T00:00:00  ...              -0.22        -0.24
2  2020-12-01T00:00:00  2020-12-31T00:00:00  ...               1.52         0.66

但是，出于安全考虑，建议使用ast.literal_eval而不是eval。在本例中，true的变量定义不起作用，因此需要在字符串中手动替换它：

import ast
pd.DataFrame(ast.literal_eval(s.replace('true','True'))['items'])