r-如何将摘要列添加到作为另外两个摘要列的函数的gt表中

Question

我使用gt显示两列表示特定时间点的值，第三列显示预处理中计算的百分比变化。我想要一个汇总行，其中包含每列中值的总和，以及各个观察值的百分比变化列下这些总和之间的百分比变化。

为了进行说明，我使用sp500表修改了一个示例。

library(gt)
library(tidyverse)
start_date <- "2010-06-07"
end_date <- "2010-06-14"
data <- sp500 %>%
filter(date >= start_date & date <= end_date) %>%
select(date, open, close) %>%
mutate(intraday_pct_ch = (close/open - 1)*100)
data
#> # A tibble: 6 x 4
#>   date        open close intraday_pct_ch
#>   <date>     <dbl> <dbl>           <dbl>
#> 1 2010-06-14 1095  1090.          -0.490
#> 2 2010-06-11 1083. 1092.           0.827
#> 3 2010-06-10 1059. 1087.           2.65 
#> 4 2010-06-09 1063. 1056.          -0.664
#> 5 2010-06-08 1051. 1062            1.06 
#> 6 2010-06-07 1066. 1050.          -1.44
data %>% gt() %>%
summary_rows(
fns = list(Week_Sum = ~sum(.)),
columns = vars(open, close, intraday_pct_ch))

