使用Pivot_longer将R中的多列合为一 [英] Using pivot_longer to turn multiple columns into one in R
本文介绍了使用Pivot_longer将R中的多列合为一的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个df 人口，看起来像这样(并非列出的所有列和行):
I have a df population that looks something like this (not all columns and rows listed):
Region X1975 X1976 X1977 ... X2008
National Total 942420 93717 94974 132802
Bejing 844.4 845.10 860.50 1695
Tianjin 702.86 706.50 712.87 968.87
Hebei 4913 4943 4998 6989
...
sum 91979 93275 94540 132058
difference 440 441 433 743
各列从 X1975 到 X2008 ，并且每年之间.我需要的是人口看起来像这样:
The columns go from X1975 to X2008 and have every year in between. What I need is for the population to look like this:
Region Year Population
Bejing 1975 844.4
Bejing 1976 845.10
Bejing 1977 860.50
Bejing 1978 871.50
以及每个区域(包括国家总数，总和和差额)的持续变化，并且不包括2000年及以下的年份.想使用pivot_longer来做到这一点，但是我不确定如何做.到目前为止，这是我所得到的，但这是行不通的.
and on and on for every Region (including National Total, sum, and difference) and for years 2000 and up to not be included. Would like to do this using pivot_longer, but I'm not sure how. This is what I've gotten so far, which does not work.
population %>%
tidyr::pivot_longer( cols = starts_with( 'X' ), names_to = c( ".value", "year" ), names_sep = "(?<=[a-z])(?=[0-9])" ) %>%
dplyr::mutate( year = as.integer( year ) ) -> test
或
population %>%
tidyr::pivot_longer( cols = starts_with( 'X' ), names_to = "year", values_to = "population", ) %>%
dplyr::mutate( year = as.integer( year ) ) -> test
谢谢！
数据:
dput(population_to_1999)
structure(list(Region = structure(c(21L, 2L, 30L, 11L, 27L, 16L,
20L, 19L, 12L, 26L, 17L, 34L, 1L, 5L, 18L, 25L, 13L, 14L, 15L,
7L, 8L, 10L, 3L, 28L, 9L, 33L, 31L, 24L, 6L, 23L, 22L, 32L, 29L,
4L), .Label = c("Anhui", "Beijing", "Chongqing", "Difference",
"Fujian", "Gansu", "Guangdong", "Guangxi", "Guizhou", "Hainan",
"Hebei", "Heilongjiang", "Henan", "Hubei", "Hunan", "Inner Mongolia",
"Jiangsu", "Jiangxi", "Jilin", "Liaoning", "National Total",
"Ningxia", "Qinghai", "Shaanxi", "Shandong", "Shanghai", "Shanxi",
"Sichuan", "sum", "Tianjin", "Tibet", "Xinjiang", "Yunnan", "Zhejiang"
), class = "factor"), X1975 = c(92420, 844.4, 702.86, 4913, 2340,
1737.9, 3282, 2063.9, 2958.1, 1076.72, 5636.12, 3614.47, 4492,
2297, 2968.5, 6971, 6758, 4408.15, 4991.36, 4858.48, 3201, 496.82,
2592.59, 6874.7, 2530.95, 2884.29, 169.11, 2692, 1804.02, 337.49,
327.92, 1154.53, 91979.38, 440.62), X1976 = c(93717, 845.1, 706.5,
4943, 2373.1, 1769.2, 3311, 2092.6, 3019.4, 1081.3, 5700.76,
3662.82, 4558, 2351, 3048.2, 7038, 6852, 4466.7, 5056.81, 4921.95,
3267, 505.15, 2615.57, 6963, 2585.11, 2951.75, 172.4, 2722, 1825.96,
346.58, 337.93, 1185.8, 93275.69, 441.31), X1977 = c(94974, 860.5,
712.87, 4998, 2398.4, 1798.1, 3345, 2117.9, 3072.5, 1086.47,
5765.28, 3707.4, 4628, 2402, 3118, 7099, 6957, 4520.68, 5111.83,
4985.54, 3329, 516.36, 2628.06, 7031.3, 2640.14, 3024.59, 175.62,
2751, 1847.46, 356.75, 346.71, 1208.97, 94540.43, 433.57), X1978 = c(96259,
871.5, 724.27, 5057, 2423.6, 1823.4, 3394, 2149.3, 3129.6, 1098.28,
5834.33, 3750.96, 4713, 2446, 3182.8, 7160, 7067, 4574.91, 5165.91,
5064.15, 3402, 528.45, 2635.56, 7071.9, 2686.4, 3091.47, 178.82,
2779, 1870.05, 364.86, 355.58, 1233.01, 95827.11, 431.89), X1979 = c(97542,
897.1, 739.42, 5105, 2447.2, 1851.8, 3443, 2184.6, 3168.7, 1132.14,
5892.55, 3792.33, 4803, 2487, 3229, 7232, 7189, 4632.78, 5223.05,
5140.5, 3470, 540.3, 2653.69, 7120.5, 2730.99, 3134.79, 182.69,
2807, 1893.79, 372.02, 364.14, 1255.97, 97116.05, 425.95), X1980 = c(98705,
904.3, 748.91, 5168, 2476.5, 1876.5, 3487, 2210.7, 3203.8, 1146.52,
5938.19, 3826.58, 4893, 2519, 3270.2, 7296, 7285, 4684.45, 5280.95,
5230, 3538, 552.53, 2664.79, 7154.8, 2776.67, 3173.39, 185.28,
2831, 1918.43, 376.9, 373.72, 1283.24, 98274.35, 430.65), X1981 = c(100072,
919.2, 760.32, 5256, 2508.8, 1902.9, 3535, 2230.9, 3239.3, 1162.84,
6010.24, 3871.51, 4957, 2563, 3303.9, 7395, 7397, 4740.35, 5360.05,
5326.97, 3613, 560.77, 2694.05, 7215.6, 2826.78, 3222.77, 185.96,
2865, 1941.4, 381.6, 383.38, 1303.05, 99633.64, 438.36), X1982 = c(101654,
935, 774.92, 5356, 2546, 1941.6, 3592, 2257.6, 3281.1, 1180.51,
6088.94, 3924.32, 5016, 2620, 3348.3, 7494, 7519, 4800.92, 5452.12,
5419.35, 3684, 571.38, 2721.69, 7300.4, 2875.21, 3283.1, 189.25,
2904, 1974.88, 392.79, 393.04, 1315.9, 101153.32, 500.68), X1983 = c(103008,
950, 785.28, 5420, 2588.4, 1969.8, 3629, 2269.5, 3306, 1194.01,
6134.99, 3963.1, 5056, 2668, 3394.5, 7564, 7632, 4865.73, 5509.43,
5501.85, 3733, 580.66, 2738.63, 7336.9, 2901.46, 3330.8, 193.14,
2931, 1999.84, 392.57, 399.05, 1333.3, 102271.94, 736.06), X1984 = c(104357,
965, 795.52, 5487, 2631.5, 1993.1, 3655, 2284.5, 3331, 1204.78,
6171.43, 3993.09, 5103, 2720, 3457.9, 7637, 7737, 4917.75, 5561.32,
5585.61, 3806, 589.31, 2747.75, 7364, 2931.85, 3372.1, 196.68,
2966, 2025.88, 401.61, 406.87, 1344.08, 103383.63, 973.37), X1985 = c(105851,
981, 804.8, 5548, 2673.5, 2015.9, 3686, 2298, 3357, 1216.69,
6213.48, 4029.56, 5156, 2769, 3509.8, 7711, 7847, 4980.19, 5622.49,
5670.65, 3873, 597.51, 2768.26, 7419.3, 2972.18, 3418.1, 199.48,
3002, 2052.89, 407.38, 414.62, 1361.14, 104575.92, 1275.08),
X1986 = c(107507, 1028, 814.97, 5627, 2713.5, 2040.7, 3726,
2315.3, 3385, 1232.33, 6269.9, 4070.07, 5217, 2820, 3575.8,
7818, 7985, 5047.83, 5695.73, 5799.75, 3946, 605.63, 2807.6,
7511.9, 3025.86, 3480, 202.49, 3042, 2085.39, 421.12, 424.33,
1383.64, 106117.84, 1389.16), X1987 = c(109300, 1047, 828.73,
5710, 2758.1, 2066.4, 3777, 2336.4, 3424, 1249.51, 6348,
4121.19, 5287, 2875, 3632.3, 7958, 8148, 5120.27, 5782.61,
5931.79, 4016, 615.08, 2845.14, 7613.2, 3072.58, 3534, 207.95,
3088, 2115.73, 427.9, 435.16, 1406.33, 107778.37, 1521.63
), X1988 = c(111026, 1061, 839.21, 5795, 2807.2, 2093.9,
3826, 2357.4, 3466, 1262.42, 6438.27, 4169.85, 5377, 2929,
3683.9, 8061, 8317, 5184.94, 5915.68, 6066.84, 4088, 626.85,
2873.34, 7716.4, 3127.27, 3594, 212.31, 3140, 2148.15, 434.2,
444.53, 1426.42, 109483.08, 1542.92), X1989 = c(112704, 1075,
852.35, 5881, 2853, 2122.2, 3876, 2395.4, 3510, 1276.45,
6535.85, 4208.88, 5469, 2984, 3746.2, 8160, 8491, 5258.83,
6013.62, 6204.96, 4150, 639.2, 2897.01, 7803.2, 3171, 3648,
215.91, 3198, 2184.86, 440.2, 454.81, 1454.16, 111170.09,
1533.91), X1990 = c(114333, 1086, 866.25, 6159, 2899, 2162.6,
3917, 2440.2, 3543, 1283.35, 6766.9, 4238, 5661, 3037, 3810.6,
8493, 8649, 5439.29, 6110.89, 6347.19, 4242, 662.77, 2920.9,
7892.5, 3267.53, 3730.6, 221.47, 3316, 2254.67, 447.66, 465.68,
1529.16, 113860.21, 472.79), X1991 = c(115823, 1094, 872.63,
6220, 2941.9, 2183.9, 3939, 2459.7, 3575, 1287.2, 6843.7,
4269.5, 5744, 3079, 3864.6, 8570, 8763, 5512.33, 6166.33,
6527.01, 4324, 674.13, 2938.99, 7947.8, 3314.63, 3782.1,
225.03, 3363, 2284.92, 454.43, 473.88, 1554.57, 115250.28,
572.72), X1992 = c(117171, 1102, 878.97, 6275, 2979.3, 2206.6,
3958, 2474, 3608, 1289.37, 6911.2, 4304.4, 5817, 3116, 3913.1,
8610, 8861, 5579.85, 6207.78, 6706.45, 4380, 686.4, 2950.78,
7992.2, 3360.96, 3831.6, 228.53, 3405, 2314.19, 461.02, 482.27,
1580.63, 116471.6, 699.4), X1993 = c(118517, 1112, 885.89,
6334, 3012.6, 2232.4, 3983, 2496.1, 3640, 1294.74, 6967.27,
4334.8, 5870, 3150, 3966, 8642, 8946, 5653.48, 6245.58, 6936.69,
4438, 701, 2964.92, 8037.4, 3408.69, 3885.2, 232.22, 3443,
2345.23, 466.7, 490.86, 1605.26, 117721.03, 795.97), X1994 = c(119850,
1125, 890.55, 6388, 3045.2, 2260.5, 4007, 2515.6, 3672, 1298.81,
7020.54, 4363.7, 5938, 3183, 4015.4, 8671, 9027, 5718.81,
6302.58, 7209.58, 4493, 711.39, 2985.59, 8098.7, 3458.41,
3939.2, 236.14, 3481, 2387.25, 474, 503.87, 1632.7, 119053.52,
796.48), X1995 = c(121121, 1251.1, 894.67, 6437, 3077.3,
2284.4, 4034, 2550.9, 3701, 1301.37, 7066.02, 4389, 6000,
3227, 4062.5, 8705, 9100, 5772.07, 6392, 7387.49, 4543, 723.79,
3001.77, 8161.2, 3508.08, 3989.6, 239.84, 3513, 2437.95,
481.2, 512.38, 1661.35, 120405.98, 715.02), X1996 = c(122389,
1259.4, 898.45, 6484, 3109.3, 2306.6, 4057, 2579.1, 3728,
1304.43, 7110.16, 4413, 6054, 3261, 4105.5, 8738, 9172, 5825.13,
6428, 7569.78, 4589, 734.14, 2875.3, 8215.4, 3555.41, 4041.5,
243.7, 3543, 2466.86, 488.3, 521.21, 1689.29, 121365.96,
1023.04), X1997 = c(123626, 1240, 899.8, 6525, 3140.9, 2325.7,
4077, 2600.1, 3751, 1305.46, 7147.86, 4434.8, 6109, 3282,
4150.3, 8785, 9243, 5872.6, 6465, 7779.69, 4633, 743, 2873.36,
8264.7, 3605.81, 4094, 247.6, 3570, 2494.2, 495.6, 528.94,
1718.08, 122402.5, 1223.5), X1998 = c(124761, 1245.6, 905.09,
6569, 3172.2, 2344.9, 4090, 2603.2, 3773, 1306.58, 7182.46,
4456.2, 6152, 3299, 4191.2, 8838, 9315, 5907.23, 6502, 7990.03,
4675, 752.82, 2870.75, 8315.7, 3657.6, 4143.8, 251.54, 3596,
2519.37, 502.8, 536.57, 1747.35, 123411.99, 1349.01), X1999 = c(125786,
1257.2, 910.17, 6614, 3203.6, 2361.9, 4103, 2616.1, 3792,
1313.12, 7213.13, 4475.4, 6205, 3316, 4231.2, 8883, 9387,
5938.03, 6532, 8217.91, 4713, 761.93, 2860.37, 8358.6, 3710.06,
4192.4, 255.51, 3618, 2542.58, 509.8, 543.29, 1775, 124410.3,
1375.7), X2000 = c(126743, 1363.6, 912, 6674, 3247.8, 2372.4,
4135, 2627.3, 3807, 1321.63, 7327.24, 4679.91, 6278, 3410,
4148.5, 8997, 9488, 5960, 6562.05, 8650.03, 4751, 788.05,
2848.82, 8407.5, 3755.72, 4240.8, 259.83, 3644, 2556.89,
516.5, 554.32, 1849.41, 126134.3, 608.7), X2001 = c(127627,
1385.1, 913.98, 6699, 3271.6, 2377.5, 4147, 2637.1, 3811,
1327.14, 7354.92, 4697.27, 6325, 3440, 4185.8, 9041, 9555,
5974.56, 6595.85, 8733.18, 4788, 795.55, 2829.21, 8436.6,
3798.51, 4287.4, 262.95, 3659, 2575.24, 523.1, 563.22, 1876.19,
126866.97, 760.03), X2002 = c(128453, 1423.2, 919.05, 6735,
3293.7, 2378.6, 4155, 2649.4, 3813, 1334.23, 7380.97, 4730.76,
6369, 3466, 4222.4, 9082, 9613, 5987.8, 6628.5, 8842.08,
4822, 803.13, 2814.83, 8474.5, 3837.28, 4333.1, 266.88, 3674,
2592.58, 528.6, 571.54, 1905.19, 127647.32, 805.68), X2003 = c(129227,
1456.4, 926, 6769, 3314.3, 2379.6, 4162, 2658.6, 3815, 1341.77,
7405.82, 4763.46, 6410, 3488, 4254.2, 9125, 9667, 6001.7,
6662.8, 8962.69, 4857, 810.52, 2803.19, 8529.4, 3869.66,
4375.6, 270.17, 3690, 2603.34, 533.8, 580.19, 1933.95, 128420.16,
806.84), X2004 = c(129988, 1492.7, 932.55, 6809, 3335.1,
2384.4, 4173, 2661.9, 3816.8, 1352.39, 7432.5, 4803.48, 6461,
3511, 4283.6, 9180, 9717, 6016.1, 6697.7, 9110.66, 4889,
817.83, 2793.32, 8595.3, 3903.7, 4415.2, 273.68, 3705, 2618.78,
538.6, 587.71, 1963.11, 129272.11, 715.89), X2005 = c(130756,
1538, 939.31, 6851, 3355.2, 2386.4, 4221, 2669.4, 3820, 1360.26,
7474.5, 4898, 6516, 3535, 4311.2, 9248, 9768, 6031, 6732.1,
9194, 4660, 828, 2798, 8212, 3730, 4450.4, 277, 3720, 2594.36,
543.2, 596.2, 2010.35, 129267.88, 1488.12), X2006 = c(131448,
1581, 948.89, 6898, 3374.6, 2392.4, 4271, 2679.5, 3823, 1368.08,
7549.5, 4980, 6593, 3558, 4339.1, 9309, 9820, 6050, 6768.1,
9304, 4719, 835.88, 2808, 8169, 3757.18, 4483, 281, 3735,
2606.25, 547.7, 603.73, 2050, 130202.91, 1245.09), X2007 = c(132129,
1633, 959.1, 6943, 3392.6, 2405.1, 4298, 2696.1, 3824, 1378.86,
7624.5, 5060, 6676, 3581, 4368.4, 9367, 9869, 6070, 6805.7,
9449, 4768, 845.03, 2816, 8127, 3762.36, 4514, 284.15, 3748,
2617.16, 551.6, 610.25, 2095.19, 131139.1, 989.9), X2008 = c(132802,
1695, 968.87, 6989, 3410.6, 2413.7, 4315, 2710.5, 3825, 1391.04,
7676.5, 5120, 6741, 3604, 4400.1, 9417, 9918, 6110.8, 6845.2,
9544, 4816, 854.18, 2839, 8138, 3793, 4543, 287.08, 3762,
2628.12, 554.3, 617.69, 2130.81, 132058.49, 743.51)),
class = "data.frame", row.names = c(NA, -34L))
推荐答案
在此解决方案中，在调用 pivot_longer 之前，使用 num_range 进行整洁的列选择:
Here a solution that makes use of num_range for tidy selection of the columns before the call to pivot_longer:
library(dplyr)
library(tidyr)
df_long <- df %>%
select(Region, num_range("X", 1975:1999)) %>%
pivot_longer(-Region,
names_to = "Year",
names_prefix = "X",
values_to = "Population")
> df_long
# A tibble: 850 x 3
Region Year Population
<fct> <chr> <dbl>
1 National Total 1975 92420
2 National Total 1976 93717
3 National Total 1977 94974
4 National Total 1978 96259
5 National Total 1979 97542
6 National Total 1980 98705
7 National Total 1981 100072
8 National Total 1982 101654
9 National Total 1983 103008
10 National Total 1984 104357
# ... with 840 more rows
这篇关于使用Pivot_longer将R中的多列合为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
