将多级JSON/列表转换为data.frame或data.table的解决方案咨询

Hey there! Let's tackle this data reshaping problem you're facing with your Firebase player data. The error you're getting from tidyjson makes total sense—it expects JSON input, not a raw R list. But don't worry, we've got two solid solutions for you: one using the tidyverse (super intuitive for nested data) and another if you really want to stick with tidyjson.

Solution 1: Use Tidyverse (Recommended) #

This method works directly with your R list structure, no JSON conversion required. We'll use dplyr and tidyr to unnest the nested reactions and keep all player metadata attached to each reaction row.

Step-by-Step Code #

First, let's simulate your full player list (like what you downloaded from Firebase):

# Simulate your full player list
player_list <- list(
  Qwerty = list(
    age = "50", education = "nič", gender = "Muz", glasses = "Mam okuliare",
    nick = "Qwerty", psc = "08005",
    reactions = list(list(time = 584, x = 814,y = 1615), list(time = 443, x =23, y=32)),
    score = 532
  ),
  AnotherPlayer = list(
    age = "25", education = "Uni", gender = "Žena", glasses = "Nemám okuliare",
    nick = "Player2", psc = "12345",
    reactions = list(list(time = 321, x = 456,y = 789)),
    score = 600
  )
)

Now reshape it into your desired data.frame:

library(tidyverse)

# 1. Convert the player list into a tibble with expanded metadata columns
player_tbl <- player_list %>%
  enframe(name = "player_id") %>% # Optional: keep the original list key as an ID
  unnest_wider(value) # Unpack player metadata into separate columns

# 2. Split each player's reactions into individual rows
player_reactions_tbl <- player_tbl %>%
  unnest_longer(reactions) # One row per reaction

# 3. Unpack the reaction details (x, y, time) into separate columns
final_df <- player_reactions_tbl %>%
  unnest_wider(reactions)

# Optional: Convert numeric fields from character to numeric
final_df <- final_df %>%
  mutate(
    age = as.numeric(age),
    score = as.numeric(score),
    time = as.numeric(time),
    x = as.numeric(x),
    y = as.numeric(y)
  )

# View the result
head(final_df)

Why This Works #

• enframe turns your named list into a tibble with a player_id column (useful for tracking original entries)
• unnest_wider expands each player's metadata into separate columns
• unnest_longer splits the nested reactions list into one row per reaction
• The final unnest_wider pulls out the x/y/time values from each reaction

Solution 2: Fix the Tidyjson Error #

If you prefer to use tidyjson, you just need to convert your R list to a JSON string first (since as.tbl_json doesn't accept raw lists directly).

Code Example #

library(tidyjson)
library(jsonlite)

# Convert your player list to a JSON string
player_json <- toJSON(player_list, auto_unbox = FALSE)

# Now process with tidyjson
tidyjson_result <- player_json %>%
  as.tbl_json %>%
  gather_array() %>% # Treat each player as an array element
  spread_values(
    age = jstring("age"),
    education = jstring("education"),
    gender = jstring("gender"),
    glasses = jstring("glasses"),
    nick = jstring("nick"),
    psc = jstring("psc"),
    score = jnumber("score")
  ) %>%
  enter_object("reactions") %>% # Navigate into the reactions field
  gather_array() %>% # Split each reaction into its own row
  spread_values(
    time = jnumber("time"),
    x = jnumber("x"),
    y = jnumber("y")
  )

# Convert to data.frame
final_tidyjson_df <- as.data.frame(tidyjson_result)

Key Fix for the Error #

The toJSON conversion from jsonlite turns your R list into a valid JSON object that tidyjson can work with. That's why you were getting the "no applicable method" error before—you skipped this conversion step.

Quick Tips #

• If your data comes directly from a Firebase JSON file, use jsonlite::read_json("your_file.json") to load it into R as a list, then use the tidyverse method above.
• If some players have empty reactions, add keep_empty = TRUE to unnest_longer to retain those rows.
• Always double-check data types (your example has age as a string—convert to numeric if you need to run stats on it).

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