Add a column to dataframe from values of next row

Question

I have a dataframe with the below structure and values:

total_size <- 5000;
id line_number
1   1232
2   1456
3   1832
4   2002

I need to add a new column dynamically to the data frame with values from the next_row. ie: The new column value should be: (line_number) of next row -1. The value of the last row should be populated with the total_size value.

The final output I need to produce is:

id line_number   end_line_number
1   1232         1455
2   1456         1831
3   1832         2001
4   2002         5000

Any idea how I can generate this dynamically in R?

Answer 1

This is pretty basic question. You can create a new column by removing the first entry of the 2nd column, subtracting 1 from it and adding NA for the last entry.

# assuming your data.frame is called 'dt'
dt$end_line_number <- c(dt$line_number[-1]-1, NA)

(or) using transform

dt <- transform(dt, end_line_number = c(line_number[-1]-1, NA))