Convert json dict and list

Question

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

are you looking to convert [JSON to pandas DataFrame](https://stackoverflow.com/q/21104592/10197418)? — FObersteiner, Dec 29 '21 at 17:19

RomanG · Accepted Answer · 2021-12-29T19:46:54.030

I would try this:

I would make sure that I am using one of the latest python versions, because of sort in dictionaries or data source structure can be changed, better I will 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

If dictionaries can be randomly sorted (not ordered) or I want to make sure that it is sorted, so I would try an example below:

### Another sort

keys = ["AdrBal1in100KCnt", "AdrActCnt", "time"]

def value(obj, keys):
    for dictis in obj["data"]:
        listed = [dictis[x] for x in keys]
        yield listed

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

print(df)

Output:

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

addited, using indexes for sort. – RomanG Dec 29 '21 at 19:29 — RomanG, Dec 29 '21 at 19:29

Convert json dict and list

1 Answers1