I have this data in long format
library(tidyverse)
n=3
set.seed(1)
d <- tibble(y=sample(0:1,n,T),
x1=sample(1:5,n,T),
x2=sample(1:5,n,T),
x3=sample(1:5,n,T))
d_long <- d |> pivot_longer(!y)
# add new row with new variable x4
d_long_new <- q |> bind_rows(tibble(y=1,name="x4",value=10))
.
d_long_new
# A tibble: 10 x 3
y name value
<dbl> <chr> <dbl>
1 0 x1 1
2 0 x2 3
3 0 x3 3
4 1 x1 2
5 1 x2 2
6 1 x3 1
7 0 x1 5
8 0 x2 3
9 0 x3 5
10 1 x4 10
How can I convert the data to wide format to get this result?
A tibble: 4 x 5
y x1 x2 x3 x4
<dbl> <int> <int> <int> <dbl>
1 0 1 3 3 NA
2 1 2 2 1 NA
3 0 5 3 5 NA
4 1 NA NA NA 10
When I try to apply the pivot_wider() function I get something other than what I expected
d_long_new |> pivot_wider(names_from = name, values_from = value)
.
# A tibble: 2 x 5
y x1 x2 x3 x4
<dbl> <list> <list> <list> <list>
1 0 <dbl [2]> <dbl [2]> <dbl [2]> <NULL>
2 1 <dbl [1]> <dbl [1]> <dbl [1]> <dbl [1]>
You need to add another column that identifies unique rows.
d_long_new |>
mutate(seq=row_number(), .by=name) |>
pivot_wider(names_from = name, values_from = value) # |> select(-seq)
# A tibble: 4 x 6
y seq x1 x2 x3 x4
<dbl> <int> <dbl> <dbl> <dbl> <dbl>
1 0 1 1 3 3 NA
2 1 2 2 2 1 NA
3 0 3 5 3 5 NA
4 1 1 NA NA NA 10