如何计算R数据框中各行各列的所有组合比率？

现有如下结构的DataFrame：

28 Madrid 08 Barcelona 46 Valencia/València 03 Alicante/Alacant 41 Sevilla
2020 143.4846560  128.4231590           54.4870538          37.7936793 37.4922771
2032 125.5210867  113.9580077           56.6657609          40.5967195 39.5353532
2031 123.7643426  112.9806035           56.1167784          40.0735714 39.0574155
2030 122.1152422  112.1129958           55.6022418          39.5934808 38.6074651

需要按行计算所有列组合的比率：例如2020行中28 Madrid与08 Barcelona的比率（1.11728023）、28 Madrid与46 Valencia/València的比率（2.63337153）等，对所有年份行执行相同操作。

预期输出可以是长格式/宽格式DataFrame，或列表形式（示例如下）：

2020
         Barcelona    Valencia    Alicante    Sevilla
Madrid     1.11         2.63
Barcelona  x.xxx        x.xxx
Valencia   x.xxx        x.xxx
Alicante
Sevilla

2021
         Barcelona    Valencia    Alicante    Sevilla
Madrid     x.xxx        x.xxx
Barcelona  x.xxx        x.xxx
Valencia   x.xxx        x.xxx
Alicante
Sevilla

数据代码：

df = structure(list(`28 Madrid` = c(143.484656024558, 125.521086677107, 
123.764342599024, 122.115242153178, 120.515657778159, 118.995729690994, 
117.540387783171, 116.134561946176, 114.776099307365, 113.393955069462, 
112.024728067427, 110.633972338945, 107.335771447251, 101.333562513803, 
100.321712370789, 101.540238287695, 100.881459258414, 97.0213586064507, 
92.1407963208957, 92.7113075717435, 91.2667300271439), `08 Barcelona` = c(128.423159002175, 
113.958007702376, 112.980603521678, 112.11299584586, 111.277681259713, 
110.556468923735, 109.912760002968, 109.322872898317, 108.74805590218, 
108.179697523977, 107.591963291971, 107.012840550545, 108.358385953488, 
104.229176220936, 104.227023348291, 100.831943187585, 101.294810806198, 
99.6284873791932, 98.9008164252815, 96.2011141288166, 95.6026155335876
), `46 Valencia/València` = c(54.4870537649372, 56.6657608813826, 
56.1167783569818, 55.6022417948964, 55.1178454498369, 54.6528249585798, 
54.222250429638, 53.815357499788, 53.4149231878722, 53.0295589844693, 
52.6291246725535, 52.2286903606376, 54.5171939819631, 50.6721634385131, 
50.517156608094, 49.171611205151, 49.7658040550906, 48.2308058594132, 
47.6064727924476, 46.5817054135662, 45.2253956473996), `03 Alicante/Alacant` = c(37.7936792778645, 
40.5967194612755, 40.0735714086112, 39.5934808088411, 39.1112373364264, 
38.6785099348399, 38.2522411511875, 37.8561125845611, 37.4621368905793, 
37.0789255598212, 36.7000199743524, 36.3211143888836, 39.5934808088411, 
34.3899876265798, 35.0272379294136, 34.247898032029, 34.12949003657, 
32.6267849305632, 32.7365814354433, 32.0282863353341, 31.3049211267119
), `41 Sevilla` = c(37.4922771076053, 39.535353247434, 39.0574155203086, 
38.6074651375645, 38.1919607171357, 37.7850677872857, 37.3997035838828, 
37.0466324701505, 36.7021728469971, 36.3491017332648, 36.0197122186244, 
35.6989341945628, 38.1123044292814, 34.9346644056911, 33.9981648052427, 
34.0412222581369, 34.7452116129567, 33.027219242479, 32.5062240624595, 
31.8495979058233, 31.5094440279593)), row.names = c("2020", "2032", 
"2031", "2030", "2029", "2028", "2027", "2026", "2025", "2024", 
"2023", "2022", "2021", "2017", "2018", "2019", "2015", "2016", 
"2012", "2014", "2013"), class = "data.frame")

方法1：基础R实现（生成列表形式的比率矩阵） #

该方法生成一个列表，每个元素对应一年的比率矩阵，矩阵行列使用简化后的城市名称，展示所有两两组合的比率：

# 提取城市名称（去掉前缀数字和空格）
city_names = sub("^\\d+ ", "", colnames(df))

# 按行计算比率矩阵，生成列表
ratio_list = lapply(1:nrow(df), function(i) {
  row_vals = as.numeric(df[i, ])
  # 生成两两比率矩阵
  mat = outer(row_vals, row_vals, "/")
  # 设置行列名称为城市名
  dimnames(mat) = list(city_names, city_names)
  # 可选：保留两位小数
  round(mat, 2)
})

# 设置列表名称为年份
names(ratio_list) = rownames(df)

# 查看2020年的结果
ratio_list[["2020"]]

输出示例（2020年）：

Barcelona Valencia Alicante Sevilla
Madrid        1.12     2.63     3.80    3.83
Barcelona     0.89     2.36     3.40    3.43
Valencia      0.43     1.00     1.44    1.45
Alicante      0.27     0.69     1.00     1.01
Sevilla       0.26     0.69     0.99     1.00

方法2：tidyverse实现（长格式/宽格式DataFrame） #

如果需要更灵活的结构化输出，可以使用tidyverse工具链：

首先安装并加载包：

install.packages("tidyverse")
library(tidyverse)

长格式输出 #

适合后续数据分析或可视化：

df_long = df %>%
  rownames_to_column("Year") %>%
  pivot_longer(cols = -Year, names_to = "City1", values_to = "Value1") %>%
  pivot_longer(cols = -c(Year, City1, Value1), names_to = "City2", values_to = "Value2") %>%
  mutate(
    # 提取城市名称
    City1 = str_remove(City1, "^\\d+ "),
    City2 = str_remove(City2, "^\\d+ "),
    # 计算比率
    Ratio = round(Value1 / Value2, 2)
  ) %>%
  select(Year, City1, City2, Ratio)

# 查看前几行
head(df_long)

输出示例：

Year   City1    City2 Ratio
1 2020  Madrid Barcelona  1.12
2 2020  Madrid  Valencia  2.63
3 2020  Madrid  Alicante  3.80
4 2020  Madrid   Sevilla  3.83
5 2020 Barcelona  Madrid  0.89
6 2020 Barcelona Valencia  2.36

宽格式输出 #

接近矩阵形式的表格，便于直观查看单年份数据：

df_wide = df_long %>%
  pivot_wider(names_from = City2, values_from = Ratio) %>%
  arrange(Year)

# 查看2020年的结果
df_wide %>% filter(Year == "2020")

输出示例：

Year   City1 Barcelona Valencia Alicante Sevilla
1 2020  Madrid      1.12     2.63     3.80    3.83
2 2020 Barcelona     1.00     2.36     3.40    3.43
3 2020  Valencia     0.43     1.00     1.44    1.45
4 2020  Alicante     0.27     0.69     1.00     1.01
5 2020   Sevilla     0.26     0.69     0.99     1.00

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