[FIXED] how to write json into dataframe?

Issue

I've got this raw apple search ads json returned. How do I write it into dataframe? When I tried reading the campaign details, it's as followed:

   {
"other":false
"granularity":[...]
"metadata":{...}
}

{
"other":false
"granularity":[
0:{
"impressions":481
"taps":42
"installs":25
"newDownloads":20
"redownloads":5
"latOnInstalls":0
"latOffInstalls":1
"ttr":0.0873
"avgCPA":{
"amount":"1.338"
"currency":"USD"
}
"avgCPT":{
"amount":"0.7964"
"currency":"USD"
}
"avgCPM":{
"amount":"69.5426"
"currency":"USD"
}
"localSpend":{
"amount":"33.45"
"currency":"USD"
}
"conversionRate":0.5952
"date":"2022-04-01"
}
1:{
"impressions":962
"taps":27
"installs":12
"newDownloads":10
"redownloads":2
"latOnInstalls":0
"latOffInstalls":1
"ttr":0.0281
"avgCPA":{
"amount":"2.8925"
"currency":"USD"
}
"avgCPT":{
"amount":"1.2856"
"currency":"USD"
}
"avgCPM":{
"amount":"36.0811"
"currency":"USD"
}
"localSpend":{
"amount":"34.71"
"currency":"USD"
}
"conversionRate":0.4444
"date":"2022-04-02"
}
2:{
"impressions":478
"taps":22
"installs":13
"newDownloads":11
"redownloads":2
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.046
"avgCPA":{
"amount":"2.9746"
"currency":"USD"
}
"avgCPT":{
"amount":"1.7577"
"currency":"USD"
}
"avgCPM":{
"amount":"80.8996"
"currency":"USD"
}
"localSpend":{
"amount":"38.67"
"currency":"USD"
}
"conversionRate":0.5909
"date":"2022-04-03"
}
3:{
"impressions":1014
"taps":18
"installs":6
"newDownloads":5
"redownloads":1
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.0178
"avgCPA":{
"amount":"4.63"
"currency":"USD"
}
"avgCPT":{
"amount":"1.5433"
"currency":"USD"
}
"avgCPM":{
"amount":"27.3964"
"currency":"USD"
}
"localSpend":{
"amount":"27.78"
"currency":"USD"
}
"conversionRate":0.3333
"date":"2022-04-04"
}
4:{
"impressions":727
"taps":14
"installs":5
"newDownloads":5
"redownloads":0
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.0193
"avgCPA":{
"amount":"3.356"
"currency":"USD"
}
"avgCPT":{
"amount":"1.1986"
"currency":"USD"
}
"avgCPM":{
"amount":"23.0812"
"currency":"USD"
}
"localSpend":{
"amount":"16.78"
"currency":"USD"
}
"conversionRate":0.3571
"date":"2022-04-05"
}
5:{
"impressions":272
"taps":19
"installs":9
"newDownloads":8
"redownloads":1
"latOnInstalls":1
"latOffInstalls":2
"ttr":0.0699
"avgCPA":{
"amount":"3.8856"
"currency":"USD"
}
"avgCPT":{
"amount":"1.8405"
"currency":"USD"
}
"avgCPM":{
"amount":"128.5662"
"currency":"USD"
}
"localSpend":{
"amount":"34.97"
"currency":"USD"
}
"conversionRate":0.4737
"date":"2022-04-06"
}
6:{
"impressions":365
"taps":17
"installs":5
"newDownloads":5
"redownloads":0
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.0466
"avgCPA":{
"amount":"6.226"
"currency":"USD"
}
"avgCPT":{
"amount":"1.8312"
"currency":"USD"
}
"avgCPM":{
"amount":"85.2877"
"currency":"USD"
}
"localSpend":{
"amount":"31.13"
"currency":"USD"
}
"conversionRate":0.2941
"date":"2022-04-07"
}
7:{
"impressions":622
"taps":17
"installs":3
"newDownloads":3
"redownloads":0
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.0273
"avgCPA":{
"amount":"8.7567"
"currency":"USD"
}
"avgCPT":{
"amount":"1.5453"
"currency":"USD"
}
"avgCPM":{
"amount":"42.2347"
"currency":"USD"
}
"localSpend":{
"amount":"26.27"
"currency":"USD"
}
"conversionRate":0.1765
"date":"2022-04-08"
}
8:{
"impressions":498
"taps":20
"installs":8
"newDownloads":4
"redownloads":4
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.0402
"avgCPA":{
"amount":"4.1525"
"currency":"USD"
}
"avgCPT":{
"amount":"1.661"
"currency":"USD"
}
"avgCPM":{
"amount":"66.7068"
"currency":"USD"
}
"localSpend":{
"amount":"33.22"
"currency":"USD"
}
"conversionRate":0.4
"date":"2022-04-09"
}
9:{
"impressions":596
"taps":19
"installs":4
"newDownloads":3
"redownloads":1
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.0319
"avgCPA":{
"amount":"7.4575"
"currency":"USD"
}
"avgCPT":{
"amount":"1.57"
"currency":"USD"
}
"avgCPM":{
"amount":"50.0503"
"currency":"USD"
}
"localSpend":{
"amount":"29.83"
"currency":"USD"
}
"conversionRate":0.2105
"date":"2022-04-10"
}
10:{
"impressions":325
"taps":10
"installs":4
"newDownloads":4
"redownloads":0
"latOnInstalls":0
"latOffInstalls":0
"ttr":0.0308
"avgCPA":{
"amount":"4.565"
"currency":"USD"
}
"avgCPT":{
"amount":"1.826"
"currency":"USD"
}
"avgCPM":{
"amount":"56.1846"
"currency":"USD"
}
"localSpend":{
"amount":"18.26"
"currency":"USD"
}
"conversionRate":0.4
"date":"2022-04-11"
}
]
"metadata":{
"campaignId":1023861985
"campaignName":"Mar_2022"
"deleted":false
"campaignStatus":"ENABLED"
"app":{
"appName":"xxxx"
"adamId":1581963198
}
"servingStatus":"RUNNING"
"servingStateReasons":NULL
"countriesOrRegions":[
0:"ID"
]
"modificationTime":"2022-03-31T19:14:12.761"
"totalBudget":{
"amount":"960"
"currency":"USD"
}
"dailyBudget":{
"amount":"32"
"currency":"USD"
}
"displayStatus":"RUNNING"
"supplySources":[
0:"APPSTORE_SEARCH_RESULTS"
]
"adChannelType":"SEARCH"
"orgId":3718600
"countryOrRegionServingStateReasons":{}
"billingEvent":"TAPS"
"countryOrRegion":"ID"
}
}

The dictionary in granulity and metadata contains other list. However when I tried running

for s in campaign:
 print(s)

It returns others only. When I tried

for s in campaign:
  for x in s:
    print (x)

It returns o,t,h,e,r

How do I access the values to get it into a dataframe?

Solution

Please share the full API response in order to get the right solution. But so in general the pandas json_normalize() function can work.

import pandas as pd

pd.json_normalize(response_dictionary)

Answered By - dkantor