如何在Django中将嵌套JSONField转换为多层索引时序DataFrame

我的Django模型包含DateTimeField和JSONField字段，想要基于这些字段创建时序DataFrame。使用django-pandas包的to_timeseries方法可实现基础转换，但该方法会将整个JSONField放入单列中，如何将此列展平为多层索引列？

models.py

class Indicator(models.Model):
    dt = models.DateTimeField(null=True)
    metrics = models.JSONField(default=dict)

JSONField字典示例：

{'housing': {'1d_percent': 73.62755998, '2d_percent': 3e-08},
 'fund-flower': {'ratio': 0.01981295},
 'mpi': {'mpi': -0.6527736158660562}}

现有转换代码及结果：

>>> qs = Indicator.objects.all()
>>> df = qs.to_timeseries(index='dt', fieldnames='metrics').sort_index().dropna()
>>> df

                                                                    metrics
dt                                                                          
2018-01-01 00:00:00+00:00  {'mpi': {'mpi': -0.01679772442974948}, 'fund-f...
2018-01-02 00:00:00+00:00  {'mpi': {'mpi': 1.1785319016689795}, 'fund-flo...
2018-01-03 00:00:00+00:00  {'mpi': {'mpi': 1.047678402830424}, 'fund-flow...
2018-01-04 00:00:00+00:00  {'mpi': {'mpi': 1.111703887319459}, 'fund-flow...
2018-01-05 00:00:00+00:00  {'mpi': {'mpi': 2.3908629334035343}, 'fund-flo...
                                                                      ...
2022-09-17 00:00:00+00:00  {'mpi': {'mpi': -1.0434999082318062}, 'fund-fl...
2022-09-18 00:00:00+00:00  {'mpi': {'mpi': -0.9680468633746766}, 'fund-fl...
2022-09-19 00:00:00+00:00  {'mpi': {'mpi': -0.9287818619840235}, 'fund-fl...
2022-09-20 00:00:00+00:00  {'mpi': {'mpi': -0.8487296227267782}, 'fund-fl...

期望输出：

mpi            fund-flower               housing
dt                          mpi            ratio                     1d_percent    2d_percent                                                                        
2018-01-01 00:00:00+00:00   value          value                     value         value
2018-01-02 00:00:00+00:00   value          value                     value         value  
2018-01-03 00:00:00+00:00   value          value                     value         value  
2018-01-04 00:00:00+00:00   value          value                     value         value  
2018-01-05 00:00:00+00:00   value          value                     value         value  
                                                                      ...
2022-09-17 00:00:00+00:00   value          value                     value         value  
2022-09-18 00:00:00+00:00   value          value                     value         value  
2022-09-19 00:00:00+00:00   value          value                     value         value  
2022-09-20 00:00:00+00:00   value          value                     value         value  

尝试json_normalize时出现错误：

>>> pd.json_normalize(df, record_path =['metrics'])
TypeError: string indices must be integers

错误原因分析 #

报错是因为df['metrics']中的内容可能被django-pandas序列化为字符串，而非Python字典，导致json_normalize无法解析；或是调用参数不符合要求。

方法一：修复django-pandas生成的DataFrame #

import pandas as pd
import json

# 1. 原查询集转DataFrame
qs = Indicator.objects.all()
df = qs.to_timeseries(index='dt', fieldnames='metrics').sort_index().dropna()

# 2. 确保metrics列是Python字典（若为字符串则解析）
df['metrics'] = df['metrics'].apply(lambda x: json.loads(x) if isinstance(x, str) else x)

# 3. 逐个规范化JSON字典并合并对齐索引
normalized_df = pd.concat([pd.json_normalize(item) for item in df['metrics']], ignore_index=False)

# 4. 将单层列名转换为多层索引
normalized_df.columns = pd.MultiIndex.from_tuples([tuple(col.split('.')) for col in normalized_df.columns])

print(normalized_df)

方法二：跳过django-pandas，直接构建DataFrame #

避免JSON字段被序列化的问题，直接从查询集提取数据：

import pandas as pd
import json

# 1. 从查询集提取数据并构建DataFrame
data = list(Indicator.objects.values('dt', 'metrics'))
df = pd.DataFrame(data).set_index('dt').sort_index().dropna()

# 2. 确保metrics是字典类型
df['metrics'] = df['metrics'].apply(lambda x: json.loads(x) if isinstance(x, str) else x)

# 3. 规范化JSON并转多层索引
normalized_df = pd.concat([pd.json_normalize(item) for item in df['metrics']], ignore_index=False)
normalized_df.columns = pd.MultiIndex.from_tuples([tuple(col.split('.')) for col in normalized_df.columns])

print(normalized_df)

内容的提问来源于stack exchange，提问作者Florent