How can I format this JSON data in such a way that I can use it?-CodePudding

So I am trying to format my DataFrame with Pandas but am now receiving: value DAtdJH8dKSvPAr5KxiRzPsQxX2DMrgps4mAKmjcvfni5 for path mintAddress. Must be list or null.

{
   "mintAddress":"DAtdJH8dKSvPAr5KxiRzPsQxX2DMrgps4mAKmjcvfni5",
   "owner":"HQc8axxhdu9jLfKtwcsmmGaF6LZPFgNjPAV1kThh3dew",
   "supply":1,
   "collection":"bracketx",
   "name":"bracketX Access Pass",
   "updateAuthority":"5oYxvfmd6cZHdgZ8Zq5tZNwXoGAhEGXAhzxhmTeDWvEh",
   "primarySaleHappened":1,
   "sellerFeeBasisPoints":500,
   "image":"https://bafybeihy6moevkepbwd57i2qjfbvm5co36isoimg3qha2rj5vf6jpjyyge.ipfs.nftstorage.link/0.gif?ext=gif",
   "externalUrl":"www.overtime.tv",
   "attributes":[
      {
         "trait_type":"Ticket",
         "value":"bracketX"
      }
   ],
   "properties":{
      "files":[
         {
            "uri":"https://bafybeihy6moevkepbwd57i2qjfbvm5co36isoimg3qha2rj5vf6jpjyyge.ipfs.nftstorage.link/0.gif?ext=gif",
            "type":"image/gif"
         }
      ],
      "category":"image",
      "creators":[
         {
            "address":"RRUMF9KYPcvNSmnicNMAFKx5wDYix3wjNa6bA7R6xqA",
            "share":4
         },
         {
            "address":"9DuZ5wa9nEFm8g4PQktqeJnm8Kv4C4svgUsEtJpZpH64",
            "share":48
         },
         {
            "address":"AB9iLG4WBjNZcmch3S8sn7R2XjTT5BbNxJ2xCoynwaxE",
            "share":48
         }
      ]
   }
}

This is the JSON I am using, loaded through requests using this function:

def listing(mint_address):
    api_url = 'https://api-mainnet.magiceden.dev/v2/tokens/'   mint_address
    r = requests.get(url=api_url)
    response = r.json()
    name = response['name']
    df = pd.json_normalize(response)


    # df.melt(id_vars=['owner, 'name])

    # df.head(10).style.format({"name": "${:20,.0f}",
    #                           "collection": "${:20,.0f}",
    #                           "owner": "${:20,.0f}"}) \
    #                  .hide_index()

    # display(df.tostring())
    print(df)
    print(df.to_string())
    # print(df.info()) 
listing('DAtdJH8dKSvPAr5KxiRzPsQxX2DMrgps4mAKmjcvfni5')

Now what I am doing is pulling the JSON off an API: https://api.magiceden.dev/. I Basically want to create a DataFrame that looks exactly like this:

#    Name                  Value
---  ------                --------------  
 0   mintAddress           Value    
 1   owner                 Value    
 2   supply                Value   
 3   collection            Value   
 4   name                  Value         
 5   primarySaleHappened   Value      
 6   sellerFeeBasisPoints  Value

The current DataFrame object however has 1x row en 14x columns. I just can't seem to figure out how to even get all of the data in a column in the first place.

I've today spend about 5 hours on this problem so I hope someone can enlighten me here, I almost feel as if the data from the API is somewhat corrupted and json_normalize is the culprit.

CodePudding user response：

Use a simple melt:

df = pd.json_normalize(response).melt()

Output:

>>> df
                variable                                              value
0            mintAddress       DAtdJH8dKSvPAr5KxiRzPsQxX2DMrgps4mAKmjcvfni5
1                  owner       HQc8axxhdu9jLfKtwcsmmGaF6LZPFgNjPAV1kThh3dew
2                 supply                                                  1
3             collection                                           bracketx
4                   name                               bracketX Access Pass
5        updateAuthority       5oYxvfmd6cZHdgZ8Zq5tZNwXoGAhEGXAhzxhmTeDWvEh
6    primarySaleHappened                                                  1
7   sellerFeeBasisPoints                                                500
8                  image  https://bafybeihy6moevkepbwd57i2qjfbvm5co36iso...
9            externalUrl                                    www.overtime.tv
10            attributes    [{'trait_type': 'Ticket', 'value': 'bracketX'}]
11      properties.files  [{'uri': 'https://bafybeihy6moevkepbwd57i2qjfb...
12   properties.category                                              image
13   properties.creators  [{'address': 'RRUMF9KYPcvNSmnicNMAFKx5wDYix3wj...

CodePudding user response：

you can try this: with no pandas

def listing(mint_address):
    api_url = 'https://api-mainnet.magiceden.dev/v2/tokens/'   mint_address
    r = requests.get(url=api_url)
    response = r.json()
    count=0
    print("#     ","name         ","value        ")
    print("-------","-------------","--------------")
    for i,v in response.items():
        count  =1
        print(count,i,v)

listing('DAtdJH8dKSvPAr5KxiRzPsQxX2DMrgps4mAKmjcvfni5')