How to separate pivot_wider results into rows

Question

I have this df

structure(list(uniqueID = c(1512211, 1512211, 1512211, 58211, 
58211, 58211, 58211), food_item = c("sp2", "sp3", "sp4", "sp10", 
"sp3", "sp4", "sp5"), points = c(10, 3, 20, 2, 3, 4, 3)), spec = structure(list(
    cols = list(uniqueID = structure(list(), class = c("collector_double", 
    "collector")), food_item = structure(list(), class = c("collector_character", 
    "collector")), points = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), delim = ";"), class = "col_spec"), row.names = c(NA, 
-7L), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"))

I'm trying to transform into a table like this, where each point is distributed in rows and the "SPs" individually mark the points, without overlap between each SP.

I did this code but didn't work

an_anj <- an_anj %>%
    filter(uniqueID == "1512211") %>% 
  pivot_wider(names_from = food_item,
              values_from = points, 
              values_fill = 0) %>% 
uncount(points)

Erro: Problem with mutate() column _weight. i _weight = points. x _weight must be a vector, not a function. i The error occurred in group 1: uniqueID = "anjos1512211". Run rlang::last_error() to see where the error occurred.

When I do

an_anj <- an_anj %>%
    filter(uniqueID == "1512211") %>% 
  pivot_wider(names_from = food_item,
              values_from = points, 
              values_fill = 0)

I don't get separate rows

user_3732271 · Answer

We may uncount to replicate the rows before doing the pivot_wider

library(dplyr)
library(data.table)
library(tidyr)
out <- an_anj %>%
    filter(uniqueID == "1512211") %>%
    mutate(new = 1) %>% 
    uncount(points) %>%
    mutate(rn = rowid(uniqueID)) %>%
    pivot_wider(names_from = food_item, values_from = new, 
          values_fill = 0) %>% 
   select(-rn)

-output

> as.data.frame(out)
   uniqueID sp2 sp3 sp4
1   1512211   1   0   0
2   1512211   1   0   0
3   1512211   1   0   0
4   1512211   1   0   0
5   1512211   1   0   0
6   1512211   1   0   0
7   1512211   1   0   0
8   1512211   1   0   0
9   1512211   1   0   0
10  1512211   1   0   0
11  1512211   0   1   0
12  1512211   0   1   0
13  1512211   0   1   0
14  1512211   0   0   1
15  1512211   0   0   1
16  1512211   0   0   1
17  1512211   0   0   1
18  1512211   0   0   1
19  1512211   0   0   1
20  1512211   0   0   1
21  1512211   0   0   1
22  1512211   0   0   1
23  1512211   0   0   1
24  1512211   0   0   1
25  1512211   0   0   1
26  1512211   0   0   1
27  1512211   0   0   1
28  1512211   0   0   1
29  1512211   0   0   1
30  1512211   0   0   1
31  1512211   0   0   1
32  1512211   0   0   1
33  1512211   0   0   1

How to separate pivot_wider results into rows

1 Answers