时间戳

首页 > 解决方案 > 如何使用这样的标题组织我的数据框？

python - 如何使用这样的标题组织我的数据框？

问题描述

我的程序返回给我一个有好几层的字典。在 Excel 中绘图，它看起来像这样：

Excel模板： Excel 模板

代码返回这个字典（它已经被组织起来帮助你可视化）：

{'Parity': 'BITCOIN / TETHERUS', '1m': {'Datas': {'Price': {'Initial Price': '58239.43', 'Final Price': '58236.50'}, 'Oscillators': {'Buy': 1, 'Neutral': 8, 'Sell': 2}, 'MovingAverages': {'Buy': 6, 'Neutral': 1, 'Sell': 8}}}, '5m': {'Datas': {'Price': {'Initial Price': '58239.43', 'Final Price': '58231.56'}, 'Oscillators': {'Buy': 0, 'Neutral': 9, 'Sell': 2}, 'MovingAverages': {'Buy': 13, 'Neutral': 1, 'Sell': 1}}}, '15m': {'Datas': {'Price': {'Initial Price': '58239.43', 'Final Price': '58231.56'}, 'Oscillators': {'Buy': 1, 'Neutral': 6, 'Sell': 4}, 'MovingAverages': {'Buy': 13, 'Neutral': 1, 'Sell': 1}}}, '1h': {'Datas': {'Price': {'Initial Price': '58239.43', 'Final Price': '58231.55'}, 'Oscillators': {'Buy': 2, 'Neutral': 7, 'Sell': 2}, 'MovingAverages': {'Buy': 14, 'Neutral': 1, 'Sell': 0}}}, '4h': {'Datas': {'Price': {'Initial Price': '58239.43', 'Final Price':'58231.56'}, 'Oscillators': {'Buy': 3, 'Neutral': 8, 'Sell': 0}, 'MovingAverages': {'Buy': 14, 'Neutral': 1, 'Sell': 0}}}, '1D': {'Datas': {'Price': {'Initial Price': '58239.43', 'Final Price': '58231.56'}, 'Oscillators': {'Buy': 2, 'Neutra
l': 9, 'Sell': 0}, 'MovingAverages': {'Buy': 14, 'Neutral': 1, 'Sell': 0}}}, '1W': {'Datas': {'Price': {'Initial Price': '58239.43', 'Final Price': '58230.02'}, 'Oscillators': {'Buy': 1, 'Neutral': 9, 'Sell': 1}, 'MovingAverages': {'Buy': 12, 'Neutral': 3, 'Sell': 0}}}}

我想在xlsx文件中保留一个数据框，像上面那样组织。

标签： pythonexcelpandasdataframe

解决方案

只需两步

  1. pd.json_normalize()从 JSON 生成数据框
  2. 使用点分法将列重组为多索引pd.MultiIndex.from_tuples()
js = {"Parity": "BITCOIN / TETHERUS", "1m": {"Datas": {"Price": {"Initial Price": "58239.43", "Final Price": "58236.50"}, "Oscillators": {"Buy": 1, "Neutral": 8, "Sell": 2}, "MovingAverages": {"Buy": 6, "Neutral": 1, "Sell": 8}}}, "5m": {"Datas": {"Price": {"Initial Price": "58239.43", "Final Price": "58231.56"}, "Oscillators": {"Buy": 0, "Neutral": 9, "Sell": 2}, "MovingAverages": {"Buy": 13, "Neutral": 1, "Sell": 1}}}, "15m": {"Datas": {"Price": {"Initial Price": "58239.43", "Final Price": "58231.56"}, "Oscillators": {"Buy": 1, "Neutral": 6, "Sell": 4}, "MovingAverages": {"Buy": 13, "Neutral": 1, "Sell": 1}}}, "1h": {"Datas": {"Price": {"Initial Price": "58239.43", "Final Price": "58231.55"}, "Oscillators": {"Buy": 2, "Neutral": 7, "Sell": 2}, "MovingAverages": {"Buy": 14, "Neutral": 1, "Sell": 0}}}, "4h": {"Datas": {"Price": {"Initial Price": "58239.43", "Final Price": "58231.56"}, "Oscillators": {"Buy": 3, "Neutral": 8, "Sell": 0}, "MovingAverages": {"Buy": 14, "Neutral": 1, "Sell": 0}}}, "1D": {"Datas": {"Price": {"Initial Price": "58239.43", "Final Price": "58231.56"}, "Oscillators": {"Buy": 2, "Neutral": 9, "Sell": 0}, "MovingAverages": {"Buy": 14, "Neutral": 1, "Sell": 0}}}, "1W": {"Datas": {"Price": {"Initial Price": "58239.43", "Final Price": "58230.02"}, "Oscillators": {"Buy": 1, "Neutral": 9, "Sell": 1}, "MovingAverages": {"Buy": 12, "Neutral": 3, "Sell": 0}}}}

df = pd.json_normalize(js).set_index("Parity")
df.columns=pd.MultiIndex.from_tuples([tuple(c.split(".")) for c in df.columns], names=["tenor","datas","type","measure"])
df = df.droplevel("datas",1)

样本输出

tenor                         1m                                                                             5m                                                                 
type                       Price             Oscillators              MovingAverages                      Price             Oscillators              MovingAverages             
measure            Initial Price Final Price         Buy Neutral Sell            Buy Neutral Sell Initial Price Final Price         Buy Neutral Sell            Buy Neutral Sell
Parity                                                                                                                                                                          
BITCOIN / TETHERUS      58239.43    58236.50           1       8    2              6       1    8      58239.43    58231.56           0       9    2             13       1    1

推荐阅读