R вычисление уменьшающегося скользящего процента с фиксированной начальной точкой по всему датафрейму

Содержание

Вопрос или проблема
Ответ или решение
Задача
Импорт библиотек и загрузка данных
Рассчёт процента
Примечания
Заключение

Вопрос или проблема

У меня есть фрейм данных ниже, я хотел бы добавить столбец под названием Percentage. Упорядочить по каждой торговле. В строке CofQYr == 0 значение Percentage будет равно общей сумме по всем столбцам (с 2014 по 2024) деленной на общую сумму, где CofQYr == "Total" (с 2014 по 2024). В строке CofQYr == 1 значение Percentage будет равно общей сумме по всем столбцам (с 2014 по 2023) деленной на общую сумму, где CofQYr == "Total" (с 2014 по 2023). В строке CofQYr == 9 значение Percentage будет равно общей сумме по всем столбцам (с 2014) деленной на общую сумму, где CofQYr == "Total" только для 2014. Так что значение Percentage для 306A в CofQYr=1 = 0.0134. Структура(list(TRADE = c("306A", "306A", "306A", "306A", "306A", "306A", "306A", "306A", "306A", "306A", "306A", "306A", "307A", "307A", "307A", "307A", "307A", "307A", "307A", "307A", "307A", "307A", "307A", "307A", "307A", "308A", "308A", "308A", "308A", "308A", "308A", "308A", "308A", "308A", "308A", "308A", "308A", "308A", "308R", "308R", "308R", "308R", "308R", "308R", "308R", "308R", "308R", "308R", "308R", "308R", "309A", "309A", "309A", "309A", "309A", "309A", "309A", "309A", "309A", "309A", "309A", "309A", "309A", "309C", "309C", "309C", "309C", "309C", "309C", "309C", "309C", "309C", "309C", "313A", "313A", "313A", "313A", "313A", "313A", "313A", "313A", "313A", "313A", "313A", "313A", "313D", "313D", "313D", "313D", "313D", "313D", "313D", "313D", "313D", "313D", "313D", "313D", "339A", "339A", "339A", "339A", "339A", "339A", "339A", "339A", "339A", "339A", "339A", "339A", "339B", "339B", "339B", "339B", "339B", "339B", "339B", "339B", "339C", "339C", "339C", "339C", "339C", "339C", "339C", "427A", "427A", "427A", "427A", "427A", "427A", "427A", "427A", "427A", "427A", "427A"), CofQYr = c("0", "1", "10", "2", "3", "4", "5", "6", "7", "8", "9", "99999", "Total", "0", "1", "10", "2", "3", "4", "5", "6", "7", "8", "9", "99999", "Total", "0", "1", "10", "2", "3", "4", "5", "6", "7", "8", "9", "99999", "Total", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "99999", "Total", "0", "1", "10", "2", "3", "4", "5", "6", "7", "8", "9", "99999", "Total", "0", "1", "2", "3", "4", "5", "6", "8", "99999", "Total", "0", "1", "10", "2", "3", "4", "5", "6", "7", "99999", "Total"), 2014 = c(77L, 60L, 2L, 80L, 136L, 248L, 105L, 55L, 55L, 36L, 5L, 510L, 1369L, 3L, 4L, 1L, 2L, 8L, 27L, 22L, 16L, 9L, 7L, 5L, 92L, 196L, 31L, 22L, 1L, 19L, 28L, 48L, 48L, 29L, 19L, 10L, 7L, 205L, 467L, 1L, 4L, 3L, 2L, 1L, 0L, 0L, 1L, 1L, 24L, 37L, 216L, 214L, 5L, 213L, 360L, 709L, 321L, 152L, 145L, 88L, 68L, 993L, 3484L, 1L, 1L, 0L, 3L, 0L, 0L, 0L, 0L, 18L, 23L, 26L, 29L, 4L, 29L, 38L, 99L, 65L, 18L, 16L, 13L, 7L, 194L, 538L, 6L, 7L, 23L, 24L, 4L, 3L, 3L, 0L, 4L, 2L, 76L, 152L, 4L, 9L, 44L, 30L, 14L, 5L, 1L, 2L, 0L, 1L, 32L, 142L, 3L, 9L, 12L, 0L, 0L, 0L, 12L, 36L, 0L, 3L, 7L, 1L, 2L, 9L, 22L, 1L, 2L, 1L, 5L, 15L, 51L, 13L, 6L, 6L, 51L, 151L), 2015 = c(46L, 30L, 0L, 45L, 113L, 175L, 98L, 83L, 57L, 20L, 6L, 484L, 1157L, 1L, 2L, 0L, 3L, 7L, 22L, 17L, 15L, 8L, 5L, 0L, 75L, 155L, 15L, 8L, 0L, 17L, 39L, 45L, 34L, 38L, 14L, 11L, 1L, 195L, 417L, 2L, 2L, 4L, 2L, 0L, 0L, 1L, 0L, 0L, 0L, 37L, 48L, 156L, 143L, 0L, 154L, 314L, 476L, 290L, 250L, 131L, 60L, 11L, 961L, 2946L, 1L, 2L, 1L, 2L, 0L, 0L, 2L, 0L, 34L, 42L, 23L, 19L, 0L, 17L, 49L, 85L, 66L, 38L, 19L, 12L, 0L, 162L, 490L, 8L, 7L, 24L, 29L, 8L, 0L, 2L, 2L, 1L, 1L, 79L, 161L, 3L, 9L, 34L, 40L, 9L, 2L, 3L, 0L, 2L, 0L, 43L, 145L, 0L, 8L, 7L, 5L, 0L, 0L, 12L, 32L, 0L, 6L, 1L, 0L, 0L, 3L, 10L, 3L, 6L, 0L, 2L, 12L, 27L, 22L, 10L, 5L, 37L, 124L), 2016 = c(30L, 40L, 0L, 40L, 60L, 135L, 168L, 91L, 51L, 9L, 0L, 555L, 1179L, 0L, 1L, 0L, 1L, 5L, 16L, 21L, 14L, 9L, 3L, 0L, 71L, 141L, 16L, 4L, 0L, 18L, 8L, 36L, 80L, 42L, 15L, 3L, 0L, 193L, 415L, 2L, 4L, 3L, 1L, 0L, 1L, 2L, 1L, 0L, 0L, 38L, 52L, 109L, 87L, 0L, 95L, 141L, 369L, 501L, 299L, 108L, 15L, 0L, 924L, 2648L, 0L, 1L, 3L, 0L, 0L, 1L, 1L, 1L, 23L, 30L, 12L, 19L, 0L, 8L, 12L, 51L, 96L, 44L, 20L, 2L, 0L, 212L, 476L, 7L, 11L, 28L, 25L, 5L, 8L, 4L, 2L, 1L, 0L, 103L, 194L, 1L, 8L, 16L, 20L, 6L, 5L, 2L, 1L, 0L, 0L, 36L, 95L, 1L, 12L, 12L, 0L, 0L, 0L, 2L, 27L, 2L, 3L, 4L, 0L, 0L, 7L, 16L, 0L, 3L, 0L, 2L, 6L, 40L, 21L, 9L, 4L, 34L, 119L), 2017 = c(25L, 18L, 0L, 12L, 46L, 200L, 176L, 67L, 15L, 0L, 0L, 550L, 1109L, 0L, 1L, 0L, 0L, 3L, 6L, 32L, 15L, 7L, 0L, 0L, 73L, 137L, 4L, 9L, 0L, 9L, 14L, 66L, 95L, 47L, 5L, 0L, 0L, 257L, 506L, 2L, 6L, 2L, 1L, 4L, 0L, 0L, 0L, 0L, 0L, 54L, 69L, 52L, 75L, 0L, 60L, 109L, 528L, 480L, 207L, 29L, 0L, 0L, 1054L, 2594L, 1L, 0L, 0L, 0L, 0L, 3L, 0L, 0L, 24L, 28L, 13L, 9L, 0L, 9L, 23L, 65L, 80L, 67L, 5L, 0L, 0L, 203L, 474L, 4L, 10L, 12L, 13L, 14L, 16L, 6L, 0L, 0L, 0L, 118L, 193L, 2L, 5L, 30L, 35L, 10L, 6L, 1L, 0L, 0L, 0L, 25L, 114L, 0L, 20L, 12L, 1L, 0L, 0L, 6L, 39L, 3L, 1L, 0L, 0L, 0L, 4L, 8L, 0L, 3L, 0L, 4L, 9L, 26L, 14L, 9L, 4L, 66L, 135L), 2018 = c(15L, 7L, 0L, 13L, 70L, 158L, 178L, 27L, 0L, 0L, 0L, 705L, 1173L, 4L, 2L, 0L, 1L, 5L, 16L, 45L, 3L, 0L, 0L, 0L, 114L, 190L, 3L, 2L, 0L, 5L, 24L, 71L, 82L, 16L, 0L, 0L, 0L, 333L, 536L, 2L, 2L, 1L, 3L, 3L, 4L, 1L, 0L, 0L, 0L, 47L, 63L, 50L, 34L, 0L, 41L, 164L, 589L, 486L, 70L, 0L, 0L, 0L, 1286L, 2720L, 2L, 0L, 0L, 1L, 1L, 1L, 0L, 0L, 28L, 33L, 8L, 4L, 0L, 5L, 24L, 77L, 108L, 9L, 0L, 0L, 0L, 271L, 506L, 3L, 3L, 5L, 17L, 22L, 10L, 3L, 0L, 0L, 0L, 126L, 189L, 1L, 2L, 31L, 29L, 15L, 2L, 1L, 0L, 0L, 0L, 48L, 129L, 6L, 17L, 20L, 1L, 2L, 0L, 3L, 49L, 8L, 1L, 0L, 1L, 0L, 8L, 18L, 1L, 3L, 0L, 1L, 14L, 71L, 34L, 8L, 0L, 104L, 236L), 2019 = c(8L, 4L, 0L, 19L, 89L, 217L, 73L, 0L, 0L, 0L, 0L, 1081L, 1491L, 1L, 2L, 0L, 1L, 3L, 18L, 7L, 0L, 0L, 0L, 0L, 135L, 167L, 2L, 1L, 0L, 9L, 45L, 84L, 27L, 0L, 0L, 0L, 0L, 473L, 641L, 0L, 0L, 2L, 5L, 1L, 1L, 0L, 0L, 0L, 0L, 40L, 49L, 15L, 26L, 0L, 54L, 170L, 628L, 181L, 0L, 0L, 0L, 0L, 2063L, 3137L, 1L, 2L, 1L, 0L, 1L, 0L, 0L, 0L, 14L, 19L, 7L, 1L, 0L, 17L, 27L, 106L, 31L, 0L, 0L, 0L, 0L, 431L, 620L, 3L, 5L, 18L, 62L, 27L, 4L, 0L, 0L, 0L, 0L, 221L, 340L, 1L, 2L, 30L, 43L, 16L, 2L, 0L, 0L, 0L, 0L, 43L, 137L, 1L, 25L, 29L, 3L, 0L, 1L, 11L, 70L, 7L, 1L, 1L, 0L, 0L, 8L, 17L, 0L, 1L, 0L, 3L, 8L, 63L, 14L, 0L, 0L, 110L, 199L), 2020 = c(3L, 4L, 0L, 14L, 50L, 38L, 0L, 0L, 0L, 0L, 0L, 791L, 900L, 0L, 0L, 0L, 0L, 4L, 1L, 0L, 0L, 0L, 0L, 0L, 85L, 90L, 1L, 0L, 0L, 2L, 14L, 9L, 0L, 0L, 0L, 0L, 0L, 322L, 348L, 2L, 1L, 2L, 2L, 0L, 0L, 0L, 0L, 0L, 0L, 28L, 35L, 10L, 22L, 0L, 45L, 126L, 110L, 0L, 0L, 0L, 0L, 0L, 1742L, 2055L, 0L, 1L, 0L, 0L, 1L, 0L, 0L, 0L, 11L, 13L, 0L, 4L, 0L, 5L, 22L, 15L, 0L, 0L, 0L, 0L, 0L, 313L, 359L, 0L, 8L, 25L, 17L, 6L, 0L, 0L, 0L, 0L, 0L, 119L, 175L, 0L, 1L, 20L, 42L, 4L, 0L, 0L, 0L, 0L, 0L, 38L, 105L, 5L, 20L, 36L, 4L, 0L, 0L, 10L, 75L, 1L, 2L, 0L, 0L, 0L, 4L, 7L, 0L, 0L, 0L, 0L, 5L, 6L, 0L, 0L, 0L, 131L, 142L), 2021 = c(2L, 6L, 0L, 15L, 14L, 0L, 0L, 0L, 0L, 0L, 0L, 1455L, 1492L, 0L, 0L, 0L, 3L, 3L, 0L, 0L, 0L, 0L, 0L, 0L, 246L, 252L, 2L, 2L, 0L, 2L, 5L, 0L, 0L, 0L, 0L, 0L, 0L, 527L, 538L, 0L, 0L, 3L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 31L, 34L, 15L, 17L, 0L, 47L, 25L, 0L, 0L, 0L, 0L, 0L, 0L, 2854L, 2958L, 4L, 0L, 2L, 0L, 0L, 0L, 0L, 0L, 35L, 41L, 0L, 0L, 0L, 6L, 10L, 0L, 0L, 0L, 0L, 0L, 0L, 469L, 485L, 3L, 22L, 32L, 9L, 0L, 0L, 0L, 0L, 0L, 0L, 183L, 249L, 3L, 3L, 13L, 16L, 0L, 0L, 0L, 0L, 0L, 0L, 95L, 130L, 7L, 12L, 55L, 1L, 0L, 0L, 26L, 101L, 3L, 8L, 0L, 0L, 0L, 4L, 15L, 0L, 1L, 0L, 3L, 0L, 0L, 0L, 0L, 0L, 226L, 230L), 2022 = c(1L, 3L, 0L, 2L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1588L, 1594L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 190L, 192L, 1L, 1L, 0L, 3L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 451L, 456L, 0L, 4L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 40L, 44L, 12L, 14L, 0L, 9L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 3091L, 3126L, 3L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 39L, 42L, 0L, 1L, 0L, 3L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 478L, 482L, 4L, 29L, 15L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 237L, 285L, 5L, 2L, 3L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 121L, 121L), 2023 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1400L, 1400L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 158L, 158L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 587L, 587L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 43L, 44L, 6L, 11L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 3234L, 3251L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 15L, 15L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 613L, 615L, 8L, 6L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 107L, 110L, 0L, 2L, 0L, 0L, 0L, 0L, 27L, 29L, 4L, 3L, 0L, 0L, 0L, 2L, 9L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 311L, 311L), 2024 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 1197L, 1197L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 150L, 150L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 464L, 464L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 29L, 29L, 1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 2925L, 2926L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 14L, 14L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 519L, 519L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 84L, 84L, 0L, 0L, 0L, 0L, 0L, 0L, 14L, 14L, 1L, 0L, 0L, 0L, 0L, 10L, 11L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 283L, 283L)), row.names = c(NA, -137L), class = c("tbl_df", "tbl", "data.frame")).

