Skip to content
Advertisement

Why do I only receive 1 row of data after converting json to dataframe in python?

My script-

from azure.cosmos import CosmosClient
import os
import pandas as pd
url = "https://xx-cosmos-dev.xx.azure.com:xx/"
key = 'xx=='
client = CosmosClient(url, credential=key)
database_name = 'ClS'
database = client.get_database_client(database_name)
container_name = 'cls'
container = database.get_container_client(container_name)

# Enumerate the returned items
import json
for item in container.query_items(query='SELECT * FROM c',enable_cross_partition_query=True):
    print(json.dumps(item, indent=True))

Out:

    {
       "IncidentId": null,
       "IncidentType": "Illness",
       "SymptomStartDate": "4/5/2022",
       "SubDiagnosisIds": [
        155
       ]
      }
     ],
     "_rid": "xx==",
     "_self": "dbs/Cb04AA==/colls/xx=/docs/Cb04ALkCaPhsAwAAAAAAAA==/",
     "_etag": ""2600f2cf-0000-0400-0000-xxx"",
     "_attachments": "attachments/",
     "_ts": xxxx
    }
    {
     "IsServiceSubCodeToSubDiagnosisRule": false,
     "IsSubDiagnosisRule": false,
     "IsClaimSubmission": true,
     "id": "xxxx",
     "partitionKey": "Submission",
     "term": xx,
     "got": xx,
     "IsMultiInvoce": false,
     "AutomationIncidents": no

I tried-

dflist = []
dflist.append(dict(item))  
# Convert list to pandas DataFrame
df = pd.DataFrame(dflist)

But i only receive 1 row of data. I have about 300 rows.Why does it not capture all? Do I have to add something to the append item function?

Advertisement

Answer

Thank you, Tranbi and Joe Tha. Adding gist of conversation as a community wiki answer to help other community members.

Instead of this code:

dflist = []
dflist.append(dict(item))  
# Convert list to pandas DataFrame
df = pd.DataFrame(dflist)

Try this code to get more than one row:

dflist = []
for item in container.query_items(query='SELECT * FROM c',enable_cross_partition_query=True):
    dflist.append(dict(item))
df = pd.DataFrame(dflist)
7 People found this is helpful
Advertisement