Complex Infinite List to DataFrame conversion-CodePudding

A list contains content in the following format :

[{'m': {'name': 'esl',
   'type': 'FS',
   'env': 'C1',
   'service': 'None',
   'ins': '1'},
  'value': [17, 'NaN']},
 {'m': {'name': 'esl',
   'type': 'FS',
   'env': 'C1',
   'service': 'CBA',
   'instance': '10'},
  'value': [147, 'NaN']},
 {'m': {'name': 'esl',
   'type': 'FS',
   'env': 'C1',
   'service': 'CBA',
   'instance': '12'},
  'value': [16, 'NaN']}]

There are n number of items in the list

need a list to dataframe conversion, desired output should have following column names- name, type, env, service, ins, value

CodePudding user response：

The structure is very messy & inconsistent keys are there. So below code can solve your problem,

import pandas as pd


mainList = [{'m': {'name': 'esl',
   'type': 'FS',
   'env': 'C1',
   'service': 'None',
   'instance': '1'},
  'value': [17, 'NaN']},
 {'m': {'name': 'esl',
   'type': 'FS',
   'env': 'C1',
   'service': 'CBA',
   'instance': '10'},
  'value': [147, 'NaN']},
 {'m': {'name': 'esl',
   'type': 'FS',
   'env': 'C1',
   'service': 'CBA',
   'instance': '12'},
  'value': [16, 'NaN']}]

dfColumns = list(mainList[0]['m'].keys())
dfColumns.append('value')

mainDict = {}
for i in dfColumns:
    mainDict.update({i:[]})

for i in mainList:
    for key,value in i['m'].items():
        mainDict[key].append(value)
    mainDict['value'].append(i['value'])

df = pd.DataFrame(mainDict)

output DataFrame will be

  name type env service instance       value
0  esl   FS  C1    None        1   [17, NaN]
1  esl   FS  C1     CBA       10  [147, NaN]
2  esl   FS  C1     CBA       12   [16, NaN]

Note: I have corrected one of your first elements to instance from ins. To keep the same key for all the data