How to convert list into dataframe with specific column in python?-CodePudding

I have list data which I want to change into dataframe but with specific column. Please help me to get the correct output.

Example- data I have -

[{'data': [{'interval': '2021-09-22T09:13:57.000Z/2021-09-29T09:13:57.000Z',
           'metrics': [{'metric': 'nOffered',
                        'qualifier': None,
                        'stats': {'count': 17,
                                  'count_negative': None,
                                  'count_positive': None,
                                  'current': None,
                                  'denominator': None,
                                  'max': None,
                                  'min': None,
                                  'numerator': None,
                                  'ratio': None,
                                  'sum': None,
                                  'target': None}},
                       {'metric': 'tAnswered',
                        'qualifier': None,
                        'stats': {'count': 17,
                                  'count_negative': None,
                                  'count_positive': None,
                                  'current': None,
                                  'denominator': None,
                                  'max': 17327.0,
                                  'min': 4569.0,
                                  'numerator': None,
                                  'ratio': None,
                                  'sum': 156929.0,
                                  'target': None}},
                       {'metric': 'tTalk',
                        'qualifier': None,
                        'stats': {'count': 29,
                                  'count_negative': None,
                                  'count_positive': None,
                                  'current': None,
                                  'denominator': None,
                                  'max': 2650757.0,
                                  'min': 2124.0,
                                  'numerator': None,
                                  'ratio': None,
                                  'sum': 8402252.0,
                                  'target': None}}],
           'views': None}],
 'group': {'mediaType': 'voice',
           'queueId': 'a72dba75-0bc6-4a65-b120-8803364f8dc3'}}]

I have to convert it into below format-

nOffered_count  nOffered_sum   tAnswered_count  tAnswered_sum   tTalk_count   tTalk_sum
17              None           17               156929.0        29            8402252.0

CodePudding user response：

Try this:

req_col = ["nOffered_count", "nOffered_sum", "tAnswered_count", "tAnswered_sum", "tTalk_count", "tTalk_sum"]

mapper = {}
for i in data[0]["data"][0]["metrics"]:
    for j in req_col:
        metric = j.split("_")[0]
        sub_metric = j.split("_")[1]

        if i["metric"] == metric:
            mapper[j] = [i["stats"][sub_metric]]

df = pd.DataFrame(mapper)
df
Out[81]: 
   nOffered_count nOffered_sum  tAnswered_count  tAnswered_sum  tTalk_count tTalk_sum
0              17         None               17       156929.0           29 8402252.0

if it help do upvote and accept the solution :)

CodePudding user response：

this will help if you have multiple entry for data. Please upvote if this is helpful.

column_names = ["nOffered_count", "nOffered_sum", "tAnswered_count", "tAnswered_sum", "tTalk_count", "tTalk_sum"]

final_lst = []
for item in lst:
    data_lst = []
    for data in item['data']:
        for metric in data['metrics']:
            data_lst.append(metric['stats']['count'])
            data_lst.append(metric['stats']['sum'])
    final_lst.append(data_lst)
            
df = pd.DataFrame(final_lst,columns=column_names)
print(df)

   nOffered_count nOffered_sum  tAnswered_count  tAnswered_sum  tTalk_count  
0              17         None               17       156929.0           29   
   tTalk_sum  
0  8402252.0