Возможно, я не совсем понимаю цель здесь, но подход ниже суммирует каждую строку значений в столбцах с 2014 по 2023 и затем для каждой строки TRADE|CofQYr показывает отношение суммы этой строки к соответствующей строке, где CofQYr равен "Total." Библиотека(tidyverse) df |> pivot_longer(-c(1:2)) |> summarize(value = sum(value), .by = c(TRADE, CofQYr)) |> arrange(TRADE, as.numeric(CofQYr)) |> mutate(pct = value / sum(value[CofQYr == "Total"]), .by = TRADE). Результат (первые 20 строк) TRADE CofQYr value pct 1 306A 0 207 0.0147 2 306A 1 172 0.0122 3 306A 2 240 0.0171 4 306A 3 578 0.0411 5 306A 4 1171 0.0833 6 306A 5 798 0.0568 7 306A 6 323 0.0230 8 306A 7 178 0.0127 9 306A 8 65 0.00462 10 306A 9 11 0.000782 11 306A 10 2 0.000142 12 306A 99999 10316 0.734 13 306A Total 14061 1 14 307A 0 10 0.00547 15 307A 1 13 0.00711 16 307A 2 11 0.00602 17 307A 3 38 0.0208 18 307A 4 106 0.0580 19 307A 5 144 0.0788 20 307A 6 63 0.0345 … Делает ли это то, что вы ищете? df$Percentage <- rowSums(df[as.character(yrs)]) / rowSums(outer(df$CofQYr, yrs, FUN = function(x, y) y <= (yrs[length(yrs)]-as.integer(x))) * as.matrix(df[as.character(yrs)])[match(df$TRADE, ifelse(df$CofQYr=="Total", df$TRADE, NA)),]) .

