['{"data":{"attributes":{"title":"Contract 1","AnnualValue":0},"id":1,"type":"contract"}}',
 '{"data":{"attributes":{"title":"Contract 2","AnnualValue":0},"id":2,"type":"contract"}}',
 '{"data":{"attributes":{"title":"Contract 3","AnnualValue":0},"id":3,"type":"contract"}}']

I have the above data frame and need to 'pull' the 'id' value. tried converting to json etc but struggling to get the value. Is anyone able to point me in the right direction - 5 hours of googling has just led me up the garden path!!

Thanks

CodePudding user response：

import json

json_list = [
    '{"data":{"attributes":{"title":"Contract 1","AnnualValue":0},"id":1,"type":"contract"}}',
    '{"data":{"attributes":{"title":"Contract 2","AnnualValue":0},"id":2,"type":"contract"}}',
    '{"data":{"attributes":{"title":"Contract 3","AnnualValue":0},"id":3,"type":"contract"}}'
]

ids = [
    json.loads(json_body)["data"]["id"]
    for json_body in json_list
]

[1, 2, 3]

CodePudding user response：

This is how you can display that data in a dataframe:

import pandas as pd
import json

data_list = ['{"data":{"attributes":{"title":"Contract 1","AnnualValue":0},"id":1,"type":"contract"}}',
 '{"data":{"attributes":{"title":"Contract 2","AnnualValue":0},"id":2,"type":"contract"}}',
 '{"data":{"attributes":{"title":"Contract 3","AnnualValue":0},"id":3,"type":"contract"}}']

new_data_list = []
for x in data_list:
    new_data_list.append((json.loads(x)['data']['id'], json.loads(x)['data']['type'], json.loads(x)['data']['attributes']['title'], json.loads(x)['data']['attributes']['AnnualValue']))
df = pd.DataFrame(new_data_list, columns = ['Id', 'Type', 'Title', 'Annual Value'])
print(df)

Which returns: