將時間序列熊貓資料框轉換為嵌套JSON

我有以下資料框“

創建資料框的代碼：

df = pd.DataFrame( {'month_i': {0: '2022-01-31', 1: '2022-02-28', 2: '2022-03-31', 3: '2022-04-30', 4: '2022-01-31', 5: '2022-02-28', 6: '2022-03-31', 7: '2022-04-30'}, 'id': {0: 'ACT', 1: 'ACT', 2: 'ACT', 3: 'ACT', 4: 'ACT', 5: 'ACT', 6: 'ACT', 7: 'ACT'}, 'city_name': {0: 'New York', 1: 'New York', 2: 'New York', 3: 'New York', 4: 'New York', 5: 'New York', 6: 'New York', 7: 'New York'}, 'lineColor': {0: '#4F63E7', 1: '#4F63E7', 2: '#4F63E7', 3: '#4F63E7', 4: '#4F63E7', 5: '#4F63E7', 6: '#4F63E7', 7: '#4F63E7'}, 'ptype': {0: 'house', 1: 'house', 2: 'house', 3: 'house', 4: 'unit', 5: 'unit', 6: 'unit', 7: 'unit'}, 'som': {0: 171.0, 1: 483.0, 2: 478.0, 3: 465.0, 4: 107.0, 5: 250.0, 6: 268.0, 7: 248.0}, 'dom': {0: 25, 1: 30, 2: 24, 3: 24, 4: 53, 5: 51, 6: 48, 7: 37}} )
df['month_i'] = pd.to_datetime(df['month_i'])

需要以這種格式呈現為 JSON

{
  "house": {
    "som": [
      171,
      483,
      478,
      465
    ],
    "dom": [
      25,
      30,
      24,
      24
    ]
  },
  "unit": {
    "som": [
      107,
      250,
      268,
      248
    ],
    "dom": [
      53,
      51,
      48,
      37
    ]
  },
  "ref": {
    "months": [
      "2022-01-31",
      "2022-02-28",
      "2022-03-31",
      "2022-04-30"
    ],
    "lineColor": "#4F63E7",
    "city_name": "New York"
  }
}

我希望找到一個解決方案，可以在 pandas 中使用 dict 或嵌套回圈生成 json。但只能按 ptype 分組。任何幫助將不勝感激！

PS> lineColor 可以硬編碼為“#4F63E7”，不需要從資料幀中提取

uj5u.com熱心網友回復：

您可以嘗試創建兩個不同的字典：一個包含主要資料，另一個包含參考資料，然后將它們合并：

import json 

#pivot the dataframe with the main data
pivoted = df.pivot("month_i","ptype",["som","dom"]).T

#groupby and create the first dictionary in the necessary format
data = pivoted.groupby(level=1).apply(lambda x: x.droplevel(1).apply(list,axis=1).to_dict()).to_dict()

#create the second dictionary with the reference data
other = {"ref": {"months": list(pivoted.columns), 
                 "lineColor": df["lineColor"].iat[0], 
                 "city_name":df["city_name"].iat[0]}}

#merge the dictionaries
merged = data|other

#convert to json
output = json.dumps(merged)

>>> output
{
  "house": {
    "som": [
      171,
      483,
      478,
      465
    ],
    "dom": [
      25,
      30,
      24,
      24
    ]
  },
  "unit": {
    "som": [
      107,
      250,
      268,
      248
    ],
    "dom": [
      53,
      51,
      48,
      37
    ]
  },
  "ref": {
    "months": [
      "2022-01-31",
      "2022-02-28",
      "2022-03-31",
      "2022-04-30"
    ],
    "lineColor": "#4F63E7",
    "city_name": "New York"
  }
}

uj5u.com熱心網友回復：

另一種方法：

_dict = {}
m_key = []
s_key = ['som', 'dom']

for value in df['ptype'].unique():
    frame = df[df['ptype'] == value]
    m_key.append(value)

for m in m_key:
    _dict[m] = {}
    for s in s_key:
        _dict[m][s] = []
        for ptype in df['ptype'].unique():
            frame = df[df['ptype'] == ptype]
            _dict[m][s].append(frame['som'].to_list())
            _dict[m][s].append(frame['dom'].to_list())
            break

_dict['ref'] = {}
_dict['ref']['months'] = df['month_i'].unique().tolist()
_dict['ref']['lineColor'] = '#4F63E7'

print(json.dumps(_dict))

uj5u.com熱心網友回復：

您可以按照以下四個步驟進行操作：

• 更改您的列名（如果需要，您可以跳過它）
• 內爆 ['ptype', 'som', 'dom'] 資料幀子集（通過 som 和 dom 內爆）
• 內爆 ['months', 'lineColor', 'city_name'] 資料框子集（通過月內爆）
• 創建一個組合的json

這是代碼：

# rename the column in your df
df.rename(columns = {'month_i':'months'}, inplace=True)

# implode "som" and "dom" infos wrt "ptype"
ptp = df[['ptype', 'som', 'dom']]                                              \
        .groupby(['ptype'])                                                    \
        .agg({                                                                 \
            'som': lambda x: x.tolist(),                                       \
            'dom': lambda x: x.tolist()                                        \
        }).T

# implode "months" infos wrt "lineColor" and "city_name"
lnC = df[['months', 'lineColor', 'city_name']]                                 \
        .groupby(['lineColor', 'city_name'])                                   \
        .agg({                                                                 \
            'months': lambda x: list(map(lambda d: str(d)[:-9], x.tolist()))   \
        })                                                                     \
        .reset_index().T                                                       \
        .rename(columns = {0: 'ref'})                                          

# combine the two jsons
your_json = ptp.to_json(double_precision=0, indent=4)   lnC.to_json(indent=4)

print(your_json)

標籤：Python json 熊猫 嵌套的

上一篇：我可以將“GitHubDesktop”中的vscodeIDE更改為EclipseIDE

下一篇：MicrosoftGRAPH查詢使用端點/deviceManagement/deviceHealthScripts列出了一種不熟悉的檢測腳本內容格式