^{创建于2021-04-20由reprex包(v2.0.0(}

正如所写的，我得到了百分比变化的总和，这显然是我不想要的。有没有一种方法可以在这里编写一个函数，将前两列作为参数，并将结果保持在同一行中？

谢谢！

更新：这是reprex，它给了我想要的结果，但由于"_库存#"；群体增加。我从第一个reprex中删除了html。

library(gt)
library(tidyverse)
set.seed(42)  
data1 <- sp500 %>%
filter(date >= "2010-06-07" & date <= "2010-06-14") %>%
select(date, open, close) %>%
mutate(intraday_pct_ch = (close/open - 1)*100)
data2 <- data1 %>%
mutate(open = (open + rnorm(1, 100, 10)),
close = (close + rnorm(1, 100, 10)),
intraday_pct_ch = (close/open - 1)*100)
data_bind <- data1 %>% bind_cols(data2, .name_repair = "minimal") %>%
rename_with(~ str_replace(., "$", "_stock1"), .cols = 1:4) %>%
rename_with(~ str_replace(., "$", "_stock2"), .cols = 5:8) %>%
select(!starts_with("date"))
data_bind
#> # A tibble: 6 x 6
#>   open_stock1 close_stock1 intraday_pct_ch_stock1 open_stock2 close_stock2
#>         <dbl>        <dbl>                  <dbl>       <dbl>        <dbl>
#> 1       1095         1090.                 -0.490       1209.        1184.
#> 2       1083.        1092.                  0.827       1196.        1186.
#> 3       1059.        1087.                  2.65        1172.        1181.
#> 4       1063.        1056.                 -0.664       1176.        1150.
#> 5       1051.        1062                   1.06        1165.        1156.
#> 6       1066.        1050.                 -1.44        1180.        1145.
#> # … with 1 more variable: intraday_pct_ch_stock2 <dbl>
#vectorized William Gram's suggested solution
intraday_pct_ch_vec <- c((sum(data_bind$close_stock1) / sum(data_bind$open_stock1)-1)*100 , (sum(data_bind$close_stock2) / sum(data_bind$open_stock2)-1)*100 )
data_bind %>% gt() %>%
summary_rows(
fns = list(Week_Sum = ~sum(.)),
columns = starts_with(c("o", "c"))) %>%
summary_rows(
fns = list(Week_Sum = ~ return(intraday_pct_ch_vec[1])),
columns = 4) %>%
summary_rows(
fns = list(Week_Sum = ~ return(intraday_pct_ch_vec[2])),
columns = 7)

<style>html {
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, Cantarell, 'Helvetica Neue', 'Fira Sans', 'Droid Sans', Arial, sans-serif;
}
#jdrzqoxgdy .gt_table {
display: table;
border-collapse: collapse;
margin-left: auto;
margin-right: auto;
color: #333333;
font-size: 16px;
font-weight: normal;
font-style: normal;
background-color: #FFFFFF;
width: auto;
border-top-style: solid;
border-top-width: 2px;
border-top-color: #A8A8A8;
border-right-style: none;
border-right-width: 2px;
border-right-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #A8A8A8;
border-left-style: none;
border-left-width: 2px;
border-left-color: #D3D3D3;
}
#jdrzqoxgdy .gt_heading {
background-color: #FFFFFF;
text-align: center;
border-bottom-color: #FFFFFF;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
}
#jdrzqoxgdy .gt_title {
color: #333333;
font-size: 125%;
font-weight: initial;
padding-top: 4px;
padding-bottom: 4px;
border-bottom-color: #FFFFFF;
border-bottom-width: 0;
}
#jdrzqoxgdy .gt_subtitle {
color: #333333;
font-size: 85%;
font-weight: initial;
padding-top: 0;
padding-bottom: 4px;
border-top-color: #FFFFFF;
border-top-width: 0;
}
#jdrzqoxgdy .gt_bottom_border {
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
}
#jdrzqoxgdy .gt_col_headings {
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
}
#jdrzqoxgdy .gt_col_heading {
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: normal;
text-transform: inherit;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
vertical-align: bottom;
padding-top: 5px;
padding-bottom: 6px;
padding-left: 5px;
padding-right: 5px;
overflow-x: hidden;
}
#jdrzqoxgdy .gt_column_spanner_outer {
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: normal;
text-transform: inherit;
padding-top: 0;
padding-bottom: 0;
padding-left: 4px;
padding-right: 4px;
}
#jdrzqoxgdy .gt_column_spanner_outer:first-child {
padding-left: 0;
}
#jdrzqoxgdy .gt_column_spanner_outer:last-child {
padding-right: 0;
}
#jdrzqoxgdy .gt_column_spanner {
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
vertical-align: bottom;
padding-top: 5px;
padding-bottom: 6px;
overflow-x: hidden;
display: inline-block;
width: 100%;
}
#jdrzqoxgdy .gt_group_heading {
padding: 8px;
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: initial;
text-transform: inherit;
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
vertical-align: middle;
}
#jdrzqoxgdy .gt_empty_group_heading {
padding: 0.5px;
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: initial;
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
vertical-align: middle;
}
#jdrzqoxgdy .gt_from_md > :first-child {
margin-top: 0;
}
#jdrzqoxgdy .gt_from_md > :last-child {
margin-bottom: 0;
}
#jdrzqoxgdy .gt_row {
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
margin: 10px;
border-top-style: solid;
border-top-width: 1px;
border-top-color: #D3D3D3;
border-left-style: none;
border-left-width: 1px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 1px;
border-right-color: #D3D3D3;
vertical-align: middle;
overflow-x: hidden;
}
#jdrzqoxgdy .gt_stub {
color: #333333;
background-color: #FFFFFF;
font-size: 100%;
font-weight: initial;
text-transform: inherit;
border-right-style: solid;
border-right-width: 2px;
border-right-color: #D3D3D3;
padding-left: 12px;
}
#jdrzqoxgdy .gt_summary_row {
color: #333333;
background-color: #FFFFFF;
text-transform: inherit;
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
}
#jdrzqoxgdy .gt_first_summary_row {
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
}
#jdrzqoxgdy .gt_grand_summary_row {
color: #333333;
background-color: #FFFFFF;
text-transform: inherit;
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
}
#jdrzqoxgdy .gt_first_grand_summary_row {
padding-top: 8px;
padding-bottom: 8px;
padding-left: 5px;
padding-right: 5px;
border-top-style: double;
border-top-width: 6px;
border-top-color: #D3D3D3;
}
#jdrzqoxgdy .gt_striped {
background-color: rgba(128, 128, 128, 0.05);
}
#jdrzqoxgdy .gt_table_body {
border-top-style: solid;
border-top-width: 2px;
border-top-color: #D3D3D3;
border-bottom-style: solid;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
}
#jdrzqoxgdy .gt_footnotes {
color: #333333;
background-color: #FFFFFF;
border-bottom-style: none;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 2px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 2px;
border-right-color: #D3D3D3;
}
#jdrzqoxgdy .gt_footnote {
margin: 0px;
font-size: 90%;
padding: 4px;
}
#jdrzqoxgdy .gt_sourcenotes {
color: #333333;
background-color: #FFFFFF;
border-bottom-style: none;
border-bottom-width: 2px;
border-bottom-color: #D3D3D3;
border-left-style: none;
border-left-width: 2px;
border-left-color: #D3D3D3;
border-right-style: none;
border-right-width: 2px;
border-right-color: #D3D3D3;
}
#jdrzqoxgdy .gt_sourcenote {
font-size: 90%;
padding: 4px;
}
#jdrzqoxgdy .gt_left {
text-align: left;
}
#jdrzqoxgdy .gt_center {
text-align: center;
}
#jdrzqoxgdy .gt_right {
text-align: right;
font-variant-numeric: tabular-nums;
}
#jdrzqoxgdy .gt_font_normal {
font-weight: normal;
}
#jdrzqoxgdy .gt_font_bold {
font-weight: bold;
}
#jdrzqoxgdy .gt_font_italic {
font-style: italic;
}
#jdrzqoxgdy .gt_super {
font-size: 65%;
}
#jdrzqoxgdy .gt_footnote_marks {
font-style: italic;
font-size: 65%;
}
</style>
<div id="jdrzqoxgdy" style="overflow-x:auto;overflow-y:auto;width:auto;height:auto;"><table class="gt_table">

