Convert json dict and list-CodePudding

I have pulled JSON data from a URL. The result is a dictionary. How can I transform this dictionary to a dataframe so AdrActCnt and AdrBal1in100KCnt are columns, and the time is the index for each value.

Thanks in advance

{"data":[
{"asset":"btc","time":"2009-01-04T00:00:00.00000000Z","AdrActCnt":"10","AdrBal1in100KCnt":"145"},
{"asset":"btc","time":"2009-01-05T00:00:00.00000000Z","AdrActCnt":"30","AdrBal1in100KCnt":"152"},
{"asset":"btc","time":"2009-01-06T00:00:00.00000000Z","AdrActCnt":"50","AdrBal1in100KCnt":"136"}
]}

time----------------------------------------AdrActCnt---------AdrBal1in100KCnt
    
2009-01-04T00:00:00.00000000Z------------------10-------------------145
2009-01-05T00:00:00.00000000Z------------------30-------------------152
2009-01-06T00:00:00.00000000Z------------------50-------------------136

CodePudding user response：

Try this:

Make sure that you are using one of the latest python versions, because of sort in dictionaries or better add sorting for dictionary and list for this code.


import pandas as pd

obj = {"data":[
{"asset":"btc","time":"2009-01-04T00:00:00.00000000Z","AdrActCnt":"10","AdrBal1in100KCnt":"145"},
{"asset":"btc","time":"2009-01-05T00:00:00.00000000Z","AdrActCnt":"30","AdrBal1in100KCnt":"152"},
{"asset":"btc","time":"2009-01-06T00:00:00.00000000Z","AdrActCnt":"50","AdrBal1in100KCnt":"136"}
]}


def value(obj, keys):
    for dicit in obj["data"]:
        values = [
            value
            for key, value in dicit.items()
            if key in keys
        ]
        yield values

keys = ["time", "AdrActCnt", "AdrBal1in100KCnt"]
df = pd.DataFrame(
    data=[x for x in value(obj, keys)]
    , columns=keys
)

print(df)

Output:

                            time AdrActCnt AdrBal1in100KCnt
0  2009-01-04T00:00:00.00000000Z        10              145
1  2009-01-05T00:00:00.00000000Z        30              152
2  2009-01-06T00:00:00.00000000Z        50              136