在R中展平MTG JSON数据集并转换为按releaseDate排序的数据框

Got it, let's work through flattening that tricky MTGJSON nested structure and getting your sorted data frame sorted out! I’ve messed around with this dataset before, so I know those nested lists can feel like a maze. Here’s a step-by-step solution that should work:

1. Install & Load Required Packages #

First, make sure you have the necessary tools installed. If you haven’t already, run this to grab the packages we’ll need:

install.packages(c("jsonlite", "tidyr", "dplyr"))

Then load them into your R session:

library(jsonlite)
library(tidyr)
library(dplyr)

2. Read the Compressed JSON File #

Good news: you don’t need to manually unzip the file first—jsonlite::fromJSON can read directly from the zip archive. We’ll use the flatten parameter to handle the first layer of nested structures right away:

# Read the zipped JSON and flatten top-level nested fields
mtg_raw <- fromJSON("AllSets.json.zip", flatten = TRUE)

# The top level is a named list (each entry is a Magic set), so convert it to a data frame
# We add a `set_code` column to keep track of which set each entry comes from
mtg_sets <- bind_rows(mtg_raw, .id = "set_code")

3. Unnest the Nested Card Data #

Each set entry has a cards field that’s a nested list of all the cards in that set. We’ll use unnest to explode this into individual rows—one row per card, with all the set-level info attached:

# Flatten the cards column into individual rows
mtg_flattened <- mtg_sets %>%
  unnest(cols = c(cards), keep_empty = TRUE)  # keep_empty keeps sets with no cards (if any exist)

4. Sort by Release Date #

Finally, we’ll sort the data frame by releaseDate. Important: we’ll convert this field to a proper date format first—otherwise, R will sort it as a string, which can lead to weird ordering (like "2023-10" coming before "2023-01"):

# Convert releaseDate to date format and sort
mtg_sorted <- mtg_flattened %>%
  mutate(releaseDate = as.Date(releaseDate)) %>%
  arrange(releaseDate)

Bonus: Handling Deeply Nested Fields #

If you still have leftover nested fields (like card legalities or rulings), you can keep unnesting them as needed. For example, to flatten the legalities field:

mtg_sorted <- mtg_sorted %>%
  unnest(cols = c(legalities), keep_empty = TRUE)

Verify Your Result #

Check the output to make sure everything looks right:

# Preview the first 5 rows
head(mtg_sorted)

# Inspect the structure to confirm no more unwanted nesting
str(mtg_sorted)

内容的提问来源于stack exchange，提问作者Davide Lorino