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
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
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
or
population %>%
tidyr::pivot_longer( cols = starts_with( 'X' ), names_to = "year", values_to = "population", ) %>%
dplyr::mutate( year = as.integer( year ) ) -> test
Thanks!
DATA:
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))
