Multiple string replacement, decimals to quarters

user_707145 0
Viewed 83

I want to replace .00 with -Q1, .25 with -Q2, .50 with -Q3, and .75 with -Q4 as given below. However, my code is not working as expected. Any hints?

library(tidyverse)

dt1 <- 
  tibble(Date = c(2015.00, 2015.25, 2015.50, 2015.75))

dt1
# A tibble: 4 x 1
   Date
  <dbl>
1 2015 
2 2015.
3 2016.
4 2016.

dt1 %>% 
  pull(Date)

[1] 2015.00 2015.25 2015.50 2015.75

dt1 %>% 
  mutate(Date1 = str_replace_all(string = Date, pattern = c(".00" = "-Q1", ".25" = "-Q2", ".50" = "-Q3", ".75" = "-Q4")))

# A tidytable: 4 × 2
   Date Date1  
  <dbl> <chr>  
1 2015  2015   
2 2015. 2015-Q2
3 2016. 2015.5 
4 2016. 2015-Q4
stringr tidyverse r
6 Answers

There also is a zoo-function for that:

library(tidyverse)
library(zoo)

dt1 <- 
  tibble(Date = c(2015.00, 2015.25, 2015.50, 2015.75))

dt1 %>%
  mutate(Date1 = format.yearqtr(Date, format = "%Y.Q%q") )

# Date Date1  
# <dbl> <chr>  
# 1 2015  2015.Q1
# 2 2015. 2015.Q2
# 3 2016. 2015.Q3
# 4 2016. 2015.Q4
user_61620110

You may also use integer division %/% and modulo division %% simultaneously

paste0(dt1$Date %/% 1, '-Q',(dt1$Date %% 1)*4 +1)

[1] "2015-Q1" "2015-Q2" "2015-Q3" "2015-Q4"

Thus, using it in piped syntax as

dt1 %>%
  mutate(date1 = paste0(Date %/% 1, '-Q',(Date %% 1)*4 +1))

# A tibble: 4 x 2
   Date date1  
  <dbl> <chr>  
1 2015  2015-Q1
2 2015. 2015-Q2
3 2016. 2015-Q3
4 2016. 2015-Q4
user_28848590 
vec <- c("00" = "-Q1", "25" = "-Q2", "50" = "-Q3", "75" = "-Q4")
dt1 %>%
  mutate(new = paste0(Date %/% 1, vec[sprintf("%02d", Date %% 1 * 100)]))

   Date new    
  <dbl> <chr>  
1 2015  2015-Q1
2 2015. 2015-Q2
3 2016. 2015-Q3
4 2016. 2015-Q4
user_83802720

here is a quick fix:

 dt1 %>% 
  mutate(Date1 = str_replace_all(format(Date, nsmall = 2), 
                pattern = c(".00" = "-Q1", ".25" = "-Q2", ".50" = "-Q3", ".75" = "-Q4")))

The problem is that 2015.00 is first transformed to character at which point it becomes 2015. Therefore, the string replacement fails. You can see this, by trying as.character(2015.00).

However, this can easily be fixed by using format to format the number first.

user_54368770 
library(tidyverse)


dt1 <- 
    as.character(c(2015.00, 2015.25, 2015.50, 2015.75))


dt1 <- if_else(str_detect(dt1, '\\.', negate = TRUE),
               paste0(dt1, '.00'), #If condition TRUE
               dt1) #if condition FALSE


value_before <- c("\\.00","\\.25","\\.5","\\.75" )
value_after  <- c("-Q1", "-Q2","-Q3", "-Q4")

tibble(Date = str_replace(dt1, value_before, value_after))
#> # A tibble: 4 x 1
#>   Date   
#>   <chr>  
#> 1 2015-Q1
#> 2 2015-Q2
#> 3 2015-Q3
#> 4 2015-Q4

Created on 2021-06-01 by the reprex package (v2.0.0)

user_111687660

A solution with dyplr and tidyr:

  1. Prepare decimals for further process with format
  2. separate and mutate with -Q1-Q4
  3. unite
library(tidyr)
library(dplyr)
dt1 %>% 
  mutate(Date = format(round(Date, digits=2), nsmall = 2)) %>% 
  separate(Date, into = c("Year", "Quarter"), remove=FALSE) %>% 
  mutate(Quarter = recode(Quarter, "00" = "-Q1", "25" = "-Q2", "50" = "-Q3", "75" = "-Q4")) %>% 
  unite("new", Year:Quarter, sep = "")

Output:

  Date    new    
  <chr>   <chr>  
1 2015.00 2015-Q1
2 2015.25 2015-Q2
3 2015.50 2015-Q3
4 2015.75 2015-Q4
user_133216470
Related