CODEWITHSUNDEEP
pythonjsonpandas
Tim van Beek
Tim van Beek

Reputation: 23

Converting JSON response into pandas dataframe

I managed to get some data from an API which looks like this:

{ "skip" : 0,
  "take" : 100,
  "rows" : [{"Onderwerp":"Kilometers","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null},{"Onderwerp":"Test 1","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null},{"Onderwerp":"Test 2","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null}]}

I'm trying to get this into a pandas dataframe, with rows containing the fields 'onderwerp', type_dossieritem, persoonsnummer and inkooprelatie. Unfortunately I'm struggling with the skip and take values above the data. How do I get a pandas df without the skip and take represented in the df as well?

data = r.text
df = pd.read_json(data, orient='rows')
print(df)

Help is greatly appreciated!

Upvotes: 2

Views: 70

Answers (2)

Mohan
Mohan

Reputation: 107

Please try this,

import pandas as pd
import json
from pandas import json_normalize


data = '''
{ "skip" : 0,
  "take" : 100,
  "rows" : [{"Onderwerp":"Kilometers","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null},{"Onderwerp":"Test 1","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null},{"Onderwerp":"Test 2","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null}]}
'''

info = json.loads(data)

df = json_normalize(info['rows']) #rows contain the required data
print(df)

Onderwerp  Type_dossieritem Persoonsnummer Inkooprelatie
0  Kilometers                51        1000074          None
1      Test 1                51        1000074          None
2      Test 2                51        1000074          None

Upvotes: 1

S2L
S2L

Reputation: 1934

import json 
import pandas as pd
resp = '{ "skip" : 0,  "take" : 100,  "rows" : [{"Onderwerp":"Kilometers","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null},{"Onderwerp":"Test 1","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null},{"Onderwerp":"Test 2","Type_dossieritem":51,"Persoonsnummer":"1000074","Inkooprelatie":null}]}'
parsed = json.loads(resp)

df = pd.DataFrame(parsed["rows"])
print(df)

Output:

   
    Onderwerp  Type_dossieritem Persoonsnummer Inkooprelatie
0  Kilometers                51        1000074          None
1      Test 1                51        1000074          None
2      Test 2                51        1000074          None

Upvotes: 1

Related Questions