Блог

Главная - Вопросы по программированию - Обработка данных с помощью DPLYR с использованием нескольких столбцов идентификаторов, а затем суммы значений из разных строк в зависимости от категорий. Значения находятся в одном столбце

Обработка данных с помощью DPLYR с использованием нескольких столбцов идентификаторов, а затем суммы значений из разных строк в зависимости от категорий. Значения находятся в одном столбце

#r #dataframe #dplyr #tidyverse #tidyr

#r #фрейм данных #dplyr #tidyverse #tidyr

Вопрос:

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

Подсчет данных по таблице

Цель состоит в том, чтобы попытаться создать столбцы для каждой категории — ПОЛ = M, ПОЛ = F, РАСА = БЕЛЫЙ, РАСА = ЧЕРНЫЙ, РАСА = АМЕРИКАНСКИЙ ИНДЕЕЦ / УРОЖЕНЕЦ АЛЯСКИ, РАСА = ВЫХОДЕЦ ИЗ АЗИИ ИЛИ ТИХОГО ОКЕАНА, РАСА = НЕИЗВЕСТНО, HISPANIC_FLAG = 0, HISPANIC_FLAG = 1, где значения для каждого столбца будутбыть суммой столбца COUNT для каждой категории.

В настоящее время для каждого SEX = M он разбит по расе и латиноамериканскому флагу, и я хочу, чтобы это суммировалось, и аналогично я хочу, чтобы для SEX = M, а затем я пытаюсь суммировать RACE = White, RACE = Black и т.д. аналогичным образом (который игнорирует SEX = M, поэтому он просто объединяет его в своем собственном столбце).

Затем в каждой строке мне нужен уникальный идентификатор. Я пытаюсь иметь только один FIPS / YEAR / ONE / TWO / THREE / etc. быть моими уникальными идентификаторами (обратите внимание, что ОДИН / ДВА / ТРИ / и т.д. одинаковы, если FIPS / YEAR одинаковы).

Я пытался работать с некоторой комбинацией pivot_wider() и пробовал оба метода names_from и values_from:

 my_pivot = my_sf %>% 
  pivot_wider(names_from =c("SEX", "RACE_RECODE_W_B_AI_API", "HISPANIC_FLAG"), values_from="COUNT")

и этот, который, казалось, создал ~ 4500 новых строк

 my_pivot = my_sf %>%
 pivot_wider(id_cols=c("FIPS","YEAR", "ONE", "TWO", "THREE", "FOUR", "FIVE"), names_from =c("SEX", "RACE_RECODE_W_B_AI_API", "HISPANIC_FLAG"), values_from= "COUNT")

Я также пробовал некоторые агрегаты, которые я могу использовать для одного столбца, но не для всех из них. Есть ли быстрый и простой способ сделать это в dplyr или tidyr, который мне не хватает? Если это помогает, я также иногда кодирую NAs в этом как 0, но это все равно, похоже, не имеет большого значения. Спасибо!

