How to swap the values in a two column subset in R?

user_14368636 0
Viewed 115

I have a dataframe, df, in R that contains a column for sex and a column for age. During the data cleaning process, I noticed that for a few values the age and sex were flipped, so that the data looks like this:

    SequenceNo    Sex       Age
 1. sequence1     Male      65
 2. sequence2     Female    45
 3. sequence3     21        Male
 4. sequence4     Female    12

I know I can go row by row and manually assign the correct values to fix the data, but is there an easy, unifying solution that can flip only the rows where the data is mismatched?

tidyr tidyverse data-cleaning dataframe r
3 Answers

We could create a logical index (either use grepl to check only digits (\\d+ - for numeric floats, use [0-9.]+ and assuming there are negative values -?) from start (^) to end ($) of string or reverse \\D any non-digit or may use as.numeric/as.integer and check with NA elements using is.na) and swap it by swapping the column names, then change the type of the columns with type.convert

i1 <- grepl("^-?[0-9.]+$", df$Sex)
df[i1, c("Sex", "Age")] <- df[i1, c("Age", "Sex")]
df <- type.convert(df, as.is = TRUE)

-output

> df
   SequenceNo    Sex Age
1.  sequence1   Male  65
2.  sequence2 Female  45
3.  sequence3   Male  21
4.  sequence4 Female  12
> str(df)
'data.frame':   4 obs. of  3 variables:
 $ SequenceNo: chr  "sequence1" "sequence2" "sequence3" "sequence4"
 $ Sex       : chr  "Male" "Female" "Male" "Female"
 $ Age       : int  65 45 21 12

data

df <- structure(list(SequenceNo = c("sequence1", "sequence2", "sequence3", 
"sequence4"), Sex = c("Male", "Female", "21", "Female"), Age = c("65", 
"45", "Male", "12")), class = "data.frame", row.names = c("1.", 
"2.", "3.", "4."))
user_37322710

Update: to avoid NA (thanks to rjen): We create a helper column than do the same as in answer 1:

library(tidyverse)
df %>% 
    mutate(helper = paste0(Sex, Age),
           Age = parse_number(helper),
           Sex = str_replace_all(helper, "[:digit:]", "")) %>% 
    select(-helper)

SequenceNo    Sex Age
1.  sequence1   Male  65
2.  sequence2 Female  45
3.  sequence3   Male  21
4.  sequence4 Female  12

First answer: With paste0(Sex, Age) we combine both columns:

  1. We replace all digits with nothing i.e. remove all digits
  2. We extract the numbers only
library(tidyverse)
df %>% 
    mutate(Sex = str_replace_all(paste0(Sex, Age), "[:digit:]", "")) %>% 
    mutate(Age = parse_number(paste0(Sex, Age)))

  SequenceNo    Sex Age
1  sequence1   Male  65
2  sequence2 Female  45
3  sequence3   Male  NA
4  sequence4 Female  12
user_133216470

An approach using if_else().

library(dplyr)

df %>%
  mutate(SexNew = if_else(Sex %in% c('Male', 'Female'), Sex, Age),
         Age = if_else(Age %in% 1:120, Age, Sex)) %>%
  select(-Sex, Sex = SexNew)

#   SequenceNo Age    Sex
# 1  sequence1  65   Male
# 2  sequence2  45 Female
# 3  sequence3  21   Male
# 4  sequence4  12 Female
user_128202050
Related