Как спрятать фреймворк pandas с некоторыми столбцами как json?

У меня есть датафрейм df который загружает данные из базы данных. Большинство столбцов являются строками json, а некоторые - списком jsons. Например:

id     name     columnA                               columnB
1     John     {"dist": "600", "time": "0:12.10"}    [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "3rd", "value": "200"}, {"pos": "total", "value": "1000"}]
2     Mike     {"dist": "600"}                       [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "total", "value": "800"}]
...

Как видите, не все строки имеют одинаковое количество элементов в строках json для столбца.

Что мне нужно сделать, это сохранить обычные столбцы, такие как id и name как есть, и сгладить столбцы json следующим образом:

id    name   columnA.dist   columnA.time   columnB.pos.1st   columnB.pos.2nd   columnB.pos.3rd     columnB.pos.total
1     John   600            0:12.10        500               300               200                 1000 
2     Mark   600            NaN            500               300               Nan                 800

Я пытался использовать json_normalize так:

from pandas.io.json import json_normalize
json_normalize(df)

Но, похоже, есть некоторые проблемы с keyerror. Как правильно это сделать?

Ответ 1

Здесь решение, использующее json_normalize() снова с использованием пользовательской функции для получения данных в правильном формате, json_normalize функции json_normalize.

import ast
from pandas.io.json import json_normalize

def only_dict(d):
    '''
    Convert json string representation of dictionary to a python dict
    '''
    return ast.literal_eval(d)

def list_of_dicts(ld):
    '''
    Create a mapping of the tuples formed after 
    converting json strings of list to a python list   
    '''
    return dict([(list(d.values())[1], list(d.values())[0]) for d in ast.literal_eval(ld)])

A = json_normalize(df['columnA'].apply(only_dict).tolist()).add_prefix('columnA.')
B = json_normalize(df['columnB'].apply(list_of_dicts).tolist()).add_prefix('columnB.pos.')

Наконец, присоединитесь к DFs по общему индексу, чтобы получить:

df[['id', 'name']].join([A, B])

РЕДАКТИРОВАТЬ: - Согласно комментарию @MartijnPieters, рекомендуемый способ декодирования строк json будет использовать json.loads() который намного быстрее по сравнению с использованием ast.literal_eval() если вы знаете, что источником данных является JSON,

Ответ 2

создайте пользовательскую функцию для выравнивания columnB затем используйте pd.concat

def flatten(js):
    return pd.DataFrame(js).set_index('pos').squeeze()

pd.concat([df.drop(['columnA', 'columnB'], axis=1),
           df.columnA.apply(pd.Series),
           df.columnB.apply(flatten)], axis=1)

Ответ 3

Самым быстрым кажется:

json_struct = json.loads(df.to_json(orient="records"))    
df_flat = pd.io.json.json_normalize(json_struct) #use pd.io.json

Ответ 4

data = {
  "data": [
    {
      "date": "2018-08-20T00:00:00",
      "values": [
        {
          "account_id": "account_1",
          "device_id": "device_1",
          "deviceModel": "testdev",
          "csp_id": "csp_device_1",
          "Events": [
            {
              "EventCategory": "Security Scan",
              "EventCategoryData": [
                {
                  "name": "security_scan_malware_detected",
                  "info": [
                    {
                      "threat": "Pup",
                      "count": 8.0
                    }
                  ]
                },
                {
                  "name": "security_scan_malware_removed",
                  "info": [
                    {
                      "threat": "adware",
                      "count": 1.0
                    }
                  ]
                }
              ],
              "scancount": 2.0
            },
            {
              "EventCategory": "Web Security",
              "EventCategoryData": [
                {
                  "name": "web_security_number_of_unverified_sites",
                  "info": [
                    {
                      "threat": "Unverified Web Sites",
                      "count": 2.0
                    }
                  ]
                },
                {
                  "name": "web_security_number_of_suspicious_sites",
                  "info": [
                    {
                      "threat": "Suspicious Web Sites",
                      "count": 0.0
                    }
                  ]
                },
                {
                  "name": "web_security_number_of_risky_sites",
                  "info": [
                    {
                      "threat": "Risky Web Sites",
                      "count": 2.0
                    }
                  ]
                }
              ]
            },
            {
              "EventCategory": "Network Security",
              "EventCategoryData": [
                {
                  "name": "network_security_threat_detected",
                  "info": [
                    {
                      "threat": "Wap-wifi",
                      "count": 2.0
                    }
                  ]
                }
              ],
              "scancount": 4.0
            },
            {
              "EventCategory": "Others",
              "EventCategoryData": [
                {
                  "name": "security_scan_dat_update_complete",
                  "info": [
                    {
                      "previousversion": "default",
                      "updatedversion": "default"
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    },
    {
      "date": "2018-08-22T00:00:00",
      "values": [
        {
          "account_id": "account_1",
          "device_id": "device_1",
          "deviceModel": "testdev",
          "csp_id": "csp_device_1",
          "Events": [
            {
              "EventCategory": "Security Scan",
              "EventCategoryData": [
                {
                  "name": "security_scan_malware_detected",
                  "info": [
                    {
                      "threat": "Pup",
                      "count": 2
                    }
                  ]
                },
                {
                  "name": "security_scan_malware_removed",
                  "info": [
                    {
                      "threat": "Malware",
                      "count": 1
                    },
                    {
                      "threat": "Pup",
                      "count": 1
                    }
                  ]
                }
              ],
              "scancount": 1
            },
            {
              "EventCategory": "Web Security",
              "EventCategoryData": [
                {
                  "name": "web_security_number_of_unverified_sites",
                  "info": [
                    {
                      "threat": "Unverified Web Sites",
                      "count": 1
                    }
                  ]
                },
                {
                  "name": "web_security_number_of_suspicious_sites",
                  "info": [
                    {
                      "threat": "Suspicious Web Sites",
                      "count": 1
                    }
                  ]
                },
                {
                  "name": "web_security_number_of_risky_sites",
                  "info": [
                    {
                      "threat": "Risky Web Sites",
                      "count": 1
                    }
                  ]
                }
              ]
            },
            {
              "EventCategory": "Network Security",
              "EventCategoryData": [
                {
                  "name": "network_security_threat_detected",
                  "info": [
                    {
                      "threat": "OpenWifi",
                      "count": 1
                    },
                    {
                      "threat": "Wap-wifi",
                      "count": 1
                    }
                  ]
                }
              ],
              "scancount": 1
            },
            {
              "EventCategory": "Others",
              "EventCategoryData": [
                {
                  "name": "security_scan_dat_update_complete",
                  "info": [
                    {
                      "previousversion": "default",
                      "updatedversion": "default"
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  ],
  "status": "success",
  "identifier": "device_1",
  "identifier_type": "csp",
  "query_type": "rt_aggregate",
  "info_type": ""
}

У меня такое же требование, но я не могу использовать решение Nickil Maveli