Pivoting sf dataframe to make the data a suitable for creating maps

General
, , , , , ,
1

Below I have pasted a reproducible example of the dataset I am trying to work with. I am trying to follow: Chapter 2 Introduction to R | CASA0005 Geographic Information Systems and Science on how to tidy my dataset to make it suitable to create a map.

My issue is, the dataset I am trying to work with is individual level data, so there are many people from the same area code (all with different data attributes). Therefore, I would need to produces counts for each variable to make this work.


test2 <- structure(list(lad19cd = c("E06000052", "E06000052", "E06000052", 
"E06000052", "E06000052", "E06000052", "E06000052", "E06000052"
), objectid = c(49L, 49L, 49L, 49L, 49L, 49L, 49L, 49L), lad19nm = c("Cornwall", 
"Cornwall", "Cornwall", "Cornwall", "Cornwall", "Cornwall", "Cornwall", 
"Cornwall"), lad19nmw = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_
), bng_e = c(212497L, 212497L, 212497L, 212497L, 212497L, 212497L, 
212497L, 212497L), bng_n = c(64493L, 64493L, 64493L, 64493L, 
64493L, 64493L, 64493L, 64493L), long = c(-4.64254, -4.64254, 
-4.64254, -4.64254, -4.64254, -4.64254, -4.64254, -4.64254), 
    lat = c(50.450218, 50.450218, 50.450218, 50.450218, 50.450218, 
    50.450218, 50.450218, 50.450218), st_areasha = c(3548934061.31533, 
    3548934061.31533, 3548934061.31533, 3548934061.31533, 3548934061.31533, 
    3548934061.31533, 3548934061.31533, 3548934061.31533), st_lengths = c(1241482.9365225, 
    1241482.9365225, 1241482.9365225, 1241482.9365225, 1241482.9365225, 
    1241482.9365225, 1241482.9365225, 1241482.9365225), ï..age_bands = c("30 TO 34", 
    "16 TO 24", "65 TO 69", "45 TO 49", "75 TO 79", "30 TO 34", 
    "65 TO 69", "35 TO 39"), ethnicity = c("", "", "", "white", 
    "white", "white", "white", ""), gender = c("Female", "Female", 
    "Female", "Female", "Male", "Male", "Male", "Female"), project_id = c("RTB-GLD", 
    "RTB-GLD", "RTB-GLD", "INTERVAL", "RTB-IBD", "RTB-IBD", "INTERVAL", 
    "RTB-GLD"), geometry = structure(list(structure(list(list(
        structure(c(234998.8202, 234199.2005, 231675.7996, 226969.1972, 
        228841.9968, 226421.5025, 221174.0748, 219693.78, 220379.35, 
        219444.56, 214460.5, 212656.93, 209007, 205248, 204039, 
        202073.6, 192911.3, 189055.445, 185723.3, 184224.4, 175678.578, 
        175640.08, 172566.344, 169790.439, 168442.779, 163047.156, 
        158149.197, 154807.334, 150551.418, 147582.929, 143206.63, 
        140842.76, 137285.3, 135221.85, 135876.9, 134157.3994, 
        136594.2, 145706.03, 148210.311, 152517.724, 153594.542, 
        161765.89, 165363.8, 166815.81, 165929.6035, 169446.09, 
        171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
        179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 
        193211.19, 195934.7, 201605.8, 201741.19, 203898.05, 
        207591.75, 212215.7, 216120.09, 219355.15, 225603.6, 
        226774.45, 235955.42, 243532.247, 242824.77, 243911.78, 
        243028.6, 241872.3, 241450.8, 242606.17, 242283.75, 242585.29, 
        243305.19, 243570.27, 243752.23, 243628.5275, 236483.503, 
        236862.2976, 234998.8202, 85587.6273, 90329.4028, 98084.0031, 
        101116.0951, 110157.1971, 117556.2992, 117412.7424, 114860.61, 
        106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 89188, 
        83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
        59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 
        43544.349, 41887.089, 38259.782, 41399.896, 40983.218, 
        38502.49, 36067.55, 35259.5, 31695.19, 26719, 25091.146, 
        21508.7, 23826.2, 31062.812, 30547.462, 29159.056, 26447.31, 
        22515.3, 18843.11, 16533.7005, 11553.24, 11722.33, 16326.86, 
        16176.5, 21532.0955, 25186.62, 29758.8, 33564.69, 30937.38, 
        33634.05, 37739.51, 38942.78, 41252.69, 40955.9, 46485.13, 
        51499.1, 52498.96, 50935.2, 51230, 50188.29, 52316.6, 
        54063.23, 53817.89, 50574.037, 57101.11, 61854.2, 64425.91, 
        64075.22, 65591.63, 66286.69, 67931.3, 68882.1, 68711.36, 
        68553.3, 68240.53, 70929.8645, 78673.7992, 82399.9026, 
        85587.6273), .Dim = c(84L, 2L)))), class = c("XY", "MULTIPOLYGON", 
    "sfg")), structure(list(list(structure(c(234998.8202, 234199.2005, 
    231675.7996, 226969.1972, 228841.9968, 226421.5025, 221174.0748, 
    219693.78, 220379.35, 219444.56, 214460.5, 212656.93, 209007, 
    205248, 204039, 202073.6, 192911.3, 189055.445, 185723.3, 
    184224.4, 175678.578, 175640.08, 172566.344, 169790.439, 
    168442.779, 163047.156, 158149.197, 154807.334, 150551.418, 
    147582.929, 143206.63, 140842.76, 137285.3, 135221.85, 135876.9, 
    134157.3994, 136594.2, 145706.03, 148210.311, 152517.724, 
    153594.542, 161765.89, 165363.8, 166815.81, 165929.6035, 
    169446.09, 171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
    179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
    195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 212215.7, 
    216120.09, 219355.15, 225603.6, 226774.45, 235955.42, 243532.247, 
    242824.77, 243911.78, 243028.6, 241872.3, 241450.8, 242606.17, 
    242283.75, 242585.29, 243305.19, 243570.27, 243752.23, 243628.5275, 
    236483.503, 236862.2976, 234998.8202, 85587.6273, 90329.4028, 
    98084.0031, 101116.0951, 110157.1971, 117556.2992, 117412.7424, 
    114860.61, 106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 
    89188, 83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
    59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 43544.349, 
    41887.089, 38259.782, 41399.896, 40983.218, 38502.49, 36067.55, 
    35259.5, 31695.19, 26719, 25091.146, 21508.7, 23826.2, 31062.812, 
    30547.462, 29159.056, 26447.31, 22515.3, 18843.11, 16533.7005, 
    11553.24, 11722.33, 16326.86, 16176.5, 21532.0955, 25186.62, 
    29758.8, 33564.69, 30937.38, 33634.05, 37739.51, 38942.78, 
    41252.69, 40955.9, 46485.13, 51499.1, 52498.96, 50935.2, 
    51230, 50188.29, 52316.6, 54063.23, 53817.89, 50574.037, 
    57101.11, 61854.2, 64425.91, 64075.22, 65591.63, 66286.69, 
    67931.3, 68882.1, 68711.36, 68553.3, 68240.53, 70929.8645, 
    78673.7992, 82399.9026, 85587.6273), .Dim = c(84L, 2L)))), class = c("XY", 
    "MULTIPOLYGON", "sfg")), structure(list(list(structure(c(234998.8202, 
    234199.2005, 231675.7996, 226969.1972, 228841.9968, 226421.5025, 
    221174.0748, 219693.78, 220379.35, 219444.56, 214460.5, 212656.93, 
    209007, 205248, 204039, 202073.6, 192911.3, 189055.445, 185723.3, 
    184224.4, 175678.578, 175640.08, 172566.344, 169790.439, 
    168442.779, 163047.156, 158149.197, 154807.334, 150551.418, 
    147582.929, 143206.63, 140842.76, 137285.3, 135221.85, 135876.9, 
    134157.3994, 136594.2, 145706.03, 148210.311, 152517.724, 
    153594.542, 161765.89, 165363.8, 166815.81, 165929.6035, 
    169446.09, 171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
    179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
    195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 212215.7, 
    216120.09, 219355.15, 225603.6, 226774.45, 235955.42, 243532.247, 
    242824.77, 243911.78, 243028.6, 241872.3, 241450.8, 242606.17, 
    242283.75, 242585.29, 243305.19, 243570.27, 243752.23, 243628.5275, 
    236483.503, 236862.2976, 234998.8202, 85587.6273, 90329.4028, 
    98084.0031, 101116.0951, 110157.1971, 117556.2992, 117412.7424, 
    114860.61, 106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 
    89188, 83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
    59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 43544.349, 
    41887.089, 38259.782, 41399.896, 40983.218, 38502.49, 36067.55, 
    35259.5, 31695.19, 26719, 25091.146, 21508.7, 23826.2, 31062.812, 
    30547.462, 29159.056, 26447.31, 22515.3, 18843.11, 16533.7005, 
    11553.24, 11722.33, 16326.86, 16176.5, 21532.0955, 25186.62, 
    29758.8, 33564.69, 30937.38, 33634.05, 37739.51, 38942.78, 
    41252.69, 40955.9, 46485.13, 51499.1, 52498.96, 50935.2, 
    51230, 50188.29, 52316.6, 54063.23, 53817.89, 50574.037, 
    57101.11, 61854.2, 64425.91, 64075.22, 65591.63, 66286.69, 
    67931.3, 68882.1, 68711.36, 68553.3, 68240.53, 70929.8645, 
    78673.7992, 82399.9026, 85587.6273), .Dim = c(84L, 2L)))), class = c("XY", 
    "MULTIPOLYGON", "sfg")), structure(list(list(structure(c(234998.8202, 
    234199.2005, 231675.7996, 226969.1972, 228841.9968, 226421.5025, 
    221174.0748, 219693.78, 220379.35, 219444.56, 214460.5, 212656.93, 
    209007, 205248, 204039, 202073.6, 192911.3, 189055.445, 185723.3, 
    184224.4, 175678.578, 175640.08, 172566.344, 169790.439, 
    168442.779, 163047.156, 158149.197, 154807.334, 150551.418, 
    147582.929, 143206.63, 140842.76, 137285.3, 135221.85, 135876.9, 
    134157.3994, 136594.2, 145706.03, 148210.311, 152517.724, 
    153594.542, 161765.89, 165363.8, 166815.81, 165929.6035, 
    169446.09, 171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
    179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
    195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 212215.7, 
    216120.09, 219355.15, 225603.6, 226774.45, 235955.42, 243532.247, 
    242824.77, 243911.78, 243028.6, 241872.3, 241450.8, 242606.17, 
    242283.75, 242585.29, 243305.19, 243570.27, 243752.23, 243628.5275, 
    236483.503, 236862.2976, 234998.8202, 85587.6273, 90329.4028, 
    98084.0031, 101116.0951, 110157.1971, 117556.2992, 117412.7424, 
    114860.61, 106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 
    89188, 83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
    59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 43544.349, 
    41887.089, 38259.782, 41399.896, 40983.218, 38502.49, 36067.55, 
    35259.5, 31695.19, 26719, 25091.146, 21508.7, 23826.2, 31062.812, 
    30547.462, 29159.056, 26447.31, 22515.3, 18843.11, 16533.7005, 
    11553.24, 11722.33, 16326.86, 16176.5, 21532.0955, 25186.62, 
    29758.8, 33564.69, 30937.38, 33634.05, 37739.51, 38942.78, 
    41252.69, 40955.9, 46485.13, 51499.1, 52498.96, 50935.2, 
    51230, 50188.29, 52316.6, 54063.23, 53817.89, 50574.037, 
    57101.11, 61854.2, 64425.91, 64075.22, 65591.63, 66286.69, 
    67931.3, 68882.1, 68711.36, 68553.3, 68240.53, 70929.8645, 
    78673.7992, 82399.9026, 85587.6273), .Dim = c(84L, 2L)))), class = c("XY", 
    "MULTIPOLYGON", "sfg")), structure(list(list(structure(c(234998.8202, 
    234199.2005, 231675.7996, 226969.1972, 228841.9968, 226421.5025, 
    221174.0748, 219693.78, 220379.35, 219444.56, 214460.5, 212656.93, 
    209007, 205248, 204039, 202073.6, 192911.3, 189055.445, 185723.3, 
    184224.4, 175678.578, 175640.08, 172566.344, 169790.439, 
    168442.779, 163047.156, 158149.197, 154807.334, 150551.418, 
    147582.929, 143206.63, 140842.76, 137285.3, 135221.85, 135876.9, 
    134157.3994, 136594.2, 145706.03, 148210.311, 152517.724, 
    153594.542, 161765.89, 165363.8, 166815.81, 165929.6035, 
    169446.09, 171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
    179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
    195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 212215.7, 
    216120.09, 219355.15, 225603.6, 226774.45, 235955.42, 243532.247, 
    242824.77, 243911.78, 243028.6, 241872.3, 241450.8, 242606.17, 
    242283.75, 242585.29, 243305.19, 243570.27, 243752.23, 243628.5275, 
    236483.503, 236862.2976, 234998.8202, 85587.6273, 90329.4028, 
    98084.0031, 101116.0951, 110157.1971, 117556.2992, 117412.7424, 
    114860.61, 106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 
    89188, 83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
    59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 43544.349, 
    41887.089, 38259.782, 41399.896, 40983.218, 38502.49, 36067.55, 
    35259.5, 31695.19, 26719, 25091.146, 21508.7, 23826.2, 31062.812, 
    30547.462, 29159.056, 26447.31, 22515.3, 18843.11, 16533.7005, 
    11553.24, 11722.33, 16326.86, 16176.5, 21532.0955, 25186.62, 
    29758.8, 33564.69, 30937.38, 33634.05, 37739.51, 38942.78, 
    41252.69, 40955.9, 46485.13, 51499.1, 52498.96, 50935.2, 
    51230, 50188.29, 52316.6, 54063.23, 53817.89, 50574.037, 
    57101.11, 61854.2, 64425.91, 64075.22, 65591.63, 66286.69, 
    67931.3, 68882.1, 68711.36, 68553.3, 68240.53, 70929.8645, 
    78673.7992, 82399.9026, 85587.6273), .Dim = c(84L, 2L)))), class = c("XY", 
    "MULTIPOLYGON", "sfg")), structure(list(list(structure(c(234998.8202, 
    234199.2005, 231675.7996, 226969.1972, 228841.9968, 226421.5025, 
    221174.0748, 219693.78, 220379.35, 219444.56, 214460.5, 212656.93, 
    209007, 205248, 204039, 202073.6, 192911.3, 189055.445, 185723.3, 
    184224.4, 175678.578, 175640.08, 172566.344, 169790.439, 
    168442.779, 163047.156, 158149.197, 154807.334, 150551.418, 
    147582.929, 143206.63, 140842.76, 137285.3, 135221.85, 135876.9, 
    134157.3994, 136594.2, 145706.03, 148210.311, 152517.724, 
    153594.542, 161765.89, 165363.8, 166815.81, 165929.6035, 
    169446.09, 171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
    179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
    195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 212215.7, 
    216120.09, 219355.15, 225603.6, 226774.45, 235955.42, 243532.247, 
    242824.77, 243911.78, 243028.6, 241872.3, 241450.8, 242606.17, 
    242283.75, 242585.29, 243305.19, 243570.27, 243752.23, 243628.5275, 
    236483.503, 236862.2976, 234998.8202, 85587.6273, 90329.4028, 
    98084.0031, 101116.0951, 110157.1971, 117556.2992, 117412.7424, 
    114860.61, 106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 
    89188, 83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
    59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 43544.349, 
    41887.089, 38259.782, 41399.896, 40983.218, 38502.49, 36067.55, 
    35259.5, 31695.19, 26719, 25091.146, 21508.7, 23826.2, 31062.812, 
    30547.462, 29159.056, 26447.31, 22515.3, 18843.11, 16533.7005, 
    11553.24, 11722.33, 16326.86, 16176.5, 21532.0955, 25186.62, 
    29758.8, 33564.69, 30937.38, 33634.05, 37739.51, 38942.78, 
    41252.69, 40955.9, 46485.13, 51499.1, 52498.96, 50935.2, 
    51230, 50188.29, 52316.6, 54063.23, 53817.89, 50574.037, 
    57101.11, 61854.2, 64425.91, 64075.22, 65591.63, 66286.69, 
    67931.3, 68882.1, 68711.36, 68553.3, 68240.53, 70929.8645, 
    78673.7992, 82399.9026, 85587.6273), .Dim = c(84L, 2L)))), class = c("XY", 
    "MULTIPOLYGON", "sfg")), structure(list(list(structure(c(234998.8202, 
    234199.2005, 231675.7996, 226969.1972, 228841.9968, 226421.5025, 
    221174.0748, 219693.78, 220379.35, 219444.56, 214460.5, 212656.93, 
    209007, 205248, 204039, 202073.6, 192911.3, 189055.445, 185723.3, 
    184224.4, 175678.578, 175640.08, 172566.344, 169790.439, 
    168442.779, 163047.156, 158149.197, 154807.334, 150551.418, 
    147582.929, 143206.63, 140842.76, 137285.3, 135221.85, 135876.9, 
    134157.3994, 136594.2, 145706.03, 148210.311, 152517.724, 
    153594.542, 161765.89, 165363.8, 166815.81, 165929.6035, 
    169446.09, 171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
    179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
    195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 212215.7, 
    216120.09, 219355.15, 225603.6, 226774.45, 235955.42, 243532.247, 
    242824.77, 243911.78, 243028.6, 241872.3, 241450.8, 242606.17, 
    242283.75, 242585.29, 243305.19, 243570.27, 243752.23, 243628.5275, 
    236483.503, 236862.2976, 234998.8202, 85587.6273, 90329.4028, 
    98084.0031, 101116.0951, 110157.1971, 117556.2992, 117412.7424, 
    114860.61, 106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 
    89188, 83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
    59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 43544.349, 
    41887.089, 38259.782, 41399.896, 40983.218, 38502.49, 36067.55, 
    35259.5, 31695.19, 26719, 25091.146, 21508.7, 23826.2, 31062.812, 
    30547.462, 29159.056, 26447.31, 22515.3, 18843.11, 16533.7005, 
    11553.24, 11722.33, 16326.86, 16176.5, 21532.0955, 25186.62, 
    29758.8, 33564.69, 30937.38, 33634.05, 37739.51, 38942.78, 
    41252.69, 40955.9, 46485.13, 51499.1, 52498.96, 50935.2, 
    51230, 50188.29, 52316.6, 54063.23, 53817.89, 50574.037, 
    57101.11, 61854.2, 64425.91, 64075.22, 65591.63, 66286.69, 
    67931.3, 68882.1, 68711.36, 68553.3, 68240.53, 70929.8645, 
    78673.7992, 82399.9026, 85587.6273), .Dim = c(84L, 2L)))), class = c("XY", 
    "MULTIPOLYGON", "sfg")), structure(list(list(structure(c(234998.8202, 
    234199.2005, 231675.7996, 226969.1972, 228841.9968, 226421.5025, 
    221174.0748, 219693.78, 220379.35, 219444.56, 214460.5, 212656.93, 
    209007, 205248, 204039, 202073.6, 192911.3, 189055.445, 185723.3, 
    184224.4, 175678.578, 175640.08, 172566.344, 169790.439, 
    168442.779, 163047.156, 158149.197, 154807.334, 150551.418, 
    147582.929, 143206.63, 140842.76, 137285.3, 135221.85, 135876.9, 
    134157.3994, 136594.2, 145706.03, 148210.311, 152517.724, 
    153594.542, 161765.89, 165363.8, 166815.81, 165929.6035, 
    169446.09, 171313.46, 173195.3, 177869.8, 181183.8524, 178400.24, 
    179260.2, 181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
    195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 212215.7, 
    216120.09, 219355.15, 225603.6, 226774.45, 235955.42, 243532.247, 
    242824.77, 243911.78, 243028.6, 241872.3, 241450.8, 242606.17, 
    242283.75, 242585.29, 243305.19, 243570.27, 243752.23, 243628.5275, 
    236483.503, 236862.2976, 234998.8202, 85587.6273, 90329.4028, 
    98084.0031, 101116.0951, 110157.1971, 117556.2992, 117412.7424, 
    114860.61, 106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 
    89188, 83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
    59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 43544.349, 
    41887.089, 38259.782, 41399.896, 40983.218, 38502.49, 36067.55, 
    35259.5, 31695.19, 26719, 25091.146, 21508.7, 23826.2, 31062.812, 
    30547.462, 29159.056, 26447.31, 22515.3, 18843.11, 16533.7005, 
    11553.24, 11722.33, 16326.86, 16176.5, 21532.0955, 25186.62, 
    29758.8, 33564.69, 30937.38, 33634.05, 37739.51, 38942.78, 
    41252.69, 40955.9, 46485.13, 51499.1, 52498.96, 50935.2, 
    51230, 50188.29, 52316.6, 54063.23, 53817.89, 50574.037, 
    57101.11, 61854.2, 64425.91, 64075.22, 65591.63, 66286.69, 
    67931.3, 68882.1, 68711.36, 68553.3, 68240.53, 70929.8645, 
    78673.7992, 82399.9026, 85587.6273), .Dim = c(84L, 2L)))), class = c("XY", 
    "MULTIPOLYGON", "sfg"))), class = c("sfc_MULTIPOLYGON", "sfc"
    ), precision = 0, bbox = structure(c(xmin = 134157.3994, 
    ymin = 11553.24, xmax = 243911.78, ymax = 117556.2992), class = "bbox"), crs = structure(list(
        input = "OSGB 1936 / British National Grid", wkt = "PROJCRS[\"OSGB 1936 / British National Grid\",\n    BASEGEOGCRS[\"OSGB 1936\",\n        DATUM[\"OSGB 1936\",\n            ELLIPSOID[\"Airy 1830\",6377563.396,299.3249646,\n                LENGTHUNIT[\"metre\",1]]],\n        PRIMEM[\"Greenwich\",0,\n            ANGLEUNIT[\"degree\",0.0174532925199433]],\n        ID[\"EPSG\",4277]],\n    CONVERSION[\"British National Grid\",\n        METHOD[\"Transverse Mercator\",\n            ID[\"EPSG\",9807]],\n        PARAMETER[\"Latitude of natural origin\",49,\n            ANGLEUNIT[\"degree\",0.0174532925199433],\n            ID[\"EPSG\",8801]],\n        PARAMETER[\"Longitude of natural origin\",-2,\n            ANGLEUNIT[\"degree\",0.0174532925199433],\n            ID[\"EPSG\",8802]],\n        PARAMETER[\"Scale factor at natural origin\",0.9996012717,\n            SCALEUNIT[\"unity\",1],\n            ID[\"EPSG\",8805]],\n        PARAMETER[\"False easting\",400000,\n            LENGTHUNIT[\"metre\",1],\n            ID[\"EPSG\",8806]],\n        PARAMETER[\"False northing\",-100000,\n            LENGTHUNIT[\"metre\",1],\n            ID[\"EPSG\",8807]]],\n    CS[Cartesian,2],\n        AXIS[\"(E)\",east,\n            ORDER[1],\n            LENGTHUNIT[\"metre\",1]],\n        AXIS[\"(N)\",north,\n            ORDER[2],\n            LENGTHUNIT[\"metre\",1]],\n    USAGE[\n        SCOPE[\"Engineering survey, topographic mapping.\"],\n        AREA[\"United Kingdom (UK) - offshore to boundary of UKCS within 49°45'N to 61°N and 9°W to 2°E; onshore Great Britain (England, Wales and Scotland). Isle of Man onshore.\"],\n        BBOX[49.75,-9,61.01,2.01]],\n    ID[\"EPSG\",27700]]"), class = "crs"), n_empty = 0L)), sf_column = "geometry", agr = structure(c(lad19cd = NA_integer_, 
objectid = NA_integer_, lad19nm = NA_integer_, lad19nmw = NA_integer_, 
bng_e = NA_integer_, bng_n = NA_integer_, long = NA_integer_, 
lat = NA_integer_, st_areasha = NA_integer_, st_lengths = NA_integer_, 
ï..age_bands = NA_integer_, ethnicity = NA_integer_, gender = NA_integer_, 
project_id = NA_integer_), .Label = c("constant", "aggregate", 
"identity"), class = "factor"), row.names = c(NA, 8L), class = c("sf", 
"data.frame"))

