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 15 days ago
    Edited by Alexander Berezovskiy 15 days ago
    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 13 days ago
    Edited by J. Michael Hammond 13 days ago

    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
    ------------------------------