How can i export correctly my json to a dataframe in pandas-CodePudding

can someone help me please? i'm kinda new with python and pandas

I'm trying to open a json into a pandas dataframe i'm using

df3 = pd.read_json('/content/Top100.json', orient='records')
df3

But i'm getting my import incorrectly, it's showing the whole data from a record as a single cell like this:

0
{ "danceability" : 0.615, "energy" : 0.534, "key" : 9, "loudness" : -6.719, "mode" : 0, "speechiness" : 0.0386, "acousticness" : 0.106, "instrumentalness" : 0.0000176, "liveness" : 0.0607, "valence" : 0.193, "tempo" : 135.917, "type" : "audio_features", "id" : "1R0a2iXumgCiFb7HEZ7gUE", "uri" : "spotify:track:1R0a2iXumgCiFb7HEZ7gUE", "track_href" : "https://api.spotify.com/v1/tracks/1R0a2iXumgCiFb7HEZ7gUE", "analysis_url" : "https://api.spotify.com/v1/audio-analysis/1R0a2iXumgCiFb7HEZ7gUE", "duration_ms" : 236413, "time_signature" : 4}

{ "danceability" : 0.532, "energy" : 0.623, "key" : 5, "loudness" : -9.208, "mode" : 1, "speechiness" : 0.0331, "acousticness" : 0.538, "instrumentalness" : 0.0000728, "liveness" : 0.0925, "valence" : 0.403, "tempo" : 89.937, "type" : "audio_features", "id" : "3hUxzQpSfdDqwM3ZTFQY0K", "uri" : "spotify:track:3hUxzQpSfdDqwM3ZTFQY0K", "track_href" : "https://api.spotify.com/v1/tracks/3hUxzQpSfdDqwM3ZTFQY0K", "analysis_url" : "https://api.spotify.com/v1/audio-analysis/3hUxzQpSfdDqwM3ZTFQY0K", "duration_ms" : 261923, "time_signature" : 4}

instead of

danceability	energy	key
0.615	0.534	9

Here's a piece of my json

