Python:json_normalize pandas 系列给出了 TypeError [英] Python: json_normalize a pandas series gives TypeError

问题描述

我在 Pandas 系列中有数万行这样的 json 片段 df["json"]

<代码>[{'ID':[{'lotId': '1','身份证':'123456'}],'日期': '2009-04-17','bidsCount': 2,}, {'ID':[{'lotId': '2','身份证':'123456'}],'日期': '2009-04-17','bidsCount': 4,}, {'ID':[{'lotId': '3','身份证':'123456'}],'日期': '2009-04-17','bidsCount': 8,}]

原始文件示例:

{"type": "OPEN","title": "rainbow","json": [{"IDs": [{"lotId": "1","Id": "123456"}],"date": "2009-04-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "123456"}],"date": "2009-04-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "123456"}],"date": "2009-04-17","bidsCount": 8,}]}{"type": "CLOSED","title": "clouds","json": [{"IDs": [{"lotId": "1","Id": "23345"}],"date": "2009-05-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "23345"}],"date": "2009-05-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "23345"}],"date": "2009-05-17","bidsCount": 8,}]}df = pd.read_json("file.json",lines=True)

我正在尝试将它们变成一个数据框，比如

Id lotId bidsCount 日期123456 1 2 2009-04-17123456 2 4 2009-04-17123456 3 8 2009-04-17

通过使用

json_normalize(df["json"])

但是我明白

AttributeError: 'list' 对象没有属性 'values'

我想 json 片段被视为一个列表，但是我不知道如何让它工作.感谢帮助！

解决方案

我认为您的 df['json'] 是一个嵌套列表.您可以使用 for 循环并连接数据帧以获得大数据帧，即

数据:

{"type": "OPEN","title": "rainbow","json": [{"IDs": [{"lotId": "1","Id": "123456"}],"date": "2009-04-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "123456"}],"date": "2009-04-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "123456"}],"date": "2009-04-17","bidsCount": 8,}]}{"type": "CLOSED","title": "clouds","json": [{"IDs": [{"lotId": "1","Id": "23345"}],"date": "2009-05-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "23345"}],"date": "2009-05-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "23345"}],"date": "2009-05-17","bidsCount": 8,}]}df = pd.read_json("file.json",lines=True)

数据帧:

new_df = pd.concat([pd.DataFrame(json_normalize(x)) for x in df['json']],ignore_index=True)

输出:

<前>ID 投标计数日期0 [{'Id':'123456'，'lotId':'1'}] 2 2009-04-171 [{'Id':'123456'，'lotId':'2'}] 4 2009-04-172 [{'Id':'123456'，'lotId':'3'}] 8 2009-04-173 [{'Id':'23345','lotId':'1'}] 2 2009-05-174 [{'Id':'23345','lotId':'2'}] 4 2009-05-175 [{'Id':'23345'，'lotId':'3'}] 8 2009-05-17

如果您希望 ID 的键作为列，那么您可以使用

new_df['lotId'] = [x[0]['lotId'] for x in new_df['IDs']]new_df['IDs'] = [x[0]['Id'] for x in new_df['IDs']]

<前>ID bidsCount 日期 lotId0 123456 2 2009-04-17 11 123456 4 2009-04-17 22 123456 8 2009-04-17 33 23345 2 2009-05-17 14 23345 4 2009-05-17 25 23345 8 2009-05-17 3

I have tens of thousands rows of json snippets like this in a pandas series df["json"]

[{
    'IDs': [{
        'lotId': '1',
        'Id': '123456'
    }],
    'date': '2009-04-17',
    'bidsCount': 2,
}, {
    'IDs': [{
        'lotId': '2',
        'Id': '123456'
    }],
    'date': '2009-04-17',
    'bidsCount': 4,
}, {
    'IDs': [{
         'lotId': '3',
         'Id': '123456'
    }],
    'date': '2009-04-17',
    'bidsCount': 8,
}]

Sample of the original file:

{"type": "OPEN","title": "rainbow","json": [{"IDs": [{"lotId": "1","Id": "123456"}],"date": "2009-04-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "123456"}],"date": "2009-04-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "123456"}],"date": "2009-04-17","bidsCount": 8,}]}
{"type": "CLOSED","title": "clouds","json": [{"IDs": [{"lotId": "1","Id": "23345"}],"date": "2009-05-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "23345"}],"date": "2009-05-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "23345"}],"date": "2009-05-17","bidsCount": 8,}]}


df = pd.read_json("file.json", lines=True)

I am trying to make them into a data frame, something like

Id      lotId      bidsCount    date
123456  1          2            2009-04-17
123456  2          4            2009-04-17
123456  3          8            2009-04-17

by using

json_normalize(df["json"])

However I get

AttributeError: 'list' object has no attribute 'values'

I guess the json snippet is seen as a list, however I can not figure out how to make it work otherwise. Help appreciated!

解决方案

I think your df['json'] is a nested list. You can use a for loop and concatenate the dataframe to get the big dataframe i.e

Data:

{"type": "OPEN","title": "rainbow","json": [{"IDs": [{"lotId": "1","Id": "123456"}],"date": "2009-04-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "123456"}],"date": "2009-04-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "123456"}],"date": "2009-04-17","bidsCount": 8,}]}
{"type": "CLOSED","title": "clouds","json": [{"IDs": [{"lotId": "1","Id": "23345"}],"date": "2009-05-17","bidsCount": 2,}, {"IDs": [{"lotId": "2","Id": "23345"}],"date": "2009-05-17","bidsCount": 4,}, {"IDs": [{"lotId": "3","Id": "23345"}],"date": "2009-05-17","bidsCount": 8,}]}

df = pd.read_json("file.json", lines=True)

DataFrame:

new_df = pd.concat([pd.DataFrame(json_normalize(x)) for x in df['json']],ignore_index=True)

Output:

                                IDs  bidsCount        date
0  [{'Id': '123456', 'lotId': '1'}]          2  2009-04-17
1  [{'Id': '123456', 'lotId': '2'}]          4  2009-04-17
2  [{'Id': '123456', 'lotId': '3'}]          8  2009-04-17
3   [{'Id': '23345', 'lotId': '1'}]          2  2009-05-17
4   [{'Id': '23345', 'lotId': '2'}]          4  2009-05-17
5   [{'Id': '23345', 'lotId': '3'}]          8  2009-05-17

If you want the keys of IDs as columns then you use

new_df['lotId'] = [x[0]['lotId'] for x in new_df['IDs']]
new_df['IDs'] = [x[0]['Id'] for x in new_df['IDs']]

      IDs  bidsCount        date lotId
0  123456          2  2009-04-17     1
1  123456          4  2009-04-17     2
2  123456          8  2009-04-17     3
3   23345          2  2009-05-17     1
4   23345          4  2009-05-17     2
5   23345          8  2009-05-17     3

这篇关于Python:json_normalize pandas 系列给出了 TypeError的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！