I have a data frame that contains three columns for country, gender and sex and then all following columns are years 1999-2020 which contain the amount of people living in the row's country/age/gender. Here is a much smaller subset of this data// repex:
age <- c("0-7", "0-7", "0-7", "8-10", "8-10","8-10","11-15","11-15","11-15")
sex <- c("m","m","m", "f","f","f","both","both","both")
country <- c('Spain','North France','South France', 'Spain','North France','South France', 'Spain','North France','South France')
1999 <- c(21000, 23400, 26800, 21000, 23400, 26800, 21000, 23400, 26800)
2000 <- c(23444, 78330, 83920, 21000, 23400, 26800, 21000, 23400, 26800)
2001 <- c(21000, 23400, 26800, 21000, 23400, 26800, 21000, 23400, 26800)
pop.data <- data.frame(age, sex, country, 1999, 2000, 2001)
I need to combine adjacent rows of north and south france together so that they are just 'France' and then combine their values together. I have already done work on this dataset and all I can manage is to rename them to France with
pop.data$country <- gsub("^N\\.France|S\\.France$", "France", pop.data$country, fixed = FALSE)
is there any way to merge the adjacent values then in to one new row and get rid of the original two? Note that they should only be combined if they are of the same age and gender value (which is the case as they are next to each other)... please comment if you need more info