Forum Discussion

AlexanderBerezo's avatar
AlexanderBerezo
Qrew Member
5 years ago

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

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
------------------------------
APIs and custom code
  • MikeHammond's avatar
    MikeHammond
    Quickbase Staff
    5 years 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
    ------------------------------

  • RichardChagnon's avatar
    RichardChagnon
    Qrew Trainee
    5 years 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
    ------------------------------