Ответ или решение

Для выполнения поставленной задачи вычисления уменьшающегося скользящего процента с фиксированным стартовым моментом в фрейме данных в R, мы можем использовать пакет dplyr из tidyverse. Давайте разберем данный процесс по шагам.

Задача

У вас есть фрейм данных, и вы хотите добавить новый столбец Percentage, который будет рассчитываться следующим образом:

Для строк, где CofQYr == 0, процент будет равен общей сумме по всем колонкам (с 2014 по 2024) делённой на сумму по строке, где CofQYr == "Total" (также с 2014 по 2024).
Для строк, где CofQYr == 1, процент будет равен общей сумме по всем колонкам (с 2014 по 2023) делённой на сумму по строке, где CofQYr == "Total" (также с 2014 по 2023).
Для строк, где CofQYr == 9, процент будет равен общей сумме по всем колонкам (только 2014) делённой на сумму по строке, где CofQYr == "Total" (только 2014).

Импорт библиотек и загрузка данных

Сначала загрузим необходимые библиотеки:

library(dplyr)
library(tidyr)

Далее можно создать пример фрейма данных на основании предоставленных вами данных:

df <- structure(list(
  TRADE = c("306A", "306A", ..., "427A"), # Упрощено для демонстрации
  CofQYr = c("0", "1", "10", ..., "Total"),
  `2014` = c(77L, 60L, ..., 77L),
  `2015` = c(46L, 30L, ..., 207L), # и т.д. 
  `2024` = c(0L, 0L, ..., 0L)
), row.names = c(NA, -137L), class = c("tbl_df", "tbl", "data.frame"))

