Using separate and mutate functions

user_17344174 0
Viewed 47

I am new to R and trying to mutate the following character variable "Tax.Rate..." into four different columns (i.e., CGST, SGST, UTGST, and IGST) with tax rates applicable to that head under that column. Example of dataset shown below:

df # A tibble: 3 x 1 Tax.Rate....

1 "CGST 2.5% + SGST 2.5%" 2 "CGST 6% + UTGST 6%"
3 "IGST 12% "

I have tried using 'separate' and 'mutate' functions with little success

Any guidance would be appreciated

dataframe r
2 Answers

I'm sure this can be done concisely in base R as well, but here's a tidyverse approach where I first split the data into a new row at each plus, then trim the extra spaces, then split into two columns.

library(tidyverse)
df <- data.frame(Tax.Rate = c("CGST 2.5% + SGST 2.5%", "CGST 6% + UTGST 6%", "IGST 12% "))

df %>%
  mutate(orig_row = row_number()) %>% # optional, for later tracking
  separate_rows(Tax.Rate, sep = "\\+") %>%
  mutate(Tax.Rate = str_trim(Tax.Rate)) %>%
  separate(Tax.Rate, c("group", "rate"), extra = "merge", remove = FALSE)

# A tibble: 5 × 4
  Tax.Rate  group rate  orig_row
  <chr>     <chr> <chr>    <int>
1 CGST 2.5% CGST  2.5%         1
2 SGST 2.5% SGST  2.5%         1
3 CGST 6%   CGST  6%           2
4 UTGST 6%  UTGST 6%           2
5 IGST 12%  IGST  12%          3

This will produce a "long" shaped table, but if you want it "wide" with separate columns for each group (jurisdiction?) then you could add the following:

[from the end of the "separate()" line] %>%
select(-Tax.Rate) %>%
pivot_wider(names_from = group, values_from = rate)

for this result

# A tibble: 3 × 5
  orig_row CGST  SGST  UTGST IGST 
     <int> <chr> <chr> <chr> <chr>
1        1 2.5%  2.5%  NA    NA   
2        2 6%    NA    6%    NA   
3        3 NA    NA    NA    12%
user_68518250

We could:

  1. Use separate_rows separating by + using \\+ to escape the special character
  2. then str_trim to remove starting space etc...
  3. separate this column by " " 4.group_by and add id to avoid nested output
  4. pivot_wider
library(dplyr)
library(tidyr)
library(stringr)
df %>% 
  separate_rows(Tax.Rate, sep = "\\+") %>% 
  mutate(Tax.Rate = str_trim(Tax.Rate)) %>% 
  separate(Tax.Rate, c("name", "value"), sep = " ") %>% 
  group_by(name) %>% 
  mutate(id = row_number()) %>% 
  pivot_wider(
    names_from = name, 
    values_from = value
  ) %>% 
  select(-id)

  CGST  SGST  UTGST IGST 
  <chr> <chr> <chr> <chr>
1 2.5%  2.5%  6%    12%  
2 6%    NA    NA    NA

data:

structure(list(Tax.Rate = c("CGST 2.5% + SGST 2.5%", "CGST 6% + UTGST 6%", 
"IGST 12%")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-3L))
user_133216470
Related