Add line numbers per group of variable values in R dataframe

Question

I have an R dataframe like this:

blogger; date; word
joe; 2016-11-19; ears
paul; 2017-04-02; dilapidated
joe; 2016-11-21; night
joe; 2016-11-20; girl
paul; 2017-04-01; crumpled
paul; 2017-04-03; bellow

I want to sort it first by blogger and then by date. Then, I want to add a column with line numbers per blogger.

I have this:

# make linenumbers
df <- df %>%
    arrange(blogger,date) %>%
    mutate(linenumber = row_number())

But this, obviously, does not start on linenumber 1 for each blogger. The output I get is:

blogger; date; word; linenumber
joe; 2016-11-19; ears; 1
joe; 2016-11-20; girl; 2
joe; 2016-11-21; night; 3
paul; 2017-04-01; crumpled; 4
paul; 2017-04-02; dilapidated; 5
paul; 2017-04-03; bellow; 6

But the output I want is:

blogger; date; word; linenumber
joe; 2016-11-19; ears; 1
joe; 2016-11-20; girl; 2
joe; 2016-11-21; night; 3
paul; 2017-04-01; crumpled; 1
paul; 2017-04-02; dilapidated; 2
paul; 2017-04-03; bellow; 3

score 2 · Answer 1 · answered Apr 18 '17 at 13:03

If sorting the rows isn't needed you can do:

d$linenumber <- ave(as.integer(d$blogger), d$blogger, FUN=seq_along)
# > d
#   blogger        date         word linenumber
# 1     joe  2016-11-19         ears          1
# 2    paul  2017-04-02  dilapidated          1
# 3     joe  2016-11-21        night          2
# 4     joe  2016-11-20         girl          3
# 5    paul  2017-04-01     crumpled          2
# 6    paul  2017-04-03       bellow          3

data:

d <- read.table(header=TRUE, sep=';', text=
"blogger; date; word
joe; 2016-11-19; ears
paul; 2017-04-02; dilapidated
joe; 2016-11-21; night
joe; 2016-11-20; girl
paul; 2017-04-01; crumpled
paul; 2017-04-03; bellow")

score 1 · Accepted Answer · answered Apr 18 '17 at 12:38

1

Try a group_by on blogger?

df %>% arrange(blogger, date) %>% 
group_by(blogger) %>% 
mutate(linenumber = row_number()) %>%
ungroup()

answered Apr 18 '17 at 12:38

neilfws

32,751
5
50
63

score 1 · Answer 3 · answered Apr 18 '17 at 13:10

We can do this with convenient function (rowid) from data.table

library(data.table)
setDT(d)[, linenumber := rowid(blogger)]
d   
#   blogger        date         word linenumber
#1:     joe  2016-11-19         ears          1
#2:    paul  2017-04-02  dilapidated          1
#3:     joe  2016-11-21        night          2
#4:     joe  2016-11-20         girl          3
#5:    paul  2017-04-01     crumpled          2
#6:    paul  2017-04-03       bellow          3

Add line numbers per group of variable values in R dataframe

3 Answers3

data: