Discussions

Back to discussions
Expand all | Collapse all

Can't use pandas.read_json() with new QB RESTFul API

  • 1.  Can't use pandas.read_json() with new QB RESTFul API

    Posted 06-28-2020 23:43
    Edited by Alexander Berezovskiy 06-28-2020 23:46
    Hi, 
    I try to use inbuilt pandas read_json with new RESTFull QB API, but it doesn't work, even though I tried every orient and typ. 
    https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_json.html
    Using the most fitting orient'split' : dict like {index -> [index], columns -> [columns], data -> [values]}
    I get ValueError: JSON data had unexpected key(s): fields, metadata

    I've already came up with a workaround using 
    pd.DataFrame.from_dict(r.json()['data']).applymap(lambda x: list(x.values())[0])
    but it's not fully functional.

    Any ideas, what am I doing wrong? Maybe someone already had experience processing QB JSON in Python...


    ------------------------------
    Alexander Berezovskiy
    ------------------------------


  • 2.  RE: Can't use pandas.read_json() with new QB RESTFul API

    Posted 07-01-2020 09:57
    Edited by J. Michael Hammond 07-01-2020 10:21

    Hello Alexander!

    I do a lot of Python coding against Quick Base's RESTful JSON API, but I use the requests module.  I wasn't familiar at all with the pandas module.  As you can imagine, we don't know every language and every module out there, and it's tough for me to get too deeply into something that's happening within a customer's code.

    I was curious, though, so I went and looked up some documentation on pandas. I'm guessing here, but I think your problem is one of two things. 

    It's possible that pandas assumes that a JSON response has one of a specific set of structures, and our JSON simply doesn't conform to any of their expectations.  If this is the case, I'd suggest you use requests to just convert our response to a python dictionary and then do your own coding (like you're already showing us) to re-format it into whatever structure pandas requires for subsequent operations.

    It's also possible that if you pick the right values of the orient and typ parameters in pandas.read_json() you can get it to work.  That error message you show suggests that you were running a Quick Base report, and that pandas picked up the data element in our response but then didn't know what to do with the fields and metadata elements.

    Let me know if that helps!



    ------------------------------
    J. Michael Hammond

    Senior Software Engineer in Test

    Quick Base
    ------------------------------

    Original Message


  • 3.  RE: Can't use pandas.read_json() with new QB RESTFul API

    Posted 9 days ago
    Hi Alexander,
    Did you find a solution for this?   I  am working on a similar problem of writing to a csv file, and the key for me is that the response is a nested dictionary as is the data part. So a couple of  for loops works.

    for record in r.json()[data]:
           for item in record:  # build csv line


    I hope this helps

    ------------------------------
    Richard Chagnon
    Chicago IL
    ------------------------------

    Original Message