<thead class="gt_col_headings">
<tr>
<th class="gt_col_heading gt_columns_bottom_border gt_left" rowspan="1" colspan="1"></th>
<th class="gt_col_heading gt_columns_bottom_border gt_right" rowspan="1" colspan="1">open_stock1</th>
<th class="gt_col_heading gt_columns_bottom_border gt_right" rowspan="1" colspan="1">close_stock1</th>
<th class="gt_col_heading gt_columns_bottom_border gt_right" rowspan="1" colspan="1">intraday_pct_ch_stock1</th>
<th class="gt_col_heading gt_columns_bottom_border gt_right" rowspan="1" colspan="1">open_stock2</th>
<th class="gt_col_heading gt_columns_bottom_border gt_right" rowspan="1" colspan="1">close_stock2</th>
<th class="gt_col_heading gt_columns_bottom_border gt_right" rowspan="1" colspan="1">intraday_pct_ch_stock2</th>
</tr>
</thead>
<tbody class="gt_table_body">
<tr>
<td class="gt_row gt_left gt_stub"></td>
<td class="gt_row gt_right">1095.00</td>
<td class="gt_row gt_right">1089.63</td>
<td class="gt_row gt_right">-0.4904110</td>
<td class="gt_row gt_right">1208.71</td>
<td class="gt_row gt_right">1183.983</td>
<td class="gt_row gt_right">-2.0456995</td>
</tr>
<tr>
<td class="gt_row gt_left gt_stub"></td>
<td class="gt_row gt_right">1082.65</td>
<td class="gt_row gt_right">1091.60</td>
<td class="gt_row gt_right">0.8266753</td>
<td class="gt_row gt_right">1196.36</td>
<td class="gt_row gt_right">1185.953</td>
<td class="gt_row gt_right">-0.8698527</td>
</tr>
<tr>
<td class="gt_row gt_left gt_stub"></td>
<td class="gt_row gt_right">1058.77</td>
<td class="gt_row gt_right">1086.84</td>
<td class="gt_row gt_right">2.6511896</td>
<td class="gt_row gt_right">1172.48</td>
<td class="gt_row gt_right">1181.193</td>
<td class="gt_row gt_right">0.7431629</td>
</tr>
<tr>
<td class="gt_row gt_left gt_stub"></td>
<td class="gt_row gt_right">1062.75</td>
<td class="gt_row gt_right">1055.69</td>
<td class="gt_row gt_right">-0.6643237</td>
<td class="gt_row gt_right">1176.46</td>
<td class="gt_row gt_right">1150.043</td>
<td class="gt_row gt_right">-2.2454376</td>
</tr>
<tr>
<td class="gt_row gt_left gt_stub"></td>
<td class="gt_row gt_right">1050.81</td>
<td class="gt_row gt_right">1062.00</td>
<td class="gt_row gt_right">1.0648832</td>
<td class="gt_row gt_right">1164.52</td>
<td class="gt_row gt_right">1156.353</td>
<td class="gt_row gt_right">-0.7012905</td>
</tr>
<tr>
<td class="gt_row gt_left gt_stub"></td>
<td class="gt_row gt_right">1065.84</td>
<td class="gt_row gt_right">1050.47</td>
<td class="gt_row gt_right">-1.4420551</td>
<td class="gt_row gt_right">1179.55</td>
<td class="gt_row gt_right">1144.823</td>
<td class="gt_row gt_right">-2.9440531</td>
</tr>
<tr>
<td class="gt_row gt_stub gt_right gt_grand_summary_row gt_first_grand_summary_row">Week_Sum</td>
<td class="gt_row gt_right gt_grand_summary_row gt_first_grand_summary_row">6,415.82</td>
<td class="gt_row gt_right gt_grand_summary_row gt_first_grand_summary_row">6,436.23</td>
<td class="gt_row gt_right gt_grand_summary_row gt_first_grand_summary_row">0.32</td>
<td class="gt_row gt_right gt_grand_summary_row gt_first_grand_summary_row">7,098.08</td>
<td class="gt_row gt_right gt_grand_summary_row gt_first_grand_summary_row">7,002.35</td>
<td class="gt_row gt_right gt_grand_summary_row gt_first_grand_summary_row">&minus;1.35</td>
</tr>
</tbody>


