0

I'm working with R studio. I'm having issues with how to divide this up I have:

ID COLOR NUMBER
321 RED 3
321 GREEN 10
321 BLUE 4
067 RED 50
067 BLUE 2
067 YELLOW 3
111 PURPLE 10
111 YELLOW 2

I want:

ID red green blue yellow PURPLE
321 3 10 4 0 0
067 50 0 2 3 0
111 0 0 0 2 10

I got the advice on here to use:

Data <- as.data.frame.matrix(table(cbind(file_name[1], unlist(file_name[-1]))))

and I got a good start, but I had to change my data around and I can't incorporate the extra number column I added.

neilfws
  • 32,751
  • 5
  • 50
  • 63
anni
  • 83
  • 5

1 Answers1

0

You can use tidyr::pivot_wider.

library(tidyr) # install.packages("tidyr") if required
df1 %>% 
  pivot_wider(names_from = COLOR, 
              values_from = NUMBER, 
              values_fill = 0)

Result:

# A tibble: 3 × 6
     ID   RED GREEN  BLUE YELLOW PURPLE
  <int> <int> <int> <int>  <int>  <int>
1   321     3    10     4      0      0
2    67    50     0     2      3      0
3   111     0     0     0      2     10

Data:

df1 <- structure(list(ID = c(321L, 321L, 321L, 67L, 67L, 67L, 111L, 111L), 
                      COLOR = c("RED", "GREEN", "BLUE", "RED", 
                                "BLUE","YELLOW", "PURPLE", "YELLOW"), 
                      NUMBER = c(3L, 10L, 4L, 50L, 2L, 3L, 10L, 2L)), 
                      class = "data.frame", 
                      row.names = c(NA, -8L))
neilfws
  • 32,751
  • 5
  • 50
  • 63
  • Im getting the following error: Error in `pivot_wider_spec()`: ! Can't convert `fill` to . – anni Oct 12 '22 at 04:47
  • Code works using the example data in your question. If that isn't accurate, please edit it. [It's better to use dput than tables](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) so we can see the structure of the data. – neilfws Oct 12 '22 at 04:50
  • I got it when I removed values_fill. I got NULL values but I can replace those later if they cause problems – anni Oct 12 '22 at 05:02