Howto read odata into pandas with python

Question

I'm trying to load odata into

This is my code:

import pandas as pd
import pyodata
import requests
SERVICE_URL = 'http://services.odata.org/V2/Northwind/Northwind.svc/'
HTTP_LIB = requests.Session()
northwind = pyodata.Client(SERVICE_URL, HTTP_LIB)
df = pd.DataFrame(northwind)

I get this error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\KrestenSkovstedBuch\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\frame.py", line 730, in __init__
    raise ValueError("DataFrame constructor not properly called!")
ValueError: DataFrame constructor not properly called!

I think I need something in between my pyodata object and my DataFrame. What would that be?

score 0 · Answer 1 · answered Feb 04 '22 at 10:55

It needs to be some your own code :)

You are passing northwind, which is Pyodata.Client class to DataFrame constructor, which, well, expects something else. You must query your odata service using pyodata and then use the returned data structure (JSON probably) to initialize the Pandas DataFrame correctly, this is a more generic and already answered problem.

score 0 · Accepted Answer · edited Jul 10 '23 at 05:35

Here is an adaptation of a code I did. It uses pyodata package. I didn't run it, but any bug would be simple to adapt.

I think there must be a way to list all queried properties of an Entity but I don't know how. That's why I'm querying all properties.

import pyodata
import requests
import pandas as pd

SERVICE_URL = 'http://services.odata.org/V2/Northwind/Northwind.svc/'
HTTP_LIB = requests.Session()
northwind = pyodata.Client(SERVICE_URL, HTTP_LIB)

# query everything of entity set Customers
customers = northwind.entity_sets.Customers.get_entities().execute()

schema = service.schema
entity_type = next(et for et in schema.entity_types if et.name == "Customer")
properties = entity_type.properties()

def monta_prop_dict(obj, properties):
    d = {}
    for prop in properties:
        d[prop.name] = getattr(obj, prop.name)
    return d

data = []
for c in customers:
    data.append(monta_prop_dict(c, properties))

df = pd.DataFrame(data)

You would probably need to convert some values for the correct type. For me just datetimes are being converted. Everything else comes as string.

Howto read odata into pandas with python

2 Answers2

Linked