如何使用python将多个JSON字符串呈现为dataframe？

Question

我正在尝试从Spotify歌曲使用他们的API列表中获取使用他们的API数据。在获得我的API令牌之后，我对API发出请求，下面的代码如下所示

    header2 ={
    'Authorization':'Bearer '+token.json()['access_token']    
}
param2 = {
    'ids' : '7C5UlMz9b51rQI66lpEiSo,6ocbgoVGwYJhOv1GgI9NsF,22vgEDb5hykfaTwLuskFGD'

}
response = requests.get(endpoint,headers=header2,params=param2)
response.status_code
response.json()

这给了我这个回应

{'audio_features': [{'danceability': 0.297,
   'energy': 0.827,
   'key': 5,
   'loudness': -4.748,
   'mode': 1,
   'speechiness': 0.0735,
   'acousticness': 0.12,
   'instrumentalness': 0.000123,
   'liveness': 0.347,
   'valence': 0.467,
   'tempo': 147.232,
   'type': 'audio_features',
   'id': '7C5UlMz9b51rQI66lpEiSo',
   'uri': 'spotify:track:7C5UlMz9b51rQI66lpEiSo',
   'track_href': 'https://api.spotify.com/v1/tracks/7C5UlMz9b51rQI66lpEiSo',
   'analysis_url': 'https://api.spotify.com/v1/audio-analysis/7C5UlMz9b51rQI66lpEiSo',
   'duration_ms': 221773,
   'time_signature': 4},
  {'danceability': 0.778,
   'energy': 0.317,
   'key': 1,
   'loudness': -10.732,
   'mode': 0,
   'speechiness': 0.334,
   'acousticness': 0.592,
   'instrumentalness': 0,
   'liveness': 0.0881,
   'valence': 0.327,
   'tempo': 140.048,
   'type': 'audio_features',
   'id': '6ocbgoVGwYJhOv1GgI9NsF',
   'uri': 'spotify:track:6ocbgoVGwYJhOv1GgI9NsF',
   'track_href': 'https://api.spotify.com/v1/tracks/6ocbgoVGwYJhOv1GgI9NsF',
   'analysis_url': 'https://api.spotify.com/v1/audio-analysis/6ocbgoVGwYJhOv1GgI9NsF',
   'duration_ms': 178627,
   'time_signature': 4},
  {'danceability': 0.842,
   'energy': 0.734,
   'key': 1,
   'loudness': -5.065,
   'mode': 0,
   'speechiness': 0.0588,
   'acousticness': 0.0427,
   'instrumentalness': 0,
   'liveness': 0.106,
   'valence': 0.952,
   'tempo': 137.958,
   'type': 'audio_features',
   'id': '22vgEDb5hykfaTwLuskFGD',
   'uri': 'spotify:track:22vgEDb5hykfaTwLuskFGD',
   'track_href': 'https://api.spotify.com/v1/tracks/22vgEDb5hykfaTwLuskFGD',
   'analysis_url': 'https://api.spotify.com/v1/audio-analysis/22vgEDb5hykfaTwLuskFGD',
   'duration_ms': 181027,
   'time_signature': 4}]}

我现在真的想把这三个JSON记录转换成一个表，表中的列是当前作为每个轨道的标签出现的。如何创建这样的表？

我在使用json.dumps()时遇到了错误，只有在使用以下命令时才能获得每个JSON记录的字符串：

df = pd.DataFrame(data=response.json(),)
print(df)

Answer 1

您可以直接使用pandas通过使用json_normalize来完成这一任务。

from pandas.io.json import json_normalize

json_normalize(data, record_path='audio_features')

   acousticness                                       analysis_url  ...                                   uri  valence
0        0.1200  https://api.spotify.com/v1/audio-analysis/7C5U...  ...  spotify:track:7C5UlMz9b51rQI66lpEiSo    0.467
1        0.5920  https://api.spotify.com/v1/audio-analysis/6ocb...  ...  spotify:track:6ocbgoVGwYJhOv1GgI9NsF    0.327
2        0.0427  https://api.spotify.com/v1/audio-analysis/22vg...  ...  spotify:track:22vgEDb5hykfaTwLuskFGD    0.952

在pandas>=1.0.0中，它只是from pandas import json_normalize