I'm trying to flatten a json database into pandas dataframe and as it's the first time I'm dealing with json format, I can't do what I want. The database is located here https://mtgjson.com/downloads/all-files/#allprices and according to the model, the structure is like this :
{
"0120a941-9cfb-50b5-b5e4-4e0c7bd32410": {
"mtgo": {
"cardhoarder": {
"currency": "USD",
"retail": {
"foil": {
..., // more rows
"2020-04-21": 0.02
},
"normal": {
..., // more rows
"2020-04-21": 0.02
}
}
},
},
"paper": {
"cardkingdom" : {
"buylist": {
"foil": {
..., // more rows
"2020-04-21": 0.6
},
"normal": {
..., // more rows
"2020-04-21": 0.01
}
},
"currency": "USD",
"retail": {
"foil": {
..., // more rows
"2020-04-21": 0.12
},
"normal": {
..., // more rows
"2020-04-21": 0.02
}
}
},
"cardmarket": {
"currency": "EUR",
"retail": {
"foil": {
..., // more rows
"2020-04-21": 0.12
},
"normal": {
..., // more rows
"2020-04-21": 0.02
}
}
},
"tcgplayer": {
"currency": "USD",
"retail": {
"foil": {
..., // more rows
"2020-04-21": 0.12
},
"normal": {
..., // more rows
"2020-04-21": 0.02
}
}
}
}
}
}
When I look into the json file I have this :
{"meta": {"date": "2021-11-07", "version": "5.1.0+20211107"}, "data": {"00010d56-fe38-5e35-8aed-518019aa36a5": {"paper": {"cardkingdom": {"buylist": {"foil.....
When I do the basic pd.read_json('AllPrices.json') I got this
| meta | data | |
|---|---|---|
| date | 2021-11-07 | NaN |
| version | 5.1.0+20211107 | NaN |
| 00010d56-fe38-5e35-8aed-518019aa36a5 | NaN | {'paper': {'cardkingdom': {'buylist': {'foil':... |
| 0001e0d0-2dcd-5640-aadc-a84765cf5fc9 | NaN | {'paper': {'cardkingdom': {'buylist': {'normal... |
So I did some research and found the json_normalize and wrote this piece of code :
with open('AllPrices.json','r') as f:
data = json.loads(f.read())
pd.json_normalize(data, errors='ignore')
This did the job by flattening the json database but I ended with one row and 31 millions columns. What I want is only one information in this database that is the uuid and the cardmarket price of a normal paper card on the date I want like this :
| uuid | paper.cardmarket.retail.normal.2021-11-07 |
|---|---|
| 00010d56-fe38-5e35-8aed-518019aa36a5 | 0.5 |
| 0001e0d0-2dcd-5640-aadc-a84765cf5fc9 | 0.25 |
I played with the record_path = parameter and the meta = parameter but the best I did was not my expected table.
I tried record_path = ['data'] that give me only the uuid in one column.
Thanks for your help