Filtering, string splitting and replacing the values in R-CodePudding

I have a dataframe which have income statements, I want to filter out the dataframe which have more than 500 yearly income, then correct them into monthly and yearly column.

In 2nd row, income is 300_100 (yearly_monthly) but its wrong in monthly column, so I want to change that after splitting the income column.

ID	yearly	monthly	income
1	100	200	100_200
2	500	100	300_100
3	1000	500	600_500
4	300	250	500_250

Output should be like

ID	yearly	monthly	income
1	100	200	100_200
2	300	100	300_100
3	600	500	600_500
4	300	250	500_250

CodePudding user response：

A possible solution:

library(tidyverse)

df %>%
  separate(income, into = c("yearly", "monthly"), remove = F, convert = T) %>% 
  inner_join(df, by = "ID") %>% 
  mutate(yearly = if_else(yearly.y > 500, yearly.x, yearly.y)) %>% 
  select(ID, yearly, monthly = monthly.x, income = income.x)

#>   ID yearly monthly  income
#> 1  1    100     200 100_200
#> 2  2    500     100 300_100
#> 3  3    600     500 600_500
#> 4  4    300     250 500_250