pandas -在数据框的列内扩展嵌套的json数组 [英] Pandas - expand nested json array within column in dataframe
问题描述
我有一个json数据(来自mongodb)，其中包含数千条记录(因此是json对象的数组/列表)，每个对象的结构如下所示:
I have a json data (coming from mongodb) containing thousands of records (so an array/list of json object) with a structure like the below one for each object:
{
"id":1,
"first_name":"Mead",
"last_name":"Lantaph",
"email":"mlantaph0@opensource.org",
"gender":"Male",
"ip_address":"231.126.209.31",
"nested_array_to_expand":[
{
"property":"Quaxo",
"json_obj":{
"prop1":"Chevrolet",
"prop2":"Mercy Streets"
}
},
{
"property":"Blogpad",
"json_obj":{
"prop1":"Hyundai",
"prop2":"Flashback"
}
},
{
"property":"Yabox",
"json_obj":{
"prop1":"Nissan",
"prop2":"Welcome Mr. Marshall (Bienvenido Mister Marshall)"
}
}
]
}
当加载到数据框中时，"nested_array_to_expand"是一个包含json的字符串(我在加载过程中确实使用了"json_normalize").预期的结果是获得一个带有3行(给定上面的示例)和一个嵌套对象的新列的数据框，如下所示:
When loaded in a dataframe the "nested_array_to_expand" is a string containing the json (I do use "json_normalize" during loading). The expected result is to get a dataframe with 3 row (given the above example) and new columns for the nested objects such as below:
index email first_name gender id ip_address last_name \
0 mlantaph0@opensource.org Mead Male 1 231.126.209.31 Lantaph
1 mlantaph0@opensource.org Mead Male 1 231.126.209.31 Lantaph
2 mlantaph0@opensource.org Mead Male 1 231.126.209.31 Lantaph
test.name test.obj.ahah test.obj.buzz
0 Quaxo Mercy Streets Chevrolet
1 Blogpad Flashback Hyundai
2 Yabox Welcome Mr. Marshall (Bienvenido Mister Marshall) Nissan
我能够使用以下功能获得该结果，但是它非常慢(1k记录大约2s)，所以我想改进现有代码或寻找一种完全不同的方法来获得该结果.
I was able to get that result with the below function but it extremely slow (around 2s for 1k records) so I would like to either improve the existing code or find a completely different approach to get this result.
def expand_field(field, df, parent_id='id'):
all_sub = pd.DataFrame()
# we need an id per row to be able to merge back dataframes
# if no id, then we will create one based on index of rows
if parent_id not in df:
df[parent_id] = df.index
# go through all rows and create a new dataframe with values
for i, row in df.iterrows():
try:
sub = json_normalize(df[field].values[i])
sub = sub.add_prefix(field + '.')
sub['parent_id'] = row[parent_id]
all_sub = all_sub.append(sub)
except:
print('crash')
pass
df = pd.merge(df, all_sub, left_on=parent_id, right_on='parent_id', how='left')
#remove old columns
del df["parent_id"]
del df[field]
#return expanded dataframe
return df
非常感谢您的帮助.
=====编辑以回复评论====
===== EDIT for answering comment ====
从mongodb加载的数据是对象数组. 我用以下代码加载它:
The data loaded from mongodb is an array of object. I load it with the following code:
data = json.loads(my_json_string)
df = json_normalize(data)
输出为我提供了一个以df ["nested_array_to_expand"]作为dtype对象(字符串)的数据框
The output give me a dataframe with df["nested_array_to_expand"] as dtype object (string)
0 [{'property': 'Quaxo', 'json_obj': {'prop1': '...
Name: nested_array_to_expand, dtype: object
推荐答案
I propose an interesting answer I think using pandas.json_normalize.
I use it to expand the nested json -- maybe there is a better way, but you definitively should consider using this feature. Then you have just to rename the columns as you want.
import io
from pandas import json_normalize
# Loading the json string into a structure
json_dict = json.load(io.StringIO(json_str))
df = pd.concat([pd.DataFrame(json_dict),
json_normalize(json_dict['nested_array_to_expand'])],
axis=1).drop('nested_array_to_expand', 1)
这篇关于 pandas -在数据框的列内扩展嵌套的json数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