[{"0":"{\n "danceability" : 0.615,\n "energy" : 0.534,\n "key" : 9,\n "loudness" : -6.719,\n "mode" : 0,\n "speechiness" : 0.0386,\n "acousticness" : 0.106,\n "instrumentalness" : 0.0000176,\n "liveness" : 0.0607,\n "valence" : 0.193,\n "tempo" : 135.917,\n "type" : "audio_features",\n "id" : "1R0a2iXumgCiFb7HEZ7gUE",\n "uri" : "spotify:track:1R0a2iXumgCiFb7HEZ7gUE",\n "track_href" : "https://api.spotify.com/v1/tracks/1R0a2iXumgCiFb7HEZ7gUE",\n "analysis_url" : "https://api.spotify.com/v1/audio-analysis/1R0a2iXumgCiFb7HEZ7gUE",\n "duration_ms" : 236413,\n "time_signature" : 4\n}"},{"0":"{\n "danceability" : 0.532,\n "energy" : 0.623,\n "key" : 5,\n "loudness" : -9.208,\n "mode" : 1,\n "speechiness" : 0.0331,\n "acousticness" : 0.538,\n "instrumentalness" : 0.0000728,\n "liveness" : 0.0925,\n "valence" : 0.403,\n "tempo" : 89.937,\n "type" : "audio_features",\n "id" : "3hUxzQpSfdDqwM3ZTFQY0K",\n "uri" : "spotify:track:3hUxzQpSfdDqwM3ZTFQY0K",\n "track_href" : "https://api.spotify.com/v1/tracks/3hUxzQpSfdDqwM3ZTFQY0K",\n "analysis_url" : "https://api.spotify.com/v1/audio-analysis/3hUxzQpSfdDqwM3ZTFQY0K",\n "duration_ms" : 261923,\n "time_signature" : 4\n}"},{"0":"{\n "danceability" : 0.753,\n "energy" : 0.678,\n "key" : 5,\n "loudness" : -5.421,\n "mode" : 1,\n "speechiness" : 0.0644,\n "acousticness" : 0.0850,\n "instrumentalness" : 0.00000164,\n "liveness" : 0.130,\n "valence" : 0.583,\n "tempo" : 96.006,\n "type" : "audio_features",\n "id" : "1p80LdxRV74UKvL8gnD7ky",\n "uri" : "spotify:track:1p80LdxRV74UKvL8gnD7ky",\n "track_href" : "https://api.spotify.com/v1/tracks/1p80LdxRV74UKvL8gnD7ky",\n "analysis_url" : "https://api.spotify.com/v1/audio-analysis/1p80LdxRV74UKvL8gnD7ky",\n "duration_ms" : 231827,\n "time_signature" : 4\n}"},{"0":"{\n "danceability" : 0.552,\n "energy" : 0.702,\n "key" : 9,\n "loudness" : -5.707,\n "mode" : 1,\n "speechiness" : 0.157,\n "acousticness" : 0.117,\n "instrumentalness" : 0.0000206,\n "liveness" : 0.105,\n "valence" : 0.564,\n "tempo" : 169.994,\n "type" : "audio_features",\n "id" : "1BxfuPKGuaTgP7aM0Bbdwr",\n "uri" : "spotify:track:1BxfuPKGuaTgP7aM0Bbdwr",\n "track_href" : "https://api.spotify.com/v1/tracks/1BxfuPKGuaTgP7aM0Bbdwr",\n "analysis_url" : "https://api.spotify.com/v1/audio-analysis/1BxfuPKGuaTgP7aM0Bbdwr",\n "duration_ms" : 178427,\n "time_signature" : 4\n}"}

i'd be pretty thankful if someone could help me with this :)

CodePudding user response：

I can read it correctly:

stri = """{ "danceability" : 0.615, "energy" : 0.534, "key" : 9, "loudness" : -6.719, "mode" : 0, "speechiness" : 0.0386, "acousticness" : 0.106, "instrumentalness" : 0.0000176, "liveness" : 0.0607, "valence" : 0.193, "tempo" : 135.917, "type" : "audio_features", "id" : "1R0a2iXumgCiFb7HEZ7gUE", "uri" : "spotify:track:1R0a2iXumgCiFb7HEZ7gUE", "track_href" : "https://api.spotify.com/v1/tracks/1R0a2iXumgCiFb7HEZ7gUE", "analysis_url" : "https://api.spotify.com/v1/audio-analysis/1R0a2iXumgCiFb7HEZ7gUE", "duration_ms" : 236413, "time_signature" : 4},{ "danceability" : 0.532, "energy" : 0.623, "key" : 5, "loudness" : -9.208, "mode" : 1, "speechiness" : 0.0331, "acousticness" : 0.538, "instrumentalness" : 0.0000728, "liveness" : 0.0925, "valence" : 0.403, "tempo" : 89.937, "type" : "audio_features", "id" : "3hUxzQpSfdDqwM3ZTFQY0K", "uri" : "spotify:track:3hUxzQpSfdDqwM3ZTFQY0K", "track_href" : "https://api.spotify.com/v1/tracks/3hUxzQpSfdDqwM3ZTFQY0K", "analysis_url" : "https://api.spotify.com/v1/audio-analysis/3hUxzQpSfdDqwM3ZTFQY0K", "duration_ms" : 261923, "time_signature" : 4}"""
pd.read_json(stri, lines=True)

Output:

CodePudding user response：

It looks like your json file might have an extra key layer that is preventing pandas parsing the layer you want. If you don't want to/can't edit the json file, this should work as a quick workaround:

temp = pd.read_json("my.json", orient='columns')
# Get only the 0 column as a list of dictionaries
j = temp[0].to_list()
# Generate the dataframe from the list of dictionaries
df = pd.DataFrame(j)

(If this doesn't work, or in the future, please post a sample of your json data to make it easier for people to help you.)

CodePudding user response：

hi i hope to help you :

i should see your file to help you but use this link

or try this run this :

import pandas as pd
df = pd.json_normalize(json.loads('/content/Top100.json')) 
print(df)