Hi @Proteus73, and welcome to the forum! For future reference, you may want to consult this post of how to create a good reprex. If we cannot reproduce the data you are working with or at least recreate something with a similar structure, there isn't really any way to test whether a proposed routine would solve your problem. Instead, we have to go out on a limb and "hope" that it will. That said, based on your explanation, it sounds as though a simply regex routine should do the trick.
There are multiple ways to do what follows, but they all amount to targeting the gauge.location column and simply removing all trailing MDF.csv suffixes. Below, I provide three slightly different approaches, though they should all produce equivalent results:
library(tidyverse)
# create data similar to what was shared
df <- data.frame(
stringsAsFactors = FALSE,
DT.Index = c("01/01/2015 09:00",
"02/01/2015 09:00","03/01/2015 09:00","04/01/2015 09:00",
"05/01/2015 09:00","06/01/2015 09:00","07/01/2015 09:00",
"08/01/2015 09:00","09/01/2015 09:00","10/01/2015 09:00"),
FQ..m3.s. = c(8.805,7.26,7.626,6.168,
5.623,6.153,11.009,7.007,8.627,9.042),
gauge.location = c("Antrim MDF.csv",
"Antrim MDF.csv","Antrim MDF.csv","Antrim MDF.csv","Antrim MDF.csv",
"Ballinderry MDF.csv","Ballinderry MDF.csv",
"Ballinderry MDF.csv","Ballinderry MDF.csv",
"Ballinderry MDF.csv")
)
# use mutate and str_remove to remove unwanted suffixed in the gauge.location
# column
df %>%
mutate(gauge.location = stringr::str_remove(gauge.location, ' MDF\\.csv
```))
#> DT.Index FQ..m3.s. gauge.location
#> 1 01/01/2015 09:00 8.805 Antrim
#> 2 02/01/2015 09:00 7.260 Antrim
#> 3 03/01/2015 09:00 7.626 Antrim
#> 4 04/01/2015 09:00 6.168 Antrim
#> 5 05/01/2015 09:00 5.623 Antrim
#> 6 06/01/2015 09:00 6.153 Ballinderry
#> 7 07/01/2015 09:00 11.009 Ballinderry
#> 8 08/01/2015 09:00 7.007 Ballinderry
#> 9 09/01/2015 09:00 8.627 Ballinderry
#> 10 10/01/2015 09:00 9.042 Ballinderry
# use mutate and str_replace to replace unwanted suffixed in the gauge.location
# column
df %>%
mutate(gauge.location = stringr::str_replace(gauge.location, ' MDF\\.csv
```, ''))
#> DT.Index FQ..m3.s. gauge.location
#> 1 01/01/2015 09:00 8.805 Antrim
#> 2 02/01/2015 09:00 7.260 Antrim
#> 3 03/01/2015 09:00 7.626 Antrim
#> 4 04/01/2015 09:00 6.168 Antrim
#> 5 05/01/2015 09:00 5.623 Antrim
#> 6 06/01/2015 09:00 6.153 Ballinderry
#> 7 07/01/2015 09:00 11.009 Ballinderry
#> 8 08/01/2015 09:00 7.007 Ballinderry
#> 9 09/01/2015 09:00 8.627 Ballinderry
#> 10 10/01/2015 09:00 9.042 Ballinderry
# use mutate and gsub to replace unwanted suffixed in the gauge.location column
df %>%
mutate(gauge.location = gsub(' MDF\\.csv
```, '', gauge.location))
#> DT.Index FQ..m3.s. gauge.location
#> 1 01/01/2015 09:00 8.805 Antrim
#> 2 02/01/2015 09:00 7.260 Antrim
#> 3 03/01/2015 09:00 7.626 Antrim
#> 4 04/01/2015 09:00 6.168 Antrim
#> 5 05/01/2015 09:00 5.623 Antrim
#> 6 06/01/2015 09:00 6.153 Ballinderry
#> 7 07/01/2015 09:00 11.009 Ballinderry
#> 8 08/01/2015 09:00 7.007 Ballinderry
#> 9 09/01/2015 09:00 8.627 Ballinderry
#> 10 10/01/2015 09:00 9.042 Ballinderry