从深度嵌套的 JSON 创建 Pandas DataFrame
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21494030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a Pandas DataFrame from deeply nested JSON
提问by idclark
I'm trying to create a single Pandas DataFrame object from a deeply nested JSON string.
我正在尝试从深度嵌套的 JSON 字符串创建单个 Pandas DataFrame 对象。
The JSON schema is:
JSON 架构是：
{"intervals": [
{
pivots: "Jane Smith",
"series": [
{
"interval_id": 0,
"p_value": 1
},
{
"interval_id": 1,
"p_value": 1.1162791357932633e-8
},
{
"interval_id": 2,
"p_value": 0.0000028675012051504467
}
],
},
{
"pivots": "Bob Smith",
"series": [
{
"interval_id": 0,
"p_value": 1
},
{
"interval_id": 1,
"p_value": 1.1162791357932633e-8
},
{
"interval_id": 2,
"p_value": 0.0000028675012051504467
}
]
}
]
}
Desired OutcomeI need to flatten this to produce a table:
期望的结果我需要将其展平以生成表格：
Actor Interval_id Interval_id Interval_id ...
Jane Smith 1 1.1162 0.00000 ...
Bob Smith 1 1.1162 0.00000 ...
The first column is the Pivotsvalues, and the remaining columns are the values of the keys interval_idand p_valuestored in the list series.
第一列是Pivots值，其余列是键的值interval_id并p_value存储在列表中series。
So far i've got
到目前为止我有
import requests as r
import pandas as pd
actor_data = r.get("url/to/data").json['data']['intervals']
df = pd.DataFrame(actor_data)
actor_datais a list where the length is equal to the number of individuals ie pivots.values(). The df object simply returns
actor_data是一个长度等于个体数量的列表，即pivots.values()。df 对象只是返回
<bound method DataFrame.describe of pivots Series
0 Jane Smith [{u'p_value': 1.0, u'interval_id': 0}, {u'p_va...
1 Bob Smith [{u'p_value': 1.0, u'interval_id': 0}, {u'p_va...
.
.
.
How can I iterate through that serieslist to get to the dict values and create N distinct columns? Should I try to create a DataFrame for the serieslist, reshape it,and then do a column bind with the actor names?
如何遍历该series列表以获取 dict 值并创建 N 个不同的列？我应该尝试为series列表创建一个 DataFrame，对其进行整形，然后使用演员姓名进行列绑定吗？
UPDATE:
更新：
pvalue_list = [i['p_value'] for i in json_data['series']]
this gives me a list of lists. Now I need to figure out how to add each list as a row in a DataFrame.
这给了我一个列表列表。现在我需要弄清楚如何将每个列表添加为 DataFrame 中的一行。
value_list = []
for i in pvalue_list:
pvs = [j['p_value'] for j in i]
value_list = value_list.append(pvs)
return value_list
This returns a NoneType
这将返回一个 NoneType
Solution
解决方案
def get_hypthesis_data():
raw_data = r.get("/url/to/data").json()['data']
actor_dict = {}
for actor_series in raw_data['intervals']:
actor = actor_series['pivots']
p_values = []
for interval in actor_series['series']:
p_values.append(interval['p_value'])
actor_dict[actor] = p_values
return pd.DataFrame(actor_dict).T
This returns the correct DataFrame. I transposed it so the individuals were rows and not columns.
这将返回正确的 DataFrame。我调换了它，所以个人是行而不是列。
采纳答案by Phillip Cloud
I think organizing your data in way that yields repeating column names is only going to create headaches for you later on down the road. A better approach IMHO is to create a column for each of pivots, interval_id, and p_value. This will make extremely easy to query your data after loading it into pandas.
我认为以产生重复列名的方式组织您的数据只会让您以后头疼。更好的方法是恕我直言创造每一个列pivots，interval_id和p_value。在将数据加载到 Pandas 后，这将使查询数据变得非常容易。
Also, your JSON has some errors in it. I ran it through thisto find the errors.
此外，您的 JSON 中有一些错误。我通过它来查找错误。
jqhelps here
jq在这里有帮助
import sh
jq = sh.jq.bake('-M') # disable colorizing
json_data = "from above"
rule = """[{pivots: .intervals[].pivots,
interval_id: .intervals[].series[].interval_id,
p_value: .intervals[].series[].p_value}]"""
out = jq(rule, _in=json_data).stdout
res = pd.DataFrame(json.loads(out))
This will yield output similar to
这将产生类似于
interval_id p_value pivots
32 2 2.867501e-06 Jane Smith
33 2 1.000000e+00 Jane Smith
34 2 1.116279e-08 Jane Smith
35 2 2.867501e-06 Jane Smith
36 0 1.000000e+00 Bob Smith
37 0 1.116279e-08 Bob Smith
38 0 2.867501e-06 Bob Smith
39 0 1.000000e+00 Bob Smith
40 0 1.116279e-08 Bob Smith
41 0 2.867501e-06 Bob Smith
42 1 1.000000e+00 Bob Smith
43 1 1.116279e-08 Bob Smith
Adapted from this comment
改编自此评论
Of course, you can always call res.drop_duplicates()to remove the duplicate rows. This gives
当然，您可以随时调用res.drop_duplicates()以删除重复的行。这给
In [175]: res.drop_duplicates()
Out[175]:
interval_id p_value pivots
0 0 1.000000e+00 Jane Smith
1 0 1.116279e-08 Jane Smith
2 0 2.867501e-06 Jane Smith
6 1 1.000000e+00 Jane Smith
7 1 1.116279e-08 Jane Smith
8 1 2.867501e-06 Jane Smith
12 2 1.000000e+00 Jane Smith
13 2 1.116279e-08 Jane Smith
14 2 2.867501e-06 Jane Smith
36 0 1.000000e+00 Bob Smith
37 0 1.116279e-08 Bob Smith
38 0 2.867501e-06 Bob Smith
42 1 1.000000e+00 Bob Smith
43 1 1.116279e-08 Bob Smith
44 1 2.867501e-06 Bob Smith
48 2 1.000000e+00 Bob Smith
49 2 1.116279e-08 Bob Smith
50 2 2.867501e-06 Bob Smith
[18 rows x 3 columns]
