Arya Arya - 4 months ago 32
R Question

R Filling empty data frame

I have a data frame that looks the following way.

Q C N
2 234 white
3 888 white
4 543 white
5 234 white
1 098 Blue
3 126 Blue
5 366 Black
1 222 pink
2 897 pink


i want the data frame to look like this.

Q C N
1 555 white
2 234 white
3 888 white
4 543 white
5 234 white
1 098 Blue
2 0 Blue
3 126 Blue
4 0 Blue
5 0 Blue
1 0 Black
2 0 Black
3 0 Black
4 0 Black
5 366 Black
1 222 pink
2 897 pink
3 0 pink
4 0 pink
5 0 pink


I want it to appear like this.
The idea is that Q always has to be from 1-5 if it doesn't appear i want the data frame to have a line were the quality is the one that is missing with its name and fill 0 for the C.
Thanks

Answer
library(tidyr)
complete(df, Q, N, fill = list(C = 0))

Gets what you want (assuming your data is called df). It reorders the columns and rows, but you can sort that out if necessary.


Using this data as input:

df <- structure(list(Q = c(2L, 3L, 4L, 5L, 1L, 3L, 5L, 1L, 2L), C = c(234L, 
888L, 543L, 234L, 98L, 126L, 366L, 222L, 897L), N = structure(c(4L, 
4L, 4L, 4L, 2L, 2L, 1L, 3L, 3L), .Label = c("Black", "Blue", 
"pink", "white"), class = "factor")), .Names = c("Q", "C", "N"
), class = "data.frame", row.names = c(NA, -9L))

df
#   Q   C     N
# 1 2 234 white
# 2 3 888 white
# 3 4 543 white
# 4 5 234 white
# 5 1  98  Blue
# 6 3 126  Blue
# 7 5 366 Black
# 8 1 222  pink
# 9 2 897  pink

complete(df, Q, N, fill = list(C = 0))
# # A tibble: 20 x 3
#        Q      N     C
#    <int> <fctr> <dbl>
# 1      1  Black     0
# 2      1   Blue    98
# 3      1   pink   222
# 4      1  white     0
# 5      2  Black     0
# 6      2   Blue     0
# 7      2   pink   897
# 8      2  white   234
# 9      3  Black     0
# 10     3   Blue   126
# 11     3   pink     0
# 12     3  white   888
# 13     4  Black     0
# 14     4   Blue     0
# 15     4   pink     0
# 16     4  white   543
# 17     5  Black   366
# 18     5   Blue     0
# 19     5   pink     0
# 20     5  white   234