python之在python中将json转换为dataframe

我有一个 json 文件(下面的示例)。我正在尝试使用 python 从中创建一个 Dataframe: JSON:

{"data": { 
        "A": [{ 
                "CREATION_DATE": "1482105600", 
                "SOURCE_COUNT": "0" 
            }, 
            { 
                "CREATION_DATE": "1482105600", 
                "SOURCE_COUNT": "0" 
            } ], 
         "B": [{ 
                "CREATION_DATE": "1487808000", 
                "SOURCE_COUNT": "1048" 
                }, 
                { 
                "CREATION_DATE": "1487894400", 
                "SOURCE_COUNT": "1103" 
                 } ] 
         } 
 } 

当我尝试将其转换为数据框时:

我的代码:

import json 
file = 'mysample.json' 
with open(file) as train_file: 
    dict_train = json.load(train_file) 
 
# converting json dataset from dictionary to dataframe 
train = pd.DataFrame.from_dict(dict_train, orient='index') 
train.reset_index(level=0, inplace=True) 

输出:

    index      A                                                   B 
0   data    [{'CREATION_DATE': '1482105600', 'SOURCE_COUNT...   [{'CREATION_DATE': '1487808000', 'SOURCE_COUNT... 

相反，我希望将其转换为如下所示的数据框:

system  CREATION_DATE   SOURCE_COUNT 
A        1482105600        0 
A        1482105600        0 
B        1487808000        1048 
B        1487894400        1103 

如何修改我的代码以获得预期的输出？

请您参考如下方法:

pd.DataFrame(dict_train['data']).stack().apply(pd.Series).reset_index(level=0, drop=True).sort_index() 
 
 
  CREATION_DATE SOURCE_COUNT 
A    1482105600            0 
A    1482105600            0 
B    1487808000         1048 
B    1487894400         1103