How to properly json_normalize so that I get the dataframe in proper format?

user_14364775 0
Viewed 176

I am using an amazon dataset for question answering problems.

the code from amazon is :

import pandas as pd
import gzip

def parse(path):
  g = gzip.open(path, 'rb')
  for l in g:
    yield eval(l)

def getDF(path):
  i = 0
  df = {}
  for d in parse(path):
    df[i] = d
    i += 1
  return pd.DataFrame.from_dict(df, orient='index')

df = getDF('QA_Video_Games.json.gz')

df is like:

enter image description here

I want to further normalize the questions field,

I tried

df1 = pd.json_normalize(df,record_path=['questions'])

How to properly normalize the json part so that I can get the every part as separate columns in a dataframe?

pandas dataframe numpy python json
1 Answers

To fully normalize this file, you can use:

from ast import literal_eval

all_data = []
with open("unpacked_file.json", "r") as f_in:
    for line in f_in:
        all_data.append(literal_eval(line))

df = pd.DataFrame(all_data)
df = df.explode("questions").reset_index(drop=True)
df = (
    pd.concat(
        [df, pd.json_normalize(df.pop("questions"))],
        axis=1,
    )
    .explode("answers")
    .reset_index(drop=True)
)
df = pd.concat(
    [df, pd.json_normalize(df.pop("answers"))],
    axis=1,
)
print(df)

Prints:

             asin questionType                askerID        questionTime                                       questionText                                         answerText             answererID          answerTime     helpful answerType answerScore
0      B0000512IE   open-ended         A39TQ7XW6P36BU       June 29, 2014                 Can I use these with Window 8/8.1?  Yes, you will need to go to their website to d...          AH14F9TYDABNH       June 29, 2014      [2, 2]        NaN         NaN
1      B0000512IE   open-ended         A39TQ7XW6P36BU       June 29, 2014                 Can I use these with Window 8/8.1?  Sure can. My Windows 8.1 machine automatically...         A36BQQD67VJOD2       June 29, 2014      [2, 2]        NaN         NaN
2      B0000512IE   open-ended         A39TQ7XW6P36BU       June 29, 2014                 Can I use these with Window 8/8.1?  Mine work with W8, can't answer for W8.1 but s...          AZY7TFN31JV8M       June 29, 2014      [1, 1]        NaN         NaN
3      B0000512IE   open-ended         A39TQ7XW6P36BU       June 29, 2014                 Can I use these with Window 8/8.1?  Yes. I installed on windows 8 and had no problems         A1RU4ZCFMLYY9E       June 29, 2014      [1, 1]        NaN         NaN
4      B0000512IE   open-ended         A39TQ7XW6P36BU       June 29, 2014                 Can I use these with Window 8/8.1?  I don't see why not. I don't use the CH driver...         A223O8W9Q7VLAE       June 29, 2014      [0, 1]        NaN         NaN
5      B0000512IE   open-ended          AL82696CUTIL5     August 27, 2014  Could this be adapted to be used in racing gam...  I googled the question and found many forums o...          AG8XZT4PKY7UZ     August 28, 2014      [0, 0]        NaN         NaN
6      B0000512IE   open-ended          AL82696CUTIL5     August 27, 2014  Could this be adapted to be used in racing gam...  I've always been under the impression you coul...         A261POEFLJDN6L     August 28, 2014      [0, 0]        NaN         NaN
7      B0000512IE   open-ended          AL82696CUTIL5     August 27, 2014  Could this be adapted to be used in racing gam...                              Yes I would think so.          AHGOG7QAAE3XP     August 28, 2014      [0, 0]        NaN         NaN

...

28890  B00K4S9C9I   open-ended         A1NJ7DFD2KUOLC       March 5, 2015                         Would it work on a lap top               yes it would work great on a lap top          A6JLDUBTXCZA5      March 14, 2015      [0, 0]        NaN         NaN
28891  B00K4S9C9I       yes/no         A1X0KO1GKYUXLL    January 10, 2015                                 can u mod this one  Yes you can. I personally download from spinti...         A215AZEU7D23KI    January 10, 2015      [0, 0]          Y      0.9897
28892  B00K4S9C9I       yes/no         A1X0KO1GKYUXLL    January 10, 2015                                 can u mod this one                                          Yes I can          ARR5TN6Y7R824    January 10, 2015      [0, 0]          Y      0.9807
user_100359850
Related