</table></div>
<sup>Created on 2021-04-21 by the [reprex package](https://reprex.tidyverse.org) (v2.0.0)</sup>

Answer 1

我完全误解了你对gt()的意图，对此深表歉意。

您可以添加一个额外的summary_rows，并单独计算总和：

intraday_pct_ch_total <- (sum(data$close) / sum(data$open)-1)*100 
data %>% 
gt() %>%
summary_rows(
fns = list(Week_Sum = ~ sum(.)),
columns = vars(open, close)) %>% 
summary_rows(
fns = list(Week_Sum = ~ return(intraday_pct_ch_total)),
columns = vars(intraday_pct_ch)
)

如果这能产生你所期望的表格，请告诉我。

更新我一直在尽我所能使答案可扩展，但没有成功。

首先，我制作了intraday_pct_ch，它可以包含任何数量的列：

pct_changes <- data %>% 
summarise(
across(contains('open'), sum),
across(contains('close'), sum)
) %>% 
pivot_longer(
cols = everything(),
names_to = c('open_close', 'no'),
names_pattern = '(open|close)(.)',
values_to = 'sum'
) %>% 
pivot_wider(
names_from = 'open_close',
values_from = 'sum'
) %>% 
mutate(diff = (close/open-1)*100, .keep='unused') %>% 
pivot_wider(
names_from = 'no',
values_from = 'diff',
names_prefix = 'intraday_pct_ch'
)

然后我必须将它们添加到表中：

table_info <- data %>% gt() %>%
summary_rows(
fns = list(Week_Sum = ~sum(.)),
columns = starts_with(c("o", "c"))
)
table_info %>%
summary_rows(
fns = list(Week_Sum = ~ return(as.numeric(pct_changes[1, 1]))),
columns = names(tmp)[1]) %>%
summary_rows(
fns = list(Week_Sum = ~ return(as.numeric(pct_changes[1, 2]))),
columns = names(tmp)[2]
)

我试着做一个愚蠢的循环，这样就不必手动了，但出于某种原因，至少在我这边，它不起作用。

for (i in seq_along(pct_changes)) {
table_info
print(paste0('change: ', i, ': ', names(pct_changes)[i], ': ', pct_changes %>% pull(names(pct_changes)[i])))
table_info <- table_info %>% 
summary_rows(
fns = list(Week_Sum = ~ pct_changes %>% pull(names(pct_changes)[i])),
columns = names(pct_changes)[i]
)
}