如何将PostgreSQL中{year1: value}格式数据在Pandas中按列输出？

先明确数据存储类型 #

PostgreSQL里的{year1: value, year2: value}格式，大概率是JSON/JSONB类型字段或者类字典格式的字符串字段，两种场景的处理方式不同：

场景1：字段为JSON/JSONB类型 #

方式1：在PostgreSQL查询时直接展开 #

用PostgreSQL内置的json_each_text函数，查询结果直接是两列结构，导入Pandas即可使用：

SELECT 
    elem.key AS year,
    elem.value AS value
FROM your_table,
     json_each_text(your_json_column) AS elem;

方式2：导入Pandas后再处理 #

如果已经把数据导入Pandas（此时JSON列是字典格式），可以用apply+stack展开：

import pandas as pd

# 假设DataFrame名为df，目标列名为json_col
expanded = df['json_col'].apply(pd.Series).stack().reset_index()
expanded.columns = ['原索引', 'year', 'value']
# 提取需要的两列
result = expanded[['year', 'value']]

场景2：字段为类字典格式的字符串 #

这种情况需要先把字符串转换成合法JSON（原格式的键没有双引号，不符合JSON规范），再展开：

方式1：在PostgreSQL中预处理 #

通过字符串替换补全引号，转成JSON后展开：

SELECT 
    elem.key AS year,
    elem.value AS value
FROM your_table,
     json_each_text(
         replace(replace(your_string_col, '{', '{"'), ':', '":')::json
     ) AS elem;

方式2：导入Pandas后处理 #

用正则补全引号，转成字典再展开：

import pandas as pd
import json
import re

# 修复类字典字符串为合法JSON格式
def fix_str_dict(s):
    return re.sub(r'(\w+):', r'"\1":', s)

df['fixed_json'] = df['string_col'].apply(fix_str_dict)
df['data_dict'] = df['fixed_json'].apply(json.loads)

# 展开为目标表格
expanded = df['data_dict'].apply(pd.Series).stack().reset_index()
expanded.columns = ['原索引', 'year', 'value']
result = expanded[['year', 'value']]

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