Python Forum
HELP! Importing json file into csv into jupyter notebook
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
HELP! Importing json file into csv into jupyter notebook
#1
Im trying to access data for Magic the gathering to process it in a pandas dataframe using Jupyter Notebook.

The data is downloaded from this website, in json. file type:
https://mtgjson.com/downloads/all-sets/

However, when I read the data in jupyter notebook, the dataframe only displays a small portion of the data.
Can someone please tell me where I'm going wrong?

Notebook print screen:
[Image: dateposted-public]
Reply
#2
I couldn't read this file with pandas.read_csv.
I can however read it with the json class.

Note that the json file has nested json within.
This is why Pandas couldn't read it.
these can be json loaded individually

This is the first json file from the URL you supplied

ReadJson.py
import json
from CreateDict import CreateDict
import os


# Set starting directory to script directory
os.chdir(os.path.abspath(os.path.dirname(__file__)))

filename = ".../data/json/TSR.json"

datadict = {}

with open(filename) as fp:
    datadict = json.load(fp)

CreateDict().display_dict(datadict)
Note: I used display_dict from a class (which I have included) this must be in same directory as above script, and named CreateDict.py
CreateDict.py
#  Author: Larz60+ Nov 22, 2018
import os


class CreateDict:
    def __init__(self):
        os.chdir(os.path.abspath(os.path.dirname(__file__)))

    def new_dict(self, dictname):
        setattr(self, dictname, {})

    def add_node(self, parent, nodename):
        node = parent[nodename] = {}
        return node

    def add_cell(self, nodename, cellname, value):
        cell =  nodename[cellname] = value
        return cell

    def display_dict(self, dictname, level=0):
        indent = " " * (4 * level)
        for key, value in dictname.items():
            if isinstance(value, dict):
                print(f'\n{indent}{key}')
                level += 1
                self.display_dict(value, level)
            else:
                print(f'{indent}{key}: {value}')
            if level > 0:
                level -= 1
