Complete dataframe with missing combinations of values

I have a data frame with two factors (distance) and years (years). I would like to complete all years values for every factor by 0.

i.e. from this:

    distance years area
1      NPR     3   10
2      NPR     4   20
3      NPR     7   30
4      100     1   40
5      100     5   50
6      100     6   60

get this:

   distance years area
1       NPR     1    0
2       NPR     2    0
3       NPR     3   10
4       NPR     4   20
5       NPR     5    0
6       NPR     6    0
7       NPR     7   30
8       100     1   40
9       100     2    0
10      100     3    0
11      100     4    0
12      100     5   50
13      100     6   60
14      100     7    0

I tried to apply expand function:

library(tidyr)
library(dplyr, warn.conflicts = FALSE)

expand(df, years = 1:7)

but this just produces one column data frame and does not expand the original one:

# A tibble: 7 x 1
  years
  <int>
1     1
2     2
3     3
4     4
5     5
6     6
7     7

or expand.grid does not working neither:

require(utils)    
expand.grid(df, years = 1:7)

Error in match.names(clabs, names(xi)) : 
  names do not match previous names
In addition: Warning message:
In format.data.frame(x, digits = digits, na.encode = FALSE) :
  corrupt data frame: columns will be truncated or padded with NAs

Is there a simple way to expand my data frame? And how to expand it based on two categories: distance and uniqueLoc?

distance <- rep(c("NPR", "100"), each = 3)
years <-c(3,4,7, 1,5,6)
area <-seq(10,60,10)
uniqueLoc<-rep(c("a", "b"), 3)

df<-data.frame(uniqueLoc, distance, years, area)

> df
  uniqueLoc distance years area
1         a      NPR     3   10
2         b      NPR     4   20
3         a      NPR     7   30
4         b      100     1   40
5         a      100     5   50
6         b      100     6   60

  • Possible duplicate stackoverflow.com/q/41613710/680068

    – 

Leave a Comment