如何将带有一些列作为JSON的Pandas数据框扁平化？

Question

如何将带有一些列作为JSON的Pandas数据框扁平化？

71

我有一个数据框df，从数据库加载数据。大部分列是JSON字符串，而有些甚至是JSON列表。例如：

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字符串中的元素数量并不相同。

我需要做的是保留普通列（如id和name），并展开json列，如下所示:

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 存在问题。正确的做法是什么？

- sfactor

B列中的值怎么办？您也想展开字典吗？ - MMF

是的，它们也需要被展平。在原始问题中我把所有被展平的列都写成了columnA，这是一个打字错误，但现在已经更正了。 - sfactor

4个回答

55

最快的似乎是：

import pandas as pd
import json

json_struct = json.loads(df.to_json(orient="records"))    
df_flat = pd.io.json.json_normalize(json_struct) #use pd.io.json

- staonas

2

这绝对是最简单的方法，也是适合我的方法。唯一需要注意的是，您的嵌套对象将以长名称结束（data.level1.level2.level3 ...等等）。 - TheTiGuR

这绝对是我选择的答案——完美地解决了问题，而且非常简洁。谢谢！ - Atlas7

这是最好的答案！ - Srivatsan

2

得拿到一些 orient="records 和 pd.io.json.json_normalize。现在把这些东西塞进长期记忆的脑细胞里。 - WestCoastProjects

25

简而言之 复制粘贴以下函数并像这样使用：flatten_nested_json_df(df)

这是我能想到的最通用的函数：

def flatten_nested_json_df(df):
    
    df = df.reset_index()
    
    print(f"original shape: {df.shape}")
    print(f"original columns: {df.columns}")
    
    
    # search for columns to explode/flatten
    s = (df.applymap(type) == list).all()
    list_columns = s[s].index.tolist()
    
    s = (df.applymap(type) == dict).all()
    dict_columns = s[s].index.tolist()
    
    print(f"lists: {list_columns}, dicts: {dict_columns}")
    while len(list_columns) > 0 or len(dict_columns) > 0:
        new_columns = []
        
        for col in dict_columns:
            print(f"flattening: {col}")
            # explode dictionaries horizontally, adding new columns
            horiz_exploded = pd.json_normalize(df[col]).add_prefix(f'{col}.')
            horiz_exploded.index = df.index
            df = pd.concat([df, horiz_exploded], axis=1).drop(columns=[col])
            new_columns.extend(horiz_exploded.columns) # inplace
        
        for col in list_columns:
            print(f"exploding: {col}")
            # explode lists vertically, adding new columns
            df = df.drop(columns=[col]).join(df[col].explode().to_frame())
            # Prevent combinatorial explosion when multiple
            # cols have lists or lists of lists
            df = df.reset_index(drop=True)
            new_columns.append(col)
        
        # check if there are still dict o list fields to flatten
        s = (df[new_columns].applymap(type) == list).all()
        list_columns = s[s].index.tolist()

        s = (df[new_columns].applymap(type) == dict).all()
        dict_columns = s[s].index.tolist()
        
        print(f"lists: {list_columns}, dicts: {dict_columns}")
        
    print(f"final shape: {df.shape}")
    print(f"final columns: {df.columns}")
    return df

它接受一个数据框，该数据框的列可能包含嵌套列表和/或字典，并递归地展开/扁平化这些列。

它使用 pandas 的 pd.json_normalize 来展开字典（创建新列），并使用 pandas 的 explode 来展开列表（创建新行）。

使用简单：

# Test
df = pd.DataFrame(
    columns=['id','name','columnA','columnB'],
    data=[
        [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"}]]
    ])

flatten_nested_json_df(df)

这不是地球上最有效的事情，它会重置您数据框的索引，但它可以完成工作。随意调整它。

- Michele Piccolini

3

这绝对是我长期以来见过的最佳解决方案！干得好！ - Serge de Gosson de Varennes

嗨，这很有帮助，但似乎无法保存新的数据框。 - Cameron Stewart

@CameronStewart 保存在哪里？ - Michele Piccolini

它给我一个错误

msg.format(req_len=len(left.columns), given_len=len(right)) ValueError: 无法强制转换为Series，长度必须为44：给定1

- Atharv Thakur

这是一个很好的解决方案，但我发现一个问题，即当多记录JSON文件中的某个键值（对于任何一个记录）为空时。当发生这种情况时，类型函数不会返回正确的dict/list类型，导致该列被忽略。不确定类型函数是否有办法在这种情况下考虑到空值。 - KahlilG

显示剩余2条评论

16

创建一个自定义函数来展平columnB，然后使用pd.concat。

def flatten(js):
    return pd.DataFrame(js).set_index('pos').squeeze()

pd.concat([df.drop(['columnA', 'columnB'], axis=1),
           df.columnA.apply(pd.Series),
           df.columnB.apply(flatten)], axis=1)

- piRSquared

这是我在一个带有B类型列的情况下找到的唯一有效解决方案。看看代码的优雅！只需一行。 - Diego

这是最清晰的答案。也是最容易理解的。不过还需要一个文档字符串。 - undefined

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nickil Maveli · Accepted Answer

下面是使用json_normalize()的解决方案，通过使用自定义函数将数据格式转换为json_normalize函数所理解的正确格式。

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 在相同的索引上进行连接，得到：

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

编辑：根据@MartijnPieters的评论，建议解码JSON字符串的推荐方法是使用json.loads()，与使用ast.literal_eval()相比速度要快得多，如果您知道数据源是JSON。