title: expand.grid when one variable is really two columns
body: <p>I have a data set with districts, counties and years.  If a given district/county combination occurs in any year I want that combination to occur in every year.  Below are two ways I have figured out to do this.  The first approach uses a function to create combinations of district, county and year and only requires six lines of code.  The bottom approach uses a combination of <code>paste</code>, <code>expand.grid</code> and <code>strsplit</code> and is much more complex/convoluted.</p>
  
  <p>There are probably much more efficient methods than either above.  For example, is there a way to use <code>expand.grid</code> that might achieve the district/county/year combinations perhaps with only 1 or 2 lines of code?</p>
  
  <p>Thank you for any advice.  My function can do the job, but this question is a learning opportunity for me.  I prefer base R.</p>
  
  <p>Here is the example data set:</p>
  
  <pre><code>df.1 &lt;- read.table(text = '
      state    district    county   year   apples
         AA          EC        A    1980     100
         AA          EC        B    1980      10
         AA          EC        C    1980     150
         AA           C        G    1980     200
         AA           C    other    1980      20
         AA           C        I    1980     250
         AA          WC        R    1980     300
         AA          WC        S    1980      30
         AA          WC     other   1980     350
         AA          EC        A    1999    1100
         AA          EC        D    1999     110
         AA          EC        E    1999    1150
         AA           C        H    1999    1200
         AA           C        I    1999     120
         AA           C        J    1999    1250
         AA          WC        R    1999    1300
         AA          WC    other    1999     130
         AA          WC        T    1999    1350
  ', header=TRUE, stringsAsFactors = FALSE)
  </code></pre>
  
  <p>Here is the desired result:</p>
  
  <pre><code>desired.result &lt;- read.table(text = '
     state district county  year apples
        AA        C      G  1980    200
        AA        C      H  1980     NA
        AA        C      I  1980    250
        AA        C      J  1980     NA
        AA        C  other  1980     20
        AA       EC      A  1980    100
        AA       EC      B  1980     10
        AA       EC      C  1980    150
        AA       EC      D  1980     NA
        AA       EC      E  1980     NA
        AA       WC  other  1980    350
        AA       WC      R  1980    300
        AA       WC      S  1980     30
        AA       WC      T  1980     NA
        AA        C      G  1999     NA
        AA        C      H  1999   1200
        AA        C      I  1999    120
        AA        C      J  1999   1250
        AA        C  other  1999     NA
        AA       EC      A  1999   1100
        AA       EC      B  1999     NA
        AA       EC      C  1999     NA
        AA       EC      D  1999    110
        AA       EC      E  1999   1150
        AA       WC  other  1999    130
        AA       WC      R  1999   1300
        AA       WC      S  1999     NA
        AA       WC      T  1999   1350
  ', header=TRUE, stringsAsFactors = FALSE)
  </code></pre>
  
  <p>Here is my most straight-forward solution so far, which uses a function to represent every district/county combination for each year:</p>
  
  <pre><code>my.unique.function &lt;- function(year) {
       my.unique     &lt;- data.frame(unique(df.1[, c('state', 'district', 'county')]), year)
       return(my.unique = my.unique)
  }
  
  years &lt;- as.data.frame(unique(df.1[, 'year']))
  my.unique.output &lt;- apply(years, 1, function(x) {my.unique.function(x)})
  my.unique.output2 &lt;- do.call(rbind.data.frame, my.unique.output)
  
  desired.result2 &lt;- merge(df.1, my.unique.output2, by = c('state', 'year', 'district', 'county'), all=TRUE)
  
  # compare output with a priori desired result
  desired.result &lt;- desired.result[order(desired.result$state, desired.result$year, desired.result$district, desired.result$county),]
  all.equal(desired.result[,c(1,4,2,3,5)], desired.result2[,1:5])
  </code></pre>
  
  <p>Here is my initial, much more complex solution:</p>
  
  <pre><code># find unique combinations of district and county
  my.unique     &lt;- unique(df.1[, c('district', 'county')])
  
  # paste district and county together
  my.unique$x   &lt;- apply( my.unique[ , c('district', 'county') ] , 1 , paste , collapse = "-" )
  
  # represent each district/county combination for each year
  expand.unique      &lt;- expand.grid(my.unique$x, year = c(1980, 1999))
  expand.unique$Var1 &lt;- as.character(expand.unique$Var1)
  
  # split combined district/county combinations into two columns
  expand.unique$Var1b &lt;- sub('-', ' ', expand.unique$Var1)
  unique.split        &lt;- strsplit(expand.unique$Var1b, ' ')
  unique.splits       &lt;- matrix(unlist(unique.split), ncol=2, byrow=TRUE, dimnames = list(NULL, c("district", "county")))
  
  # create template prior to merging with original data set
  state &lt;- 'AA'
  desired.resultb &lt;- data.frame(state, expand.unique, unique.splits)
  
  # merge template with original data set so missing observations are present if a county is not included for a given year
  desired.resultc &lt;- merge(df.1, desired.resultb, by = c('state', 'year', 'district', 'county'), all=TRUE)
  desired.resultc
  
  # compare output with a priori desired result
  desired.result &lt;- desired.result[order(desired.result$state, desired.result$year, desired.result$district, desired.result$county),]
  all.equal(desired.result[,c(1,4,2,3,5)], desired.resultc[,1:5])
  </code></pre>