Why are the keys not spread across multiple columns?

Question

I am trying to spread the key-values of the 'Type' column across multiple columns. The dataframe (dat_weighted) is as follows:

  AreaName             Type index_disagg_standard index Category
1 Barking and Dagenham MRF  0.3329420             0.518 Average
2 Barking and Dagenham SDH  0.5286273             0.518 Average
3 Barking and Dagenham HO   0.7096024             0.518 Average
4 Barnet               MRF  0.8639524             0.750 Average
5 Barnet               SDH  0.3641302             0.750 Average
6 Barnet               HO   0.8493885             0.750 Average
7 Barnsley             MRF  0.5628280             0.610 Average
8 Barnsley             SDH  0.801927              0.610 Average
9 Barnsley             HO   0.4823344             0.610 Average

The code I have tried is as follows :

dat_index <- dat_weighted %>%
  distinct(AreaName, .keep_all = TRUE) %>%
  arrange(index) %>%
  spread(key = Type, value = index_disagg_standard)

But it only provides me with this:

  AreaName             MRF         index
1 Barking and Dagenham 0.33294203  0.518
2 Barnet               0.86395241  0.750
3 Barnsley             0.56282804  0.610

and I would be expecting something like this:

  AreaName             MRF         SDH       OH        index
1 Barking and Dagenham 0.33294203  0.5286273 0.7096024 0.518
2 Barnet               0.86395241  0.3641302 0.8493885 0.750    
3 Barnsley             0.56282804  0.801927  0.4823344 0.610

It's easier to help you if you share your data in a [reproducible format](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). Explicitly list any non-base R packages that you are using. — MrFlick, Mar 31 '19 at 23:33

score 0 · Answer 1 · answered Mar 31 '19 at 23:51

To get your expected output, we can do

library(dplyr)
library(tidyr)

df %>%
  select(-Category) %>%
  gather(key, value, -c(AreaName, Type, index)) %>%
  spread(Type, value) %>%
  select(-key)

#             AreaName index        HO       MRF       SDH
#1 Barking_and_Dagenham 0.518 0.7096024 0.3329420 0.5286273
#2               Barnet 0.750 0.8493885 0.8639524 0.3641302
#3             Barnsley 0.610 0.4823344 0.5628280 0.8019270

score 0 · Answer 2 · answered Apr 01 '19 at 03:01

We just need to remove the 'Category' column and it should work fine

library(tidyverse)
dat_weighted  %>% 
     select(-Category) %>% 
     spread(Type, index_disagg_standard)
#             AreaName index        HO       MRF       SDH
#1 Barking and Dagenham 0.518 0.7096024 0.3329420 0.5286273
#2               Barnet 0.750 0.8493885 0.8639524 0.3641302
#3             Barnsley 0.610 0.4823344 0.5628280 0.8019270

data

dat_weighted <- structure(list(AreaName = c("Barking and Dagenham", 
     "Barking and Dagenham", 
"Barking and Dagenham", "Barnet", "Barnet", "Barnet", "Barnsley", 
"Barnsley", "Barnsley"), Type = c("MRF", "SDH", "HO", "MRF", 
"SDH", "HO", "MRF", "SDH", "HO"), index_disagg_standard = c(0.332942, 
0.5286273, 0.7096024, 0.8639524, 0.3641302, 0.8493885, 0.562828, 
0.801927, 0.4823344), index = c(0.518, 0.518, 0.518, 0.75, 0.75, 
0.75, 0.61, 0.61, 0.61), Category = c("Average", "Average", "Average", 
"Average", "Average", "Average", "Average", "Average", "Average"
)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", 
"6", "7", "8", "9"))

Why are the keys not spread across multiple columns?

2 Answers2

data