Needing Help Converting Conversation Aggregation Query to a Dataframe To Be Exported as A CSV

[cos]

I'm working with the the Conversations Aggregate Query, I am able to get the response back easily enough but the result is a severely nested JSON response. Is there a built in method within the SDK to parse this or any pointers on being able to normalize it into a pandas dataframe? The goal would be to export this as a CSV in a format that can be easily used for analyzing (for now, future use is to load it into a db).

The code I am using is below:

import PureCloudPlatformClientV2
from PureCloudPlatformClientV2.rest import ApiException
from pprint import pprint

CLIENT_ID = 'ID'
CLIENT_SECRET = 'sec'
ORG_REGION = 'us_west_2'

# Set environment
region = PureCloudPlatformClientV2.PureCloudRegionHosts[ORG_REGION]
PureCloudPlatformClientV2.configuration.host = region.get_api_host()

# OAuth when using Client Credentials
api_client = PureCloudPlatformClientV2.api_client.ApiClient() \
            .get_client_credentials_token(CLIENT_ID, CLIENT_SECRET)
token = api_client.access_token

# Configure OAuth2 access token for authorization: PureCloud OAuth
PureCloudPlatformClientV2.configuration.access_token = token

# create an instance of the API class
api_instance = PureCloudPlatformClientV2.ConversationsApi()
body = PureCloudPlatformClientV2.ConversationAggregationQuery() # ConversationAggregationQuery | query
body.interval = '2022-05-30T00:00:00.000Z/2022-05-31T00:00:00.000Z'
body.metrics = ["nOffered","tTalkComplete"]
body.group_by = ['userId']

try:
    # Query for conversation aggregates
    api_response = api_instance.post_analytics_conversations_aggregates_query(body).to_json()
    print(api_response)
except ApiException as e:
    print("Exception when calling PostAnalyticsConversationsAggregatesQueryRequest->post_analytics_conversations_aggregates_query: %s\n" % e)

[John_Carnell]

Hi COS,

Our Python SDK does not have any built-in mechanisms to convert the results of an API call like the API query into a Pandas data frame. Since our API enforces the API contracts one of the more common mistakes people make is that they try to use the pd.json_normalize function to try and flatten the object into a Pandas data frame. This code fails because you are not getting back a dictionary of dictionaries-style data structure that Pandas expect.

We do have a function in the util.py called sanitize_for_serialization() that can help take a result and make it more parseable for JSON libraries. That being said, I am afraid right now the only option you have is perform your query and then manually copy the data into a data structure that can be then consumed by a Pandas data structure.

Thanks,
John Carnell
Manager, Developer Engagement

[cos]

Thanks for the reply John! Within the code above, would you be able to provide an example of how I would add the sanitize function? I should clarify too it doesn't have to be a dataframe within pandas specifically I just need something more so tabular to work with. I assume the answer is still the same, just figured i'd clarify.

[John_Carnell]

Hi Cos,

I don't have anything readily available (it's been forever since I did some Pandas/Juypter notebook work) :).

I did write up a ticket for a blueprint from our team that will demonstrate this. I know this wont help you in the short term, but it might help answer this question in the future. I am going to send this thread onto some of my colleagues on the partner side and see if they can come up with something, but I can't promise anything in a timely manner.

Thanks,
John Carnell
Manager, Developer Engagement