results:
Output:
meta date: 2021-01-20 version: 5.1.0+20210120 data baseSetSize: 4 cards: [{'artist': 'Todd Lockwood', 'availability': ['paper'], 'borderColor': 'black', 'colorIdentity': ['W'], 'colors': ['W'], 'convertedManaCost': 1.0, 'edhrecRank': 46, 'foreignData': [], 'frameVersion': '1997', 'hasFoil': True, 'hasNonFoil': True, 'identifiers': {'mtgjsonV4Id': '02730c76-3b51-56c4-9cd1-d42b41b4e1b8', 'scryfallId': '2ee3e42e-120c-468a-988d-4441b6025bbe', 'scryfallIllustrationId': '521e5f89-b059-432f-b515-c207cccf3796', 'scryfallOracleId': 'd683d985-9888-4d21-8b5f-69e69ce4a03b'}, 'isReprint': True, 'isStarter': True, 'layout': 'normal', 'legalities': {'commander': 'Legal', 'duel': 'Legal', 'legacy': 'Legal', 'modern': 'Legal', 'vintage': 'Legal'}, 'manaCost': '{W}', 'name': 'Path to Exile', 'number': '299', 'printings': ['2XM', 'ARC', 'CMA', 'CMD', 'CON', 'DDI', 'E02', 'F15', 'JMP', 'MB1', 'MD1', 'MM3', 'MMA', 'PF20', 'PRM', 'PWP09', 'SS2', 'TSR'], 'purchaseUrls': {}, 'rarity': 'mythic', 'rulings': [{'date': '2020-08-07', 'text': 'If the target creature is an illegal target by the time Path to Exile tries to resolve, the spell won’t resolve. The creature’s controller won’t search for a basic land card.'}, {'date': '2020-08-07', 'text': 'The controller of the exiled creature isn’t required to search their library for a basic land. If that player doesn’t, the player won’t shuffle their library.'}], 'setCode': 'TSR', 'subtypes': [], 'supertypes': [], 'text': 'Exile target creature. Its controller may search their library for a basic land card, put that card onto the battlefield tapped, then shuffle their library.', 'type': 'Instant', 'types': ['Instant'], 'uuid': '36e7671e-ed7c-518f-a75c-5ae3eaef1f65'}, {'artist': 'Thomas M. Baxa', 'availability': ['paper'], 'borderColor': 'black', 'colorIdentity': ['B'], 'colors': ['B'], 'convertedManaCost': 3.0, 'edhrecRank': 6927, 'foreignData': [], 'frameVersion': '1997', 'hasAlternativeDeckLimit': True, 'hasFoil': True, 'hasNonFoil': True, 'identifiers': {'mtgjsonV4Id': '8ca52d74-bed3-5f9b-99e3-16099b5d6fa0', 'scryfallId': '28de0281-0370-44df-9ee0-6e1bb9925a92', 'scryfallIllustrationId': '76fc4aad-fff6-4814-8567-fe796b04d013', 'scryfallOracleId': '104ea189-14cd-420f-afdc-57b0f827ab8e'}, 'isReprint': True, 'isStarter': True, 'layout': 'normal', 'legalities': {'commander': 'Legal', 'duel': 'Legal', 'legacy': 'Legal', 'modern': 'Legal', 'pauper': 'Legal', 'penny': 'Legal', 'vintage': 'Legal'}, 'manaCost': '{1}{B}{B}', 'name': 'Relentless Rats', 'number': '329', 'power': '2', 'printings': ['10E', '5DN', 'A25', 'M10', 'M11', 'PLIST', 'PURL', 'TSR'], 'purchaseUrls': {}, 'rarity': 'mythic', 'rulings': [{'date': '2018-03-16', 'text': 'The second ability of Relentless Rats lets you ignore the “four-of” rule. It doesn’t let you ignore format legality. For example, during a Masters 25 Limited event, you can’t add Relentless Rats from your personal collection.'}], 'setCode': 'TSR', 'subtypes': ['Rat'], 'supertypes': [], 'text': 'Relentless Rats gets +1/+1 for each other creature on the battlefield named Relentless Rats.\nA deck can have any number of cards named Relentless Rats.', 'toughness': '2', 'type': 'Creature — Rat', 'types': ['Creature'], 'uuid': '69c11c4c-3876-58ed-b948-811ea4341dfa'}, {'artist': 'Mark Zug', 'availability': ['paper'], 'borderColor': 'black', 'colorIdentity': [], 'colors': [], 'convertedManaCost': 0.0, 'edhrecRank': 6628, 'foreignData': [], 'frameVersion': '1997', 'hasFoil': True, 'hasNonFoil': True, 'identifiers': {'mtgjsonV4Id': '25ed657c-93f6-5500-96ff-5f46f9cfa3cb', 'scryfallId': '25674e46-e15b-4fc0-9813-39b4e1c23de4', 'scryfallIllustrationId': 'b51bb61e-bad7-46ca-abd0-c5322c43ecdb', 'scryfallOracleId': '74027ba4-a6ba-4696-baf1-60ef0b27ee0a'}, 'isReprint': True, 'isStarter': True, 'layout': 'normal', 'legalities': {'commander': 'Legal', 'duel': 'Legal', 'legacy': 'Legal', 'modern': 'Legal', 'vintage': 'Restricted'}, 'manaCost': '{X}{X}', 'name': 'Chalice of the Void', 'number': '390', 'printings': ['A25', 'J19', 'MMA', 'MPS', 'MRD', 'PRM', 'TSR'], 'purchaseUrls': {}, 'rarity': 'mythic', 'rulings': [{'date': '2018-03-16', 'text': 'A mana cost of {X}{X} means that you pay twice X. If you want X to be 3, you pay {6} to cast Chalice of the Void.'}, {'date': '2018-03-16', 'text': 'The number of counters on Chalice of the Void matters only at the time the spell is cast. Changing the number of charge counters on Chalice of the Void after a spell has been cast won’t change whether the ability counters the spell.'}, {'date': '2018-03-16', 'text': 'If there are zero charge counters on Chalice of the Void, it counters each spell with a converted mana cost of 0. This includes face-down creature spells cast with morph’s alternative cost.'}, {'date': '2018-03-16', 'text': 'Chalice of the Void has to be on the battlefield at the end of casting a spell for the ability to trigger. If you sacrifice Chalice of the Void as a cost to cast a spell, its ability can’t trigger. However, if it leaves the battlefield once its ability has triggered, that ability will still counter the spell.'}], 'setCode': 'TSR', 'subtypes': [], 'supertypes': [], 'text': 'Chalice of the Void enters the battlefield with X charge counters on it.\nWhenever a player casts a spell with converted mana cost equal to the number of charge counters on Chalice of the Void, counter that spell.', 'type': 'Artifact', 'types': ['Artifact'], 'uuid': '6468d7f6-aad6-54fe-bc3f-086240bd93cf'}, {'artist': 'Christopher Rush', 'availability': ['paper'], 'borderColor': 'black', 'colorIdentity': [], 'colors': [], 'convertedManaCost': 0.0, 'edhrecRank': 3936, 'foreignData': [], 'frameVersion': '1997', 'hasFoil': True, 'hasNonFoil': False, 'identifiers': {'mtgjsonV4Id': 'c593de9e-4bd7-5bce-a4fa-7fe90595bcc7', 'scryfallId': '7466be25-5d41-408d-90eb-6de47ea11484', 'scryfallIllustrationId': '31b5d531-26e6-4ab4-97e1-d047dd635ef8', 'scryfallOracleId': '04cf02dc-f053-414e-87d8-1537f25bcbf4'}, 'isPromo': True, 'isReprint': True, 'isStarter': True, 'keywords': ['Suspend'], 'layout': 'normal', 'legalities': {'commander': 'Legal', 'duel': 'Legal', 'legacy': 'Legal', 'modern': 'Legal', 'vintage': 'Legal'}, 'name': 'Lotus Bloom', 'number': '411', 'printings': ['MMA', 'PLIST', 'PRM', 'PTSP', 'TSP', 'TSR'], 'promoTypes': ['release'], 'purchaseUrls': {}, 'rarity': 'rare', 'rulings': [{'date': '2013-06-07', 'text': 'Exiling a card with suspend isn’t casting that card. This action doesn’t use the stack and can’t be responded to.'}, {'date': '2013-06-07', 'text': 'If the spell requires any targets, those targets are chosen when the spell is finally cast, not when it’s exiled.'}, {'date': '2013-06-07', 'text': 'If the first triggered ability of suspend (the one that removes time counters) is countered, no time counter is removed. The ability will trigger again during the card’s owner’s next upkeep.'}, {'date': '2013-06-07', 'text': 'When the last time counter is removed, the second triggered ability of suspend will trigger. It doesn’t matter why the last time counter was removed or what effect removed it.'}, {'date': '2013-06-07', 'text': 'You can exile a card in your hand using suspend any time you could cast that card. Consider its card type, any effect that affects when you could cast it (such as flash) and any other effects that could stop you from casting it (such as Meddling Mage’s effect) to determine if and when you can do this. Whether or not you could actually complete all steps in casting the card is irrelevant. For example, you can exile a card with suspend that has no mana cost or requires a target even if no legal targets are available at that time.'}, {'date': '2013-06-07', 'text': 'As the second triggered ability resolves, you must cast the card if able. Timing restrictions based on the card’s type are ignored.'}, {'date': '2013-06-07', 'text': 'If you can’t cast the card, perhaps because there are no legal targets available, it remains exiled with no time counters on it, and it’s no longer suspended.'}, {'date': '2013-06-07', 'text': 'If the spell has any mandatory additional costs, you must pay those if able. However, if an additional cost includes a mana payment, you are forced to pay that cost only if there’s enough mana in your mana pool at the time you cast the spell. You aren’t forced to activate any mana abilities, although you may do so if you wish.'}, {'date': '2013-06-07', 'text': 'A creature cast using suspend will enter the battlefield with haste. It will have haste until another player gains control of it (or, in some rare cases, gains control of the creature spell itself).'}, {'date': '2013-06-07', 'text': 'If the second triggered ability of suspend (the one that lets you cast the card) is countered, the card can’t be cast. It remains exiled with no time counters on it, and it’s no longer suspended.'}, {'date': '2016-09-20', 'text': 'This has no mana cost, which means its mana cost can’t be paid for effects such as replicate from Djinn Illuminatus or flashback from Snapcaster Mage.'}], 'setCode': 'TSR', 'subtypes': [], 'supertypes': [], 'text': 'Suspend 3—{0} (Rather than cast this card from your hand, pay {0} and exile it with three time counters on it. At the beginning of your upkeep, remove a time counter. When the last is removed, cast it without paying its mana cost.)\n{T}, Sacrifice Lotus Bloom: Add three mana of any one color.', 'type': 'Artifact', 'types': ['Artifact'], 'uuid': '9940c435-2bf3-50e8-9e9b-ce3651bfb6f6'}] code: TSR isFoilOnly: False isOnlineOnly: False isPartialPreview: True keyruneCode: TSR name: Time Spiral Remastered releaseDate: 2021-03-19 tcgplayerGroupId: 2772 tokens: [] totalSetSize: 4 translations type: masters
Reply
#3
There is pandas.json_normalize to help with task like this.
If doc dos help search eg Normalize nested JSON objects with pandas.
Larz60+ likes this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Setting up and integrating Jupyter Notebook development environment with VSC Drone4four 3 579 Sep-12-2022, 09:18 AM
Last Post: Drone4four
  Jupyter:On my computer, where csv file is? Leloup 2 805 Mar-22-2022, 04:39 PM
Last Post: Leloup
Thumbs Up can't access data from URL in pandas/jupyter notebook aaanoushka 1 933 Feb-13-2022, 01:19 PM
Last Post: jefsummers
Exclamation Jupyter Notebook - Help Needed! eyadfr 1 1,018 Jan-26-2022, 06:42 PM
Last Post: jefsummers
  Help needed with Jupyter Notebook eyadfr 4 1,047 Jan-04-2022, 08:20 PM
Last Post: snippsat
  How can draw a real-time marker on map using folium/leaflet Jupyter notebook C3PO 0 1,736 Dec-22-2020, 07:04 PM
Last Post: C3PO
  Python Custom Module not working in Jupyter Notebook with Pandas fid 0 1,463 Jul-04-2020, 11:05 AM
Last Post: fid
  JSON file Loading issue punna111 4 6,608 Jun-29-2020, 08:07 AM
Last Post: buran
  Indirectlty convert string to float in JSON file WBPYTHON 6 3,803 May-06-2020, 12:09 PM
Last Post: WBPYTHON
  Help batch converting .json chosen file to MySQL BrandonKastning 2 1,647 Mar-14-2020, 09:19 PM
Last Post: BrandonKastning

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020