R - Expand.grid + merge lost factor levels order

Question

The following issue happens in a shiny application, and the data involved is the product of several subsets and modifications. So this is difficult to get a reproducible dataset to add to this request (i can't set a dput() in my shiny chunks). My request seems to be a logical issue, so i'll try first without adding data.

I have a DF0 with 3 columns: YEAR: factor, SP: factor, IA: numeric.

I subset it with levels of SP, called DF1, but loose some YEAR levels in the process.

My code find unused levels of YEAR within the subset using expand.grid, then merge empty missing rows to the subset. The purpose is to set "0" as value on IA on the missing years and have a complete time serie. I manage to get all the levels of YEAR by requesting in my original dataset, DF.

AGG<-reactive({
  DF2 <- with(DF1(), expand.grid(YEAR = levels(DF()$YEAR),SP = levels(DF0()$SP)))
  DF2 <- merge(DF1(), DF2,by=c("YEAR", "SP"), all = TRUE)
  DF2$IA[is.na(DF2$IA)] <- 0
  DF2<-data.table(DF2)
})

Basically i merge DF1 and DF2 which have the same structure:

DF1:

 $ YEAR: Factor w/ 6 levels "2006","2007",..: 1 2 3 4 5 6
 $ SP  : Factor w/ 1 level "Aglais io (Linnaeus, 1758)": 1 1 1 1 1 1
 $ IA  : num  82.3 78.8 79.3 135.4 81.8 ...

DF2:

$ YEAR: Factor w/ 14 levels "2006","2007",..: 1 2 3 4 5 6 7 8 9 10 ...
$ SP  : Factor w/ 1 level "Aglais io (Linnaeus, 1758)": 1 1 1 1 1 1 1 1 1 1  ...
 $ IA  : num  82.3 78.8 79.3 135.4 81.8 ...
 - attr(*, ".internal.selfref")=<externalptr>

The two datasets actually merge, but in the process my YEAR levels loose their order.

DF2

YEAR     SP  IA
2006     Aglais io (Linnaeus, 1758)  82.32
2007     Aglais io (Linnaeus, 1758)  78.79
2008     Aglais io (Linnaeus, 1758)  79.29
2009     Aglais io (Linnaeus, 1758)  135.35
2010     Aglais io (Linnaeus, 1758)  81.82
2014     Aglais io (Linnaeus, 1758)  51.26
2001     Aglais io (Linnaeus, 1758)  0.00
2002     Aglais io (Linnaeus, 1758)  0.00
2003     Aglais io (Linnaeus, 1758)  0.00
2004     Aglais io (Linnaeus, 1758)  0.00
2005     Aglais io (Linnaeus, 1758)  0.00
2011     Aglais io (Linnaeus, 1758)  0.00
2012     Aglais io (Linnaeus, 1758)  0.00
2013     Aglais io (Linnaeus, 1758)  0.00

levels(DF) (original)
2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014
levels(DF1) (subset)
2006 2007 2008 2009 2010 2014
levels(DF2) (recovered)
2006 2007 2008 2009 2010 2014 2001 2002 2003 2004 2005 2011 2012 2013

I tried to reorder DF2$YEAR using order() and sort() following multiple SO posts but none have an impact on this. YEAR levels are found automatically in the app so i can't manually set the order.

My final plot does'nt make any sense! (All the 0 years are recovered)

How am i supposed to recover (or never loose) my factor order ?

You may set the order of factor levels manually by DF2$YEAR <- factor(DF2$YEAR, levels = c("2001", "2002", ...)) or DF2$YEAR <- factor(DF2$YEAR, levels = sort(as.integer(as.character(DF2$YEAR)))). Hope this will help. — DrDom
Thanks a lot, your second solution worked! I'll keep this tip for further merges! — user2542995

DrDom DrDom · Accepted Answer · 2016-01-14T16:09:46

You may set the order of factor levels manually by

DF2$YEAR <- factor(DF2$YEAR, levels = c("2001", "2002", ...))

or

DF2$YEAR <- factor(DF2$YEAR, levels = sort(as.integer(as.character(DF2$YEAR))))

Such a complex call as.integer(as.character(DF2$YEAR)) is required if YEAR is a factor. If YEAR is an integer you may sort YEAR directly.

R - Expand.grid + merge lost factor levels order

1 Answers