R语言：提取各季节Top2到访城市并转换为目标数据框格式

1. 字符串排序的可靠性问题 #

直接对含括号、百分号的value字符串排序完全不可靠。R会按字符串的字典序比较，比如数值9对应的字符串"9 (12.68%)"会排在数值10的"10 (14.08%)"前面——因为字符'9'的ASCII码比'1'大，这和实际数值大小逻辑完全相反。必须提取字符串中的原始到访量数值，用数值型数据排序才准确。

2. 完整代码（生成目标数据框） #

方案一：基于原始数据处理（推荐） #

先从原始数据中筛选各季节Top2城市，再做格式化，彻底避免字符串排序问题：

library(dplyr)
library(tidyr)
library(janitor)

# 原始数据（暂不做janitor格式化）
df_raw <- data.frame(
    city = c("London", "Paris", "Rome", "Madrid", "Venice", "Bern"),
    Spring = c(10, 3, 6, 9, 23, 8),
    Summer = c(1, 5, 6, 4, 30, 12),
    Fall = c(22, 24, 15, 4, 12, 8),
    Winter = c(0, 12, 4, 22, 7, 9),
    check.names=F
)

# 生成带排名的Top2城市数据框（含原始到访量）
top2_df <- df_raw %>%
  pivot_longer(cols = -city, names_to = "season", values_to = "visits") %>%
  group_by(season) %>%
  slice_max(visits, n = 2, with_ties = FALSE) %>%  # 用原始数值排序，逻辑可靠
  mutate(rank = row_number()) %>%  # 标记Top1/Top2
  pivot_wider(names_from = season, values_from = paste0(city, " (", visits, ")")) %>%
  select(rank, everything())

# 如果需要和原df一致的「数值(百分比)」格式，可结合janitor处理：
df_formatted <- df_raw %>%
  adorn_totals(c("row")) %>%
  adorn_percentages("col") %>%
  adorn_pct_formatting(digits = 2) %>%
  adorn_ns(position = "front") %>%
  filter(city != "Total") %>%
  pivot_longer(cols = -city, names_to = "season", values_to = "formatted_value")

top2_df_with_pct <- df_raw %>%
  pivot_longer(cols = -city, names_to = "season", values_to = "visits") %>%
  group_by(season) %>%
  slice_max(visits, n = 2, with_ties = FALSE) %>%
  left_join(df_formatted, by = c("city", "season")) %>%
  mutate(rank = row_number()) %>%
  pivot_wider(names_from = season, values_from = formatted_value) %>%
  select(rank, everything())

方案二：基于已格式化的df处理 #

如果必须从已格式化的df出发，先提取原始数值再排序：

library(dplyr)
library(tidyr)
library(stringr)

semi_output <- df %>%
  filter(city != 'Total') %>%
  pivot_longer(cols = -city, names_to = "season", values_to = "formatted_value") %>%
  # 从格式化字符串中提取原始到访量数值
  mutate(visits = as.numeric(str_extract(formatted_value, "^\\d+"))) %>%
  group_by(season) %>%
  slice_max(visits, n = 2, with_ties = FALSE) %>%  # 用数值排序，逻辑可靠
  mutate(rank = row_number()) %>%
  pivot_wider(names_from = season, values_from = paste0(city, " - ", formatted_value)) %>%
  select(rank, everything())

最终效果说明 #

生成的目标数据框结构清晰：

• 第一列为排名（Top1/Top2）
• 后续列对应各个季节，单元格内容为「城市 - 到访量(百分比)」格式，直观展示各季节的Top2城市。

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