Converting list of dictionary and dictionay data to a dataframe in python

Converting list of dictionary and dictionay data to a dataframe in python

Problem Description:

I am trying to send a SOAP request and iteratively and the response captured for each iteration is as follows.

df = {
   '@diffgr:id': 'Table1', 
   '@msdata:rowOrder': '0', 
   'NVIC_CUR': '0BQU22', 
   'NVIC_MODEL': '0BQU', 
   'ModelName': 'DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'
}

{'@diffgr:id': 'Table1', '@msdata:rowOrder': '0', 'NVIC_CUR': '0BQT22', 'NVIC_MODEL': '0BQT', 'ModelName': 'FDIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'}
[{'@diffgr:id': 'Table1', '@msdata:rowOrder': '0', 'NVIC_CUR': '09GE22', 'NVIC_MODEL': '09GE', 'ModelName': 'DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'}, {'@diffgr:id': 'Table2', '@msdata:rowOrder': '1', 'NVIC_CUR': '0BR222', 'NVIC_MODEL': '0BR2', 'ModelName': 'DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'}]
[{'@diffgr:id': 'Table1', '@msdata:rowOrder': '0', 'NVIC_CUR': '09HR22', 'NVIC_MODEL': '09HR', 'ModelName': 'DIESEL TURBO 5 3198 cc DTFI 6 SP AUTOMATIC'}, {'@diffgr:id': 'Table2', '@msdata:rowOrder': '1', 'NVIC_CUR': '09HS22', 'NVIC_MODEL': '09HS', 'ModelName': 'DIESEL TURBO 5 3198 cc DTFI 6 SP MANUAL'}]

The SOAP API sometimes return dictionary data, and sometime list of dictionary.

My idea was to create a Dataframe of selected columns (NVIC_CUR, NVIC_MODEL, ModelName)

output dataframe

Solution – 1

You can do it by this three-step-

1. You can append each SOAP API response to either its dictionary or a list to a new python list, let’s say its called – list_of_dict, and

2. Then iterate it and append it to a new list let’s call it final_list_of_dict if it’s a dictionary else iterate it again if a listlike below.

3. Finally, make a dataframe from the list of dictionaries.

Fullcode:

import pandas as pd

list_of_dict = [{'@diffgr:id': 'Table1', '@msdata:rowOrder': '0', 'NVIC_CUR': '0BQU22', 'NVIC_MODEL': '0BQU', 'ModelName': 'DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'},
{'@diffgr:id': 'Table1', '@msdata:rowOrder': '0', 'NVIC_CUR': '0BQT22', 'NVIC_MODEL': '0BQT', 'ModelName': 'FDIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'},
[{'@diffgr:id': 'Table1', '@msdata:rowOrder': '0', 'NVIC_CUR': '09GE22', 'NVIC_MODEL': '09GE', 'ModelName': 'DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'}, {'@diffgr:id': 'Table2', '@msdata:rowOrder': '1', 'NVIC_CUR': '0BR222', 'NVIC_MODEL': '0BR2', 'ModelName': 'DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'}],
[{'@diffgr:id': 'Table1', '@msdata:rowOrder': '0', 'NVIC_CUR': '09HR22', 'NVIC_MODEL': '09HR', 'ModelName': 'DIESEL TURBO 5 3198 cc DTFI 6 SP AUTOMATIC'}, {'@diffgr:id': 'Table2', '@msdata:rowOrder': '1', 'NVIC_CUR': '09HS22', 'NVIC_MODEL': '09HS', 'ModelName': 'DIESEL TURBO 5 3198 cc DTFI 6 SP MANUAL'}]
]

final_list_of_dict = []
for item in list_of_dict:
    if isinstance(item, list):
        for item in item:
            final_list_of_dict.append(item)
    final_list_of_dict.append(item)    
df = pd.DataFrame(final_list_of_dict, columns=['NVIC_CUR', 'NVIC_MODEL','ModelName'])
print(df)

Output:

  NVIC_CUR NVIC_MODEL                                          ModelName
0   0BQU22       0BQU  DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC
1   0BQT22       0BQT  FDIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOM...
2   09GE22       09GE  DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC
3   0BR222       0BR2  DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC
4   0BR222       0BR2  DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC
5   09HR22       09HR         DIESEL TURBO 5 3198 cc DTFI 6 SP AUTOMATIC
6   09HS22       09HS            DIESEL TURBO 5 3198 cc DTFI 6 SP MANUAL
7   09HS22       09HS            DIESEL TURBO 5 3198 cc DTFI 6 SP MANUAL

Solution – 2

Try using pandas library, it makes it quite easy:

import pandas as pd


df = {
   '@diffgr:id': 'Table1', 
   '@msdata:rowOrder': '0', 
   'NVIC_CUR': '0BQU22', 
   'NVIC_MODEL': '0BQU', 
   'ModelName': 'DIESEL TWIN TURBO 4 1996 cc BTCDI 10 SP AUTOMATIC'
}

if isinstance(df, list):
    result = pd.DataFrame(df, columns=['NVIC_CUR', 'NVIC_MODEL', 'ModelName'])
else:
    result = pd.DataFrame.from_dict(df, 
                                    orient='index',
                                    columns=['NVIC_CUR', 'NVIC_MODEL', 'ModelName']
                                    )