Sequence along identical rows

Hi
I have a tibble and want to have a sequence according to the grouping Goods/COICOP:

test <- tribble( 
  ~Goods , ~COICOP  ,~Value ,~G ,    ~Begin,   ~End,
  "G01-03", "c01_1_1", 0.025, "01-03",     1    , 3,  
  "G01-03", "c01_1_1", 0.025, "01-03",     1    , 3, 
  "G01-03", "c01_1_1", 0.025, "01-03",     1    , 3,
  "G01-03", "c01_1_3", 0.025, "01-03",     1    , 3,
  "G01-03", "c01_1_3", 0.025, "01-03",     1    , 3,  
  "G01-03", "c01_1_3", 0.025, "01-03",     1    , 3)

test <- test %>% 
  mutate(G1 = Begin) 
  group_by(COICOP, Goods) %>% 
  mutate(G1 = ifelse(!is.na(lag(G1)), G1 +1, G1)) 

G1 should look like 1,2,3,1,2,3. However, I only manage to get 1,2,2,1,2,2.
Any idea how to correct this?
Cheers
Renger

The G1 referred to in the ifelse is always the G1 in the current row, so G1 + 1 is always 2. Does the following code do what you want?

test <- test %>% 
  group_by(COICOP, Goods) %>% 
  mutate(G1 = row_number())
1 Like

Unfortunately, that doesn't solve the problem, as the tibble has many more lines with Goods like G86-87. In case like that it should be 86,87.
I tried this, but that also doesn't solve the problem

test <- test %>% 
  mutate(G1 = Begin) 
  group_by(COICOP, Goods) %>% 
  mutate(G1 = ifelse(!is.na(lag(G1)), lag(G1) +1, G1)) 

but this works

G1-1+ row_number(G)

Cheers
Renger

test <- tribble( 
  ~Goods , ~COICOP  ,~Value ,~G ,    
  "G01-03", "c01_1_1", 0.025, "01-03",
  "G01-03", "c01_1_1", 0.025, "01-03",
  "G01-03", "c01_1_1", 0.025, "01-03",
  "G01-03", "c01_1_3", 0.025, "01-03",
  "G01-03", "c01_1_3", 0.025, "01-03",
  "G01-03", "c01_1_3", 0.025, "01-03",
  "G86-87", "c01_1_3", 0.025, "01-03",
  "G86-87", "c01_1_3", 0.025, "01-03")
test <- test %>% 
  mutate(G1 = as.integer(str_extract(Goods,"\\d+"))) %>% 
group_by(COICOP, Goods) %>% mutate(counter=G1+row_number()-1)

Note that I expanded the example to cover the requirements given. (as best I knew)

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.