用Pandas/Python解析一个嵌套的Json文件，json文件现在位于 Dataframe 的一列中

suzh9iv8 于 2023-02-26 发布在 Python

关注(0)|答案(1)|浏览(135)

我在一个 Dataframe 中检索到了所需的数据，其中一列包含此dict列表

[{'date': '2023-02-03T00:00:00', 'groups': [{'periodType': 'm', 
                                              'projections': [{'identifier': 'TD3BALMO', 'period': 'Feb 23', 'value': 54.621, 'validFrom': '2023-02-01', 'validTo': '2023-02-28', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3CURMON', 'period': 'Feb 23', 'value': 53.855, 'validFrom': '2023-02-01', 'validTo': '2023-02-28', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+1_M', 'period': 'Mar 23', 'value': 55.387, 'validFrom': '2023-03-01', 'validTo': '2023-03-31', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+2_M', 'period': 'Apr 23', 'value': 55.174, 'validFrom': '2023-04-01', 'validTo': '2023-04-28', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+3_M', 'period': 'May 23', 'value': 55.748, 'validFrom': '2023-05-01', 'validTo': '2023-05-31', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+4_M', 'period': 'Jun 23', 'value': 55.608, 'validFrom': '2023-06-01', 'validTo': '2023-06-30', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+5_M', 'period': 'Jul 23', 'value': 52.548, 'validFrom': '2023-07-01', 'validTo': '2023-07-31', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}]}, 
                                            {'periodType': 'q', 
                                              'projections': [{'identifier': 'TD3CURQ', 'period': 'Q1 23', 'value': 52.638, 'validFrom': '2023-01-01', 'validTo': '2023-03-31', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'},
                                                              {'identifier': 'TD3+1Q', 'period': 'Q2 23', 'value': 55.51, 'validFrom': '2023-04-01', 'validTo': '2023-06-30', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+2Q', 'period': 'Q3 23', 'value': 51.729, 'validFrom': '2023-07-01', 'validTo': '2023-09-29', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+3Q', 'period': 'Q4 23', 'value': 62.63, 'validFrom': '2023-10-01', 'validTo': '2023-12-22', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'}]
                                              }
                                            ]
  }]

什么是最简单的方法来转换为以下？（对不起，数字是不一样的，但你的想法）我尝试了json_normalize，但还没有找到一个有效的方法来转换为以下？事实上，我只需要从前3列的数据：标识符、周期、值

identifier  period   value  ...     validTo nextRolloverDate archiveDate
0      TD3BALMO  Feb 23  68.464  ...  2023-02-28       2023-02-28  2023-02-21
1     TD3CURMON  Feb 23  60.955  ...  2023-02-28       2023-02-28  2023-02-21
2       TD3+1_M  Mar 23  67.128  ...  2023-03-31       2023-02-28  2023-02-21
3       TD3+2_M  Apr 23  63.499  ...  2023-04-28       2023-02-28  2023-02-21
4       TD3+3_M  May 23  59.734  ...  2023-05-31       2023-02-28  2023-02-21

JSON

来源：https://stackoverflow.com/questions/75525363/parse-a-quite-nested-json-file-with-pandas-python-the-json-thing-is-now-in-one

1条答案

按热度按时间

ru9i0ody1#

假设您已经将JSON加载到python对象data中：

from itertools import chain
import pandas as pd

data = [{'date': '2023-02-03T00:00:00', 'groups': [{'periodType': 'm', 
                                              'projections': [{'identifier': 'TD3BALMO', 'period': 'Feb 23', 'value': 54.621, 'validFrom': '2023-02-01', 'validTo': '2023-02-28', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3CURMON', 'period': 'Feb 23', 'value': 53.855, 'validFrom': '2023-02-01', 'validTo': '2023-02-28', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+1_M', 'period': 'Mar 23', 'value': 55.387, 'validFrom': '2023-03-01', 'validTo': '2023-03-31', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+2_M', 'period': 'Apr 23', 'value': 55.174, 'validFrom': '2023-04-01', 'validTo': '2023-04-28', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+3_M', 'period': 'May 23', 'value': 55.748, 'validFrom': '2023-05-01', 'validTo': '2023-05-31', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+4_M', 'period': 'Jun 23', 'value': 55.608, 'validFrom': '2023-06-01', 'validTo': '2023-06-30', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+5_M', 'period': 'Jul 23', 'value': 52.548, 'validFrom': '2023-07-01', 'validTo': '2023-07-31', 'nextRolloverDate': '2023-02-28', 'archiveDate': '2023-02-03'}]}, 
                                            {'periodType': 'q', 
                                              'projections': [{'identifier': 'TD3CURQ', 'period': 'Q1 23', 'value': 52.638, 'validFrom': '2023-01-01', 'validTo': '2023-03-31', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'},
                                                              {'identifier': 'TD3+1Q', 'period': 'Q2 23', 'value': 55.51, 'validFrom': '2023-04-01', 'validTo': '2023-06-30', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+2Q', 'period': 'Q3 23', 'value': 51.729, 'validFrom': '2023-07-01', 'validTo': '2023-09-29', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'}, 
                                                              {'identifier': 'TD3+3Q', 'period': 'Q4 23', 'value': 62.63, 'validFrom': '2023-10-01', 'validTo': '2023-12-22', 'nextRolloverDate': '2023-03-31', 'archiveDate': '2023-02-03'}]
                                              }
                                            ]
  }]

proj = [item.get('projections', []) for dt in data for item in dt['groups']]
df = pd.DataFrame(chain(*proj))
print(df)

输出

identifier  period   value   validFrom     validTo nextRolloverDate archiveDate
0    TD3BALMO  Feb 23  54.621  2023-02-01  2023-02-28       2023-02-28  2023-02-03
1   TD3CURMON  Feb 23  53.855  2023-02-01  2023-02-28       2023-02-28  2023-02-03
2     TD3+1_M  Mar 23  55.387  2023-03-01  2023-03-31       2023-02-28  2023-02-03
3     TD3+2_M  Apr 23  55.174  2023-04-01  2023-04-28       2023-02-28  2023-02-03
4     TD3+3_M  May 23  55.748  2023-05-01  2023-05-31       2023-02-28  2023-02-03
5     TD3+4_M  Jun 23  55.608  2023-06-01  2023-06-30       2023-02-28  2023-02-03
6     TD3+5_M  Jul 23  52.548  2023-07-01  2023-07-31       2023-02-28  2023-02-03
7     TD3CURQ   Q1 23  52.638  2023-01-01  2023-03-31       2023-03-31  2023-02-03
8      TD3+1Q   Q2 23  55.510  2023-04-01  2023-06-30       2023-03-31  2023-02-03
9      TD3+2Q   Q3 23  51.729  2023-07-01  2023-09-29       2023-03-31  2023-02-03
10     TD3+3Q   Q4 23  62.630  2023-10-01  2023-12-22       2023-03-31  2023-02-03

赞(0）回复(0）举报 2023-02-26

我来回答

用Pandas/Python解析一个嵌套的Json文件，json文件现在位于 Dataframe 的一列中

1条答案

相关问题

热门标签

最新问答