读取嵌套JSON并转换为Tidy Data，将选举数据JSON转为合规tibble

Got it, let's turn that election data into a tidy tibble that follows Tidy Data principles. First, let's unpack the raw data you shared: it has two distinct row types—coalition/party total vote entries (no candidate name, just the list/pact/party) and individual candidate results (numbered names, affiliated party, votes, percentage, and election status).

Tidy Data requires each row to represent one observation, and each column to represent one variable. Here's a step-by-step solution using R's tidyverse package:

Step 1: Input the Raw Data #

First, let's encode the truncated data you provided into a raw tibble (I'll add a placeholder for the cut-off "54. ELS..." entry):

library(tidyverse)

# Raw election data from the Aysén Region results
raw_election_data <- tibble(
  listapacto = c(
    "H. SUMEMOS", "TODOS", "50. EDUARDO ROMO LAFOY", "51. SARA MARTINEZ MONDELO",
    "CIUDADANOS", "52. VICTOR MANUEL BORQUEZ FINCKE", "53. MARISOL LUSDEMIA PINILLA VEJAR",
    "K. COALICIÓN REGIONALISTA VERDE", "DEMOCRACIA REGIONAL PATAGONICA", "54. ELS..."
  ),
  partido = c(
    "", "", "IND-TODOS", "IND-TODOS", "", "CIUD.", "CIUD.", "", "", ""
  ),
  votos = c(365, 113, 69, 44, 252, 53, 199, 200, 200, NA),
  porcentaje = c(
    "4,52%", "1,40%", "0,86%", "0,55%", "3,12%", "0,66%", "2,47%", "2,48%", "2,48%", NA
  ),
  electo = rep("", 10)
)

Step 2: Clean and Reshape to Tidy Format #

We'll separate total entries from candidates, link candidates to their parent coalitions/parties, and standardize variable types:

tidy_election_tibble <- raw_election_data %>%
  # Flag rows that are coalition/party totals (no leading number in listapacto)
  mutate(is_total = !str_detect(listapacto, "^\\d+\\.")) %>%
  # Fill down the coalition/party name to associate with its candidates
  fill(listapacto, .direction = "down") %>%
  # Rename columns for clarity
  rename(
    coalition_or_party = listapacto,
    candidate_name = ifelse(is_total, NA, listapacto),
    party_affiliation = partido,
    vote_count = votos,
    vote_percentage = porcentaje,
    elected = electo
  ) %>%
  # Clean candidate names (remove leading number and dot)
  mutate(candidate_name = str_remove(candidate_name, "^\\d+\\. ")) %>%
  # Convert percentage strings to numeric values (replace comma with dot, remove %)
  mutate(vote_percentage = as.numeric(str_replace(vote_percentage, ",", "."))) %>%
  # Reorder columns for logical flow
  select(coalition_or_party, candidate_name, party_affiliation, vote_count, vote_percentage, elected) %>%
  # Keep only unique total rows and all candidate rows
  filter(is.na(candidate_name) | !duplicated(coalition_or_party & is.na(candidate_name))) %>%
  # Remove the helper flag
  select(-is_total)

Step 3: View the Tidy Result #

Print the final tibble to verify:

print(tidy_election_tibble, n = 10)

Key Tidy Data Principles Applied: #

• Single observation per row: Each row is either a coalition/party total or an individual candidate's result
• Single variable per column: Columns clearly represent one piece of information (e.g., vote_count is numeric, vote_percentage is a standardized numeric value)
• Consistent formatting: Names are cleaned, numeric values are properly typed, and candidates are linked to their parent coalitions

If you have the full data for the truncated "54. ELS..." entry, just replace the placeholder in raw_election_data and re-run the code.

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