How to universally flatten different formats of dictionaries into dataframes?

Question

I have a API response object that returns different dictionary formats depending on input to the system.

As an example these are two formats:

----------------------1------------------------------

{'0': {'cdate': '2019-09-11 22:29:17',
  'email': 'z1@z1.com',
  'phone': '',
  'first_name': '',
  'last_name': '',
  'customer_acct_id': '0',
  'customer_acct_name': '',
  'segmentio_id': '',
  'bounced_hard': '0',
  'bounced_soft': '0',
  'bounced_date': '0000-00-00',
  'ip': '2130706433',
  'ua': '',
  'hash': '7b0a049140eff94348c501d6792761ba',
  'socialdata_lastcheck': '0000-00-00 00:00:00',
  'email_local': '',
  'email_domain': '',
  'sentcnt': '0',
  'rating': '0',
  'rating_tstamp': '0000-00-00',
  'gravatar': '0',
  'deleted': '0',
  'anonymized': '0',
  'adate': '0000-00-00 00:00:00',
  'udate': '0000-00-00 00:00:00',
  'edate': '0000-00-00 00:00:00',
  'deleted_at': '0000-00-00 00:00:00',
  'created_utc_timestamp': '2019-09-11 22:29:17',
  'updated_utc_timestamp': '2019-09-11 22:29:17',
  'id': '4184'},
 'result_code': 0,
 'result_message': 'The email z1@z1.com is in the system already, please edit that contact instead.',
 'result_output': 'json'}

--------------------2---------------------------------

{'subscriber_id': 4184,
 'sendlast_should': 0,
 'sendlast_did': 0,
 'result_code': 1,
 'result_message': 'Contact added',
 'result_output': 'json'}

I can flatten 1 first dictionary format into a dataframe like so:

  df = pd.DataFrame(resp.json())

However when I get back format 2 I get error:

ValueError: If using all scalar values, you must pass an index

To work around this I've tried from_dict,read_json,DataFrame(resp.json()) to no luck.

Is there anyway I can flatten any dictionary object into a dataframe universally or atleast test for different formats?

My goal is too extract values from keys 'id' in example 1 or 'subscriber_id' from example 2. Open to all suggestions to accomplish this goal.

I can extract value of key id from example 1 like so:

df = pd.DataFrame(resp.json()) 
if 'id' in df.index:
            new['id'] = df.loc['id']['0']

Is there a missing } at the end of the first format?. It looks like one its a keyed collection and two is a straight object — Hecatonchires, Sep 12 '19 at 04:23

score 1 · Accepted Answer · answered Sep 12 '19 at 05:15

Try the below code, Hope this will help

 {0:{'subscriber_id': 4184,   # <-- Here add key  
 'sendlast_should': 0,
 'sendlast_did': 0,
 'result_code': 1,
 'result_message': 'Contact added',
 'result_output': 'json'}}

In second json you have to add key for which this whole data is value. Hence that key will act as a column header for this data.

Ouput will be :

                0
result_code     1
result_message  Contact added
result_output   json
sendlast_did    0
sendlast_should     0
subscriber_id   4184

How to universally flatten different formats of dictionaries into dataframes?

1 Answers1