View(test2)

This is the code I have tried already to clean this dataset. However, when pivoting the names, they seem to attach to the geometry values and I do not know why this is so? Can anybody help me figure this out. Or is there a more efficient way in cleaning this dataset so I can plot it on a map.

p.s. I had only taken subset of one location out for speed and visibility purposes. The full dataset contains many other locations.


#moving column names into a single row called variables
test2 <- unite(test, col='Variables', c('ï..age_bands', 'ethnicity', 'gender', 'project_id'), sep = ',')

#splitting them so they're individual values, and not joined as one word
test3 <- test2 %>%
  mutate(Variables = strsplit(as.character(Variables), ",")) %>% 
  unnest(Variables)

#creating a counts column of each one
test4 <- test3 %>%
  group_by(Variables) %>% 
  mutate(count = n())

#removing duplicates so you only have 1 variable and its corresponding count
test5 <- test4[!duplicated(test4[c("Variables","count")]),]

#moving positioning of the column, so id_cols can be used on next function
test6 <- test5 %>%
  select(Variables, count, everything())

#pivoting wider so counts show underneath each variable
test7 <- test6 %>% 
  pivot_wider(
    id_cols = 3:13,
    names_from = c(Variables),
    values_from = count
  )
1 Like
2

If I understand you well, you want to create an sf object with one row per location (I assume with key lad19cd) and you have trouble doing the pivot to get all the non-geometry data in one row because the geometry 'sticks'.