Рассчёт процента

Теперь создадим новый столбец Percentage, следуя описанной логике:

# Определим книги лет
years <- 2014:2024

df <- df %>%
  # Соберем данные в длинный формат
  pivot_longer(cols = all_of(as.character(years)), names_to = "Year", values_to = "Value") %>%

  # Суммаризация по TRADE и CofQYr
  group_by(TRADE, CofQYr) %>%
  summarize(TotalValue = sum(Value), .groups = 'drop') %>%

  # Вычисляем процент
  mutate(Percentage = case_when(
    CofQYr == "Total" ~ 1,
    CofQYr == "0" ~ TotalValue / TotalValue[CofQYr == "Total"],
    CofQYr == "1" ~ TotalValue / sum(TotalValue[CofQYr %in% c("0", "1", "Total")]),
    CofQYr == "9" ~ TotalValue / TotalValue[Year == "2014" & CofQYr == "Total"],
    TRUE ~ NA_real_
  )) %>%

  # Проводим обратное преобразование в широкий формат 
  pivot_wider(names_from = Year, values_from = TotalValue) %>%

  # Оставляем лишь нужные столбцы
  select(TRADE, CofQYr, everything(), Percentage)

print(df)

Примечания

Сортировка: Данный код автоматизирует группировку и агрегацию по каждому TRADE и CofQYr.
Расчёт: Для каждой строки определяется процент относительно соответствующей строки с CofQYr = "Total".

Заключение

Воплотив данные шаги, вы получите столбец с процентами, который будет показывать их изменение в зависимости от значения CofQYr. Важно обращать внимание на корректность подбора индексов и работу с фильтрацией, чтобы избежать ошибок в агрегации. Настоящий пример демонстрирует основы подхода, который можно адаптировать в зависимости от специфики данных.