converting long to wide with columns starting at zero

Question

I have the following data

county<-c(a,a,a,b,b,c)
id<-c(1,2,3,4,5,6)
data<-data.frame(county,id)

I need to convert from long to wide and get the following output

county<-c(a,b,c)
id__0<-c(1,4,6)
id__1<-c(2,5,##NA##)
id__2<-3,##NA##,##NA##)
data2<-data.frame(county,id__0,id__1,id__2) 

My main problem is not in converting from long to wide, but how to make the columns start with id__0.

Answer 1

You could add an intermediate variable by grouping according to county and using mutate to build a sequence from 0 upwards for each county, then pivot_wider on that:

library(tidyr)
library(dplyr)

data %>% 
  group_by(county) %>% 
  mutate(id_count = seq(n()) - 1) %>%
  pivot_wider(county, names_from =id_count, values_from = id, names_prefix = "id_")
#> # A tibble: 3 x 4
#> # Groups:   county [3]
#>   county  id_0  id_1  id_2
#>   <chr>  <dbl> <dbl> <dbl>
#> 1 a          1     2     3
#> 2 b          4     5    NA
#> 3 c          6    NA    NA

^{Created on 2022-02-10 by the reprex package (v2.0.1)}