Работая над получением вывода dput здесь, с включенной структурой, он имеет длину ~ 60 тыс. символов со 100 значениями, и даже при всего 40 значениях он составляет ~ 45 тыс. символов, поэтому я просто использую этот список:

 structure(list(X1 = c(171, 188, 205, 222, 239, 256, 273, 290, 
307, 324, 341, 358, 375, 392, 409, 426, 443, 460, 477, 494, 681, 
698, 715, 732, 749, 766, 783, 800, 817, 834, 851, 868, 885, 902, 
919, 936, 953, 970, 987, 1004, 1191, 1208, 1225, 1242, 1259, 
1276, 1293, 1310, 1327, 1344, 1361, 1378, 1395, 1412, 1429, 1446, 
1463, 1480, 1497, 172, 189, 206, 223, 240, 257, 274, 291, 308, 
325, 342, 359, 376, 393, 410, 427, 444, 461, 478, 495, 682, 699, 
716, 733, 750, 767, 784, 801, 818, 835, 852, 869, 886, 903, 920, 
937, 954, 971, 988, 1005, 1192), FIPS = c(6001, 6001, 6001, 6001, 
6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 
6001, 6001, 6001, 6001, 6001, 6013, 6013, 6013, 6013, 6013, 6013, 
6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 
6013, 6013, 6013, 6041, 6041, 6041, 6041, 6041, 6041, 6041, 6041, 
6041, 6041, 6041, 6041, 6041, 6041, 6041, 6041, 6041, 6041, 6041, 
6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 
6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6013, 6013, 
6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 
6013, 6013, 6013, 6013, 6013, 6013, 6013, 6041), SEX = c("M", 
"M", "M", "M", "M", "M", "M", "M", "M", "M", "F", "F", "F", "F", 
"F", "F", "F", "F", "F", "F", "M", "M", "M", "M", "M", "M", "M", 
"M", "M", "M", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", 
"M", "M", "M", "M", "M", "M", "M", "M", "M", "M", "F", "F", "F", 
"F", "F", "F", "F", "F", "F", "M", "M", "M", "M", "M", "M", "M", 
"M", "M", "M", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", 
"M", "M", "M", "M", "M", "M", "M", "M", "M", "M", "F", "F", "F", 
"F", "F", "F", "F", "F", "F", "F", "M"), RACE = c("White", "White", 
"Black", "Black", "American Indian/Alaska Native", "American Indian/Alaska Native", 
"Asian or Pacific Islander", "Asian or Pacific Islander", "Unknown", 
"Unknown", "White", "White", "Black", "Black", "American Indian/Alaska Native", 
"American Indian/Alaska Native", "Asian or Pacific Islander", 
"Asian or Pacific Islander", "Unknown", "Unknown", "White", "White", 
"Black", "Black", "American Indian/Alaska Native", "American Indian/Alaska Native", 
"Asian or Pacific Islander", "Asian or Pacific Islander", "Unknown", 
"Unknown", "White", "White", "Black", "Black", "American Indian/Alaska Native", 
"American Indian/Alaska Native", "Asian or Pacific Islander", 
"Asian or Pacific Islander", "Unknown", "Unknown", "White", "White", 
"Black", "Black", "American Indian/Alaska Native", "American Indian/Alaska Native", 
"Asian or Pacific Islander", "Asian or Pacific Islander", "Unknown", 
"Unknown", "White", "White", "Black", "Black", "American Indian/Alaska Native", 
"American Indian/Alaska Native", "Asian or Pacific Islander", 
"Asian or Pacific Islander", "Unknown", "White", "White", "Black", 
"Black", "American Indian/Alaska Native", "American Indian/Alaska Native", 
"Asian or Pacific Islander", "Asian or Pacific Islander", "Unknown", 
"Unknown", "White", "White", "Black", "Black", "American Indian/Alaska Native", 
"American Indian/Alaska Native", "Asian or Pacific Islander", 
"Asian or Pacific Islander", "Unknown", "Unknown", "White", "White", 
"Black", "Black", "American Indian/Alaska Native", "American Indian/Alaska Native", 
"Asian or Pacific Islander", "Asian or Pacific Islander", "Unknown", 
"Unknown", "White", "White", "Black", "Black", "American Indian/Alaska Native", 
"American Indian/Alaska Native", "Asian or Pacific Islander", 
"Asian or Pacific Islander", "Unknown", "Unknown", "White"), 
    HISPANIC_FLAG = c(0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 
    1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 
    0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 
    1, 0, 1, 0, 1, 0, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 
    1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 
    0, 1, 0, 1, 0, 1, 0, 1, 0, 1, 0), YEAR = c(2000, 2000, 2000, 
    2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
    2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
    2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
    2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
    2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
    2000, 2000, 2000, 2000, 2000, 2000, 2001, 2001, 2001, 2001, 
    2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 
    2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 
    2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 
    2001, 2001, 2001, 2001, 2001, 2001, 2001), COUNT = c(1784, 
    244, 499, 7, 6, 1, 417, 0, 56, 8, 1675, 263, 470, 4, 6, 0, 
    395, 2, 36, 1, 1649, 129, 171, 1, 6, 0, 148, 1, 18, 2, 1678, 
    161, 149, 4, 7, 0, 180, 2, 18, 5, 632, 18, 17, 2, 0, 0, 19, 
    0, 4, 0, 581, 27, 12, 1, 3, 0, 23, 0, 1, 1732, 265, 495, 
    1, 4, 0, 399, 1, 41, 2, 1635, 227, 439, 2, 6, 0, 421, 1, 
    36, 5, 1721, 153, 176, 3, 9, 0, 180, 1, 27, 3, 1672, 158, 
    181, 3, 4, 0, 212, 2, 24, 2, 671), ONE = c(290.4, 290.4, 
    290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 
    290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 290.4, 
    2940, 2940, 2940, 2940, 2940, 2940, 2940, 2940, 2940, 2940, 
    2940, 2940, 2940, 2940, 2940, 2940, 2940, 2940, 2940, 2940, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, 430.2, 430.2, 430.2, 430.2, 430.2, 430.2, 
    430.2, 430.2, 430.2, 430.2, 430.2, 430.2, 430.2, 430.2, 430.2, 
    430.2, 430.2, 430.2, 430.2, 430.2, 1555.1, 1555.1, 1555.1, 
    1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 
    1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 1555.1, 
    1555.1, NA), TWO = c(16, 16, 16, 16, 16, 16, 16, 16, 16, 
    16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 16, 92.8, 92.8, 92.8, 
    92.8, 92.8, 92.8, 92.8, 92.8, 92.8, 92.8, 92.8, 92.8, 92.8, 
    92.8, 92.8, 92.8, 92.8, 92.8, 92.8, 92.8, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 
    83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 83.5, 
    16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 
    16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 16.2, 
    NA), THREE = c(45.3, 45.3, 45.3, 45.3, 45.3, 45.3, 45.3, 
    45.3, 45.3, 45.3, 45.3, 45.3, 45.3, 45.3, 45.3, 45.3, 45.3, 
    45.3, 45.3, 45.3, 802.8, 802.8, 802.8, 802.8, 802.8, 802.8, 
    802.8, 802.8, 802.8, 802.8, 802.8, 802.8, 802.8, 802.8, 802.8, 
    802.8, 802.8, 802.8, 802.8, 802.8, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 64.2, 
    64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 
    64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 64.2, 1717.7, 
    1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 
    1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 1717.7, 
    1717.7, 1717.7, 1717.7, NA), FOUR = c(27, 27, 27, 27, 27, 
    27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 
    1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 
    1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 1258.5, 
    1258.5, 1258.5, 1258.5, 1258.5, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.7, 0.7, 
    0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 
    0.7, 0.7, 0.7, 0.7, 0.7, 0.7, 105.3, 105.3, 105.3, 105.3, 
    105.3, 105.3, 105.3, 105.3, 105.3, 105.3, 105.3, 105.3, 105.3, 
    105.3, 105.3, 105.3, 105.3, 105.3, 105.3, 105.3, NA), FIVE = c(NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 
    20, 20, 20, 20, 20, 20, 20, 20, 20, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 
    8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, 8.7, NA
    )), row.names = c(NA, -100L), class = c("tbl_df", "tbl", 
"data.frame"))