Is it an idea to separate your data into a geometry part (test2a) and a non-geometry part (test2b) as described below?
You can then do a pivot on test2b in the usual way and then join the result with test2a.

By the way: in your code the variable test is not defined

library(sf)
library(dplyr)
library(tidyr)
test2a <- test2 %>%
  group_by(lad19cd) %>%
  filter(row_number()==1) %>%
  select(lad19cd)

test2b <- test2
sf::st_geometry(test2b) <- NULL
1 Like
3

Thank you. this worked brilliantly!

4

As a follow up. The final results creates a data frame that isn't special feature (sf). Would you know how to convert it back to sf, so it can be rendered by tmap?

Example below:

data <- structure(list(lad19cd = c("E06000001", "E06000052"), objectid = c(1L, 
                                                                           49L), lad19nm = c("Hartlepool", "Cornwall"), bng_e = c(447160L, 
                                                                                                                                  212497L), bng_n = c(531474L, 64493L), long = c(-1.27018, -4.64254
                                                                                                                                  ), lat = c(54.67614, 50.450218), st_areasha = c(93712619.817559, 
                                                                                                                                                                                  3548934061.31533), st_lengths = c(71011.9339493, 1241482.9365225
                                                                                                                                                                                  ), `35 TO 39` = c(16L, 135L), white = c(181L, 848L), Male = c(104L, 
                                                                                                                                                                                                                                                492L), `RTB-IBD` = c(155L, 745L), `40 TO 44` = c(25L, 100L), 
                       `45 TO 49` = c(19L, 128L), `Ethnicity Not Stated` = c(54L, 
                                                                             597L), `RTB-GLD` = c(41L, 570L), `16 TO 24` = c(12L, 131L
                                                                             ), `50 TO 54` = c(24L, 138L), `55 TO 59` = c(17L, 143L), 
                       Female = c(132L, 962L), COMPARE = c(34L, 2L), `30 TO 34` = c(21L, 
                                                                                    142L), `>=80` = c(10L, 24L), `65 TO 69` = c(22L, 115L), `60 TO 64` = c(28L, 
                                                                                                                                                           115L), `not stated` = c(1L, 5L), INTERVAL = c(1L, 56L), `70 TO 74` = c(18L, 
                                                                                                                                                                                                                                  88L), `RTB-STR` = c(4L, 16L), `25 TO 29` = c(14L, 124L), 
                       `Gender Not Stated` = 2:3, `75 TO 79` = c(12L, 70L), `RTB-INC` = 1:2, 
                       RDC = c(2L, 8L), mixed = c(2L, 4L), `RTB-GEN` = c(NA, 58L
                       ), asian = c(NA, 3L), `<=15` = c(NA, 4L), geometry = structure(list(
                         structure(list(list(structure(c(448986.02, 447072.101, 
                                                         443444.7028, 442588.2966, 440052.7012, 448476.6765, 450118.9667, 
                                                         450132.9512, 454039.03, 451736.05, 452570.529, 448986.02, 
                                                         536729.68, 537135.9959, 533133.0017, 528536.0976, 527819.7026, 
                                                         525831.9601, 525889.424, 525915.536, 528341.61, 532034.85, 
                                                         533906.857, 536729.68), .Dim = c(12L, 2L)))), class = c("XY", 
                                                                                                                 "MULTIPOLYGON", "sfg")), structure(list(list(structure(c(234998.8202, 
                                                                                                                                                                          234199.2005, 231675.7996, 226969.1972, 228841.9968, 226421.5025, 
                                                                                                                                                                          221174.0748, 219693.78, 220379.35, 219444.56, 214460.5, 
                                                                                                                                                                          212656.93, 209007, 205248, 204039, 202073.6, 192911.3, 
                                                                                                                                                                          189055.445, 185723.3, 184224.4, 175678.578, 175640.08, 
                                                                                                                                                                          172566.344, 169790.439, 168442.779, 163047.156, 158149.197, 
                                                                                                                                                                          154807.334, 150551.418, 147582.929, 143206.63, 140842.76, 
                                                                                                                                                                          137285.3, 135221.85, 135876.9, 134157.3994, 136594.2, 
                                                                                                                                                                          145706.03, 148210.311, 152517.724, 153594.542, 161765.89, 
                                                                                                                                                                          165363.8, 166815.81, 165929.6035, 169446.09, 171313.46, 
                                                                                                                                                                          173195.3, 177869.8, 181183.8524, 178400.24, 179260.2, 
                                                                                                                                                                          181955.21, 185101.85, 187807.85, 189018.71, 193211.19, 
                                                                                                                                                                          195934.7, 201605.8, 201741.19, 203898.05, 207591.75, 
                                                                                                                                                                          212215.7, 216120.09, 219355.15, 225603.6, 226774.45, 
                                                                                                                                                                          235955.42, 243532.247, 242824.77, 243911.78, 243028.6, 
                                                                                                                                                                          241872.3, 241450.8, 242606.17, 242283.75, 242585.29, 
                                                                                                                                                                          243305.19, 243570.27, 243752.23, 243628.5275, 236483.503, 
                                                                                                                                                                          236862.2976, 234998.8202, 85587.6273, 90329.4028, 98084.0031, 
                                                                                                                                                                          101116.0951, 110157.1971, 117556.2992, 117412.7424, 114860.61, 
                                                                                                                                                                          106454.29, 101066.96, 97814.5, 94004.29, 90871.3, 89188, 
                                                                                                                                                                          83911.2, 81276.2, 80080.1, 75965.452, 75629.3, 65319.3, 
                                                                                                                                                                          59125.553, 54647.032, 51879.281, 51475.164, 47480.287, 
                                                                                                                                                                          43544.349, 41887.089, 38259.782, 41399.896, 40983.218, 
                                                                                                                                                                          38502.49, 36067.55, 35259.5, 31695.19, 26719, 25091.146, 
                                                                                                                                                                          21508.7, 23826.2, 31062.812, 30547.462, 29159.056, 26447.31, 
                                                                                                                                                                          22515.3, 18843.11, 16533.7005, 11553.24, 11722.33, 16326.86, 
                                                                                                                                                                          16176.5, 21532.0955, 25186.62, 29758.8, 33564.69, 30937.38, 
                                                                                                                                                                          33634.05, 37739.51, 38942.78, 41252.69, 40955.9, 46485.13, 
                                                                                                                                                                          51499.1, 52498.96, 50935.2, 51230, 50188.29, 52316.6, 
                                                                                                                                                                          54063.23, 53817.89, 50574.037, 57101.11, 61854.2, 64425.91, 
                                                                                                                                                                          64075.22, 65591.63, 66286.69, 67931.3, 68882.1, 68711.36, 
                                                                                                                                                                          68553.3, 68240.53, 70929.8645, 78673.7992, 82399.9026, 
                                                                                                                                                                          85587.6273), .Dim = c(84L, 2L)))), class = c("XY", "MULTIPOLYGON", 
                                                                                                                                                                                                                       "sfg"))), class = c("sfc_MULTIPOLYGON", "sfc"), precision = 0, bbox = structure(c(xmin = 134157.3994, 
                                                                                                                                                                                                                                                                                                         ymin = 11553.24, xmax = 454039.03, ymax = 537135.9959), class = "bbox"), crs = structure(list(
                                                                                                                                                                                                                                                                                                           input = "OSGB 1936 / British National Grid", wkt = "PROJCRS[\"OSGB 1936 / British National Grid\",\n    BASEGEOGCRS[\"OSGB 1936\",\n        DATUM[\"OSGB 1936\",\n            ELLIPSOID[\"Airy 1830\",6377563.396,299.3249646,\n                LENGTHUNIT[\"metre\",1]]],\n        PRIMEM[\"Greenwich\",0,\n            ANGLEUNIT[\"degree\",0.0174532925199433]],\n        ID[\"EPSG\",4277]],\n    CONVERSION[\"British National Grid\",\n        METHOD[\"Transverse Mercator\",\n            ID[\"EPSG\",9807]],\n        PARAMETER[\"Latitude of natural origin\",49,\n            ANGLEUNIT[\"degree\",0.0174532925199433],\n            ID[\"EPSG\",8801]],\n        PARAMETER[\"Longitude of natural origin\",-2,\n            ANGLEUNIT[\"degree\",0.0174532925199433],\n            ID[\"EPSG\",8802]],\n        PARAMETER[\"Scale factor at natural origin\",0.9996012717,\n            SCALEUNIT[\"unity\",1],\n            ID[\"EPSG\",8805]],\n        PARAMETER[\"False easting\",400000,\n            LENGTHUNIT[\"metre\",1],\n            ID[\"EPSG\",8806]],\n        PARAMETER[\"False northing\",-100000,\n            LENGTHUNIT[\"metre\",1],\n            ID[\"EPSG\",8807]]],\n    CS[Cartesian,2],\n        AXIS[\"(E)\",east,\n            ORDER[1],\n            LENGTHUNIT[\"metre\",1]],\n        AXIS[\"(N)\",north,\n            ORDER[2],\n            LENGTHUNIT[\"metre\",1]],\n    USAGE[\n        SCOPE[\"Engineering survey, topographic mapping.\"],\n        AREA[\"United Kingdom (UK) - offshore to boundary of UKCS within 49°45'N to 61°N and 9°W to 2°E; onshore Great Britain (England, Wales and Scotland). Isle of Man onshore.\"],\n        BBOX[49.75,-9,61.01,2.01]],\n    ID[\"EPSG\",27700]]"), class = "crs"), n_empty = 0L)), row.names = 1:2, class = "data.frame")
5

I do not know what went wrong but in the following(simplified) example it works without losing the sf class :

test2a <- test2 %>%
  group_by(lad19cd) %>%
  filter(row_number()==1) %>%
  select(lad19cd) %>%
  ungroup() # added since last post !
class(test2a)

test2b <- test2
sf::st_geometry(test2b) <- NULL
test2b <- test2b %>%
  filter(row_number()==1) %>%
  mutate(dummydata= 2021) %>%
  select(lad19cd,dummydata)
class(test2b)

test3 <- test2a %>%
  left_join(test2b,by=c(lad19cd="lad19cd"))
class(test3)

The sf package has the sf::merge function and in the example the sf object is the first argument.
Maybe the sf object should be the first argument (???).

To 'convert' to an sf object you could try

data1 <- sf::st_sf(data,sf_column_name = 'geometry')
class(data1)
1 Like
6

Thanks, another solution :slightly_smiling_face:

7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.