-4

I have a data frame with a column week and another year (87 weeks). I need to create a new column (weekseq) with a number that identify the week sequentially from first to last. I dont know how to do. Someone can help me?

Example:

id week month year yearweek weekseq

1 1 1 2014 2014/1
1 1 1 2013 2013/1
1 2 1 2014 2014/2
1 2 1 2013 2013/2
1 3 1 2014 2014/3
1 3 1 2013 2013/3
1 4 1 2014 2014/4
1 4 1 2013 2013/4
1 5 1 2014 2014/5
1 5 1 2013 2013/5
1 6 2 2014 2014/6
1 6 2 2013 2013/6
1 7 2 2014 2014/7
1 7 2 2013 2013/7
1 8 2 2014 2014/8
1 8 2 2013 2013/8
1 9 2 2014 2014/9
1 9 2 2013 2013/9
1 10 3 2014 2014/10 1 10 3 2013 2013/10 1 11 3 2014 2014/11 1 11 3 2013 2013/11 1 12 3 2014 2014/12 1 12 3 2013 2013/12

Danielle Andreza
  • 1
  • 1
  • 2
    It would be better if you show few lines of your dataset in your post – akrun Jan 16 '15 at 19:12
  • 5
    Welcome to SO, please read [How to make a great R reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and also the SO guidelines for asking a new question – Rich Scriven Jan 16 '15 at 19:12
  • Without an example, it is difficult to comment. Perhaps, you can try `library(data.table); setDT(df)[, seqCol:= as.numeric(factor(week)), year][]` or `setDT(df)[, seqCol:= rleid(week), year][]` – akrun Jan 16 '15 at 19:16
  • Could you show the expected result as well? – akrun Jan 16 '15 at 19:32
  • If `ID` is the expected output column `setDT(df)[,ID:= 1:.N , list(week, year)][]` – akrun Jan 16 '15 at 19:41
  • Might be good to note that `rleid()` is only in the 1.9.5 dev version of data.table – Rich Scriven Jan 16 '15 at 19:42
  • @akrun I hope the variable weekyear like this week year weekseq 49 2012 1 50 2012 2 51 2012 3 52 2012 4 1 2013 5 2 2013 6 3 2013 7 – Danielle Andreza Jan 16 '15 at 19:43
  • @RIchardScriven Thanks, but I guess the OP's expected output is not clear. – akrun Jan 16 '15 at 19:44
  • @DanielleAndreza The new data on the comment looks different than the posted one and the sequence you showed is not grouping on `year`. So simply, `df$weekseq <- 1:nrow(df)` – akrun Jan 16 '15 at 19:46
  • @DanielleAndreza What is the expected `weekseq` based on the new data? – akrun Jan 17 '15 at 04:07

1 Answers1

0

This solution requires the 'dplyr' and 'plyr' packages: 

# Coerce into tbd_df
datatbl <- tbl_df(data)

# Arrange, giving more weight to year than week
datatbl <- arrange(datatbl, year, month, week)

# Create a new column that numbers the arranged rows sequentially
seqtbl <- ddply(datatbl, .(id), transform, sequence=seq_along(id))
Christopher Hesh
  • 31
  • 4
  • @Chistopher Hersh, The problem is that each week is repeated about 700 times, because that is the number of traps that are inspected weekly. When I use the weeks following code will 1-61051 (the total lines), and the right would be from 1 to 87. Do you have other suggestions? – Danielle Andreza Jan 16 '15 at 20:48
  • I've added the month variable to the `arrange()` function so that each `id` will be arranged most heavily by `year`, then `month`, then `week`. The last step was changed based on your description to only number sequentially within each `id`. Now it will split the data set up into individual `id` groupings after they've been arranged, then apply the `seq_along()` function before combining everything back into the tbl_df `seqtbl`. – Christopher Hesh Jan 17 '15 at 14:40