Вывод будет выглядеть примерно так

 structure(list(FIPS = c(6001, 6001, 6001, 6001, 6001, 6001, 6001, 
6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6001, 6013, 
6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 6013, 
6013, 6013, 6013, 6013, 6013), YEAR = c(2000, 2001, 2002, 2003, 
2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 
2015, 2016, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 
2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016), M = c(3022, 
2940, 2924, 2800, 2872, 2864, 2945, 3143, 3042, 3156, 3165, 3113, 
3127, 3242, 3153, 3177, 3299, 2125, 2273, 2190, 2311, 2282, 2340, 
2409, 2539, 2561, 2602, 2592, 2716, 2746, 2614, 2557, 2702, 2481
), White = c(3966, 3859, 3973, 3734, 3625, 3664, 3812, 4040, 
3961, 4009, 4032, 3938, 3911, 4159, 3935, 3946, 4044, 3617, 3704, 
3674, 3698, 3658, 3778, 3895, 3965, 4016, 4051, 4105, 4184, 4198, 
4000, 4133, 4162, 3910), Non_Hispanic = c(5344, 5208, 5323, 5109, 
5150, 5163, 5282, 5588, 5561, 5705, 5616, 5610, 5603, 5841, 5690, 
5885, 5862, 4024, 4206, 4126, 4155, 4189, 4249, 4397, 4441, 4502, 
4487, 4676, 4832, 4849, 4673, 4741, 4789, 4514), Hispanic = c(530, 
504, 594, 526, 553, 593, 659, 617, 651, 656, 721, 740, 751, 767, 
847, 772, 858, 300, 323, 348, 369, 359, 406, 417, 467, 510, 527, 
528, 555, 543, 556, 586, 645, 639), Black = c(980, 937, 950, 
933, 980, 995, 967, 980, 1001, 1018, 983, 988, 1047, 973, 972, 
954, 945, 325, 363, 385, 382, 424, 417, 421, 411, 479, 438, 499, 
491, 494, 513, 479, 499, 465), American_Indian = c(13, 10, 10, 
9, 22, 19, 16, 20, 20, 22, 20, 25, 22, 25, 22, 17, 25, 13, 13, 
10, 14, 9, 12, 17, 7, 8, 12, 15, 8, 17, 14, 32, 29, 14), Asian = c(814, 
822, 935, 880, 989, 952, 1027, 1095, 1179, 1240, 1199, 1291, 
1279, 1342, 1480, 1610, 1539, 331, 395, 360, 366, 366, 405, 428, 
495, 473, 457, 491, 546, 574, 590, 591, 655, 650), Unknown = c(101, 
84, 49, 79, 87, 126, 119, 70, 51, 72, 103, 108, 95, 109, 128, 
130, 167, 38, 54, 45, 64, 91, 43, 53, 30, 36, 56, 94, 158, 109, 
112, 92, 89, 114), F = c(2852, 2772, 2993, 2835, 2831, 2892, 
2996, 3062, 3170, 3205, 3172, 3237, 3227, 3366, 3384, 3480, 3421, 
2199, 2256, 2284, 2213, 2266, 2315, 2405, 2369, 2451, 2412, 2612, 
2671, 2646, 2615, 2770, 2732, 2672), ONE = c(290.4, 430.2, 226.7, 
173.7, 42.7, 276.7, 494.7, 256.8, 257.6, 29.3, 192.3, 7.6, 173.3, 
31.7, 54.7, 9.4, 41, 2940, 1555.1, 1453.5, 784.4, 669.9, 501.9, 
1419.8, 340, 425.5, 674.3, 451.8, 543.2, 86.1, 511.3, 291.4, 
290.5, 600.7), TWO = c(16, 83.5, 48.4, 28.9, 29.4, 20.7, 22.6, 
NA, NA, NA, NA, 0.5, 4.8, NA, 1.2, 1.6, NA, 92.8, 16.2, 61.2, 
54.2, 11.7, 128.4, 37, 97.2, 83.2, 57.2, 204.5, 94.1, 67.7, 18.6, 
19.5, 33.1, 8.2), THREE = c(45.3, 64.2, 8, 3.1, 0.9, 5.2, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 802.8, 1717.7, 329.8, 
69.8, 29.8, 407.1, 490.8, 601.9, 674.1, 345.5, 16.3, 242.7, 77.6, 
4.7, 85.5, NA, 308), FOUR = c(27, 0.7, 17.1, 130, 89.3, 53.3, 
65, 99.6, 108.6, NA, 13, 15.4, 4.7, NA, 28.9, 1.2, NA, 1258.5, 
105.3, 1106.6, 683.4, 850, 915, 636.4, 995.2, 1580.9, 678.6, 
194.9, 440.3, 758.2, 401.4, 862.4, 511.6, 387.8), FIVE = c(NA, 
NA, NA, NA, 3.9, NA, 11.9, NA, NA, NA, NA, 15.5, NA, NA, NA, 
NA, 1.5, 20, 8.7, 2.2, NA, 1.8, 4.1, 5.6, 26.9, 0.7, 38.5, 91.5, 
51.2, 1.5, 12.7, 56.8, 27.8, 1)), row.names = c(NA, -34L), class = c("tbl_df", 
"tbl", "data.frame"))

Комментарии:

1. если вы используете dput(head(yourdata, 100)) это, мы получим первые 100 строк в удобном формате, чтобы мы могли устранить неполадки — «другой микрофон»

2. Предоставленные dput вами данные не соответствуют скриншоту предоставленных вами данных … и ваш ожидаемый результат очень сложно определить. Можно сделать все, что вы перечислили здесь, но если вам нужна помощь, вам нужно сосредоточиться на том, чтобы сделать все более понятным. Ваши данные имеют переменные типа BROMOXYNIL и RACE_RECODE_W_B_AI_API .

3. Попробуйте сделать это выше — спасибо, что сообщили мне, как это затруднит

4. @ThatOneDudeMike образец данных намного лучше, но как должны выглядеть ваши окончательные данные?

5. Я думаю, что вывод должен быть похож на приведенный выше (этот вывод основан на первых 1000 строках, но несколько верхних строк будут такими, какими будут выходные данные для первых 100 строк, приведенных выше).

Ответ №1:

Если вам нужна одна строка для каждого fips и этих 5 других переменных, этот код может сработать. Этот код создаст уникальную строку на основе FIPS и столбцов один -пять, а затем для каждого уровня переменных с правой стороны он создаст столбец для каждой переменной в value.var аргументе. Например, когда race = white в нем будет два столбца: один для значения count_white и один для значения x1_white. вы можете удалить переменные из любой из приведенных ниже частей, чтобы получить разные строки / столбцы / значения в данных.

 library(data.table)


df2 <- dcast(
  setDT(df)
  , FIPS  ONE TWO THREE FOUR FIVE ~ SEX  RACE HISPANIC_FLAG YEAR
  ,value.var = c("COUNT","X1" ))

Комментарии:

1. К сожалению, это метод, который, похоже, также генерирует сотни столбцов для меня. Это расширяет мой набор данных с нескольких столбцов шириной до 517 столбцов.

Ответ №2:

В итоге я использовал решение одного из моих коллег, которое выделило данные и присоединилось к ним, где df — исходный csv

 df_other_vars = df %>% 
  select(-SEX, -RACE,
         -HISPANIC_FLAG, -COUNT) %>% 
  distinct()
​
#now carve out the demographic columns
df_demographics = df %>% 
  select(FIPS, YEAR, SEX, RACE,
         HISPANIC_FLAG, COUNT, POPULATION)
​
#recast Hispanic flag as factor
df_demographics$HISPANIC_FLAG = as.factor(df_demographics$HISPANIC_FLAG)
​
#pivot longer to get sex, race and hispanic flag all in one column
df_demographics = df_demographics %>% 
  pivot_longer(cols = c(SEX, RACE, HISPANIC_FLAG), 
               names_to = "STATUS")
​
​
#delete the STATUS column and pivot wider on the "value" column

df_demog_wide = df_demographics %>% 
  select(-STATUS) %>% 
  pivot_wider(names_from = value, 
              values_from = COUNT,
              values_fn = sum) 
​
#rename the Hispanic columns
df_demog_wide = df_demog_wide %>% 
  rename(COUNT_NON_HISPANIC = COUNT_0,
         COUNT_HISPANIC = COUNT_1)
​
#merge everything together: 
df_merged = left_join(df_demog_wide, df_other_vars, by = c("FIPS", "YEAR"))