如何使用某些列将JSON数据框扁平化为JSON? [英] How to flatten a pandas dataframe with some columns as json?

问题描述

我有一个数据框df，可从数据库中加载数据.大多数列是json字符串，而有些列甚至是json列表.例如:

I have a dataframe df that loads data from a database. Most of the columns are json strings while some are even list of jsons. For example:

id     name     columnA                               columnB
1     John     {"dist": "600", "time": "0:12.10"}    [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "3rd", "value": "200"}, {"pos": "total", "value": "1000"}]
2     Mike     {"dist": "600"}                       [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "total", "value": "800"}]
...

如您所见，并不是所有的行在列的json字符串中都有相同数量的元素.

As you can see, not all the rows have the same number of elements in the json strings for a column.

我需要做的是保持id和name之类的普通列不变，并像这样将json列展平:

What I need to do is keep the normal columns like id and name as it is and flatten the json columns like so:

id    name   columnA.dist   columnA.time   columnB.pos.1st   columnB.pos.2nd   columnB.pos.3rd     columnB.pos.total
1     John   600            0:12.10        500               300               200                 1000 
2     Mark   600            NaN            500               300               Nan                 800 

我尝试像这样使用json_normalize:

from pandas.io.json import json_normalize
json_normalize(df)

但是keyerror似乎有一些问题.正确的方法是什么?

But there seems to be some problems with keyerror. What is the correct way of doing this?

推荐答案

以下是使用

Here's a solution using json_normalize() again by using a custom function to get the data in the correct format understood by json_normalize function.

import ast
from pandas.io.json import json_normalize

def only_dict(d):
    '''
    Convert json string representation of dictionary to a python dict
    '''
    return ast.literal_eval(d)

def list_of_dicts(ld):
    '''
    Create a mapping of the tuples formed after 
    converting json strings of list to a python list   
    '''
    return dict([(list(d.values())[1], list(d.values())[0]) for d in ast.literal_eval(ld)])

A = json_normalize(df['columnA'].apply(only_dict).tolist()).add_prefix('columnA.')
B = json_normalize(df['columnB'].apply(list_of_dicts).tolist()).add_prefix('columnB.pos.') 

最后，在公共索引上加入DFs以获得:

Finally, join the DFs on the common index to get:

df[['id', 'name']].join([A, B])

- 根据@MartijnPieters的评论，解码json字符串的推荐方法是使用

- As per the comment by @MartijnPieters, the recommended way of decoding the json strings would be to use json.loads() which is much faster when compared to using ast.literal_eval() if you know that the data source is JSON.

这篇关于如何使用某些列将JSON数据框扁平化为JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！