Re-order data frame to be consistent

user_15220667 0
Viewed 57

I have a data frame with 9 columns and 100,000 rows. The information in each row is mixed, like in the following example:

C1 <- c("Gender F", "Age 74", "Gender M")
C2 <- c("Age 54", "Gender M", "Col eyes Blue")
C3 <- c("Col eyes Brown","Col eyes Blue", "Age 56")
C4 <- c("Col hair Brown", "Col hair Black", "Col hair Blonde")

df <- cbind(C1, C2, C3, C4)

> df
     C1         C2              C3               C4               
[1,] "Gender F" "Age 54"        "Col eyes Brown" "Col hair Brown" 
[2,] "Age 74"   "Gender M"      "Col eyes Blue"  "Col hair Black" 
[3,] "Gender M" "Col eyes Blue" "Age 56"         "Col hair Blonde"

I would like to make this data frame consistent, in other words, I'd like to have all the "Gender" information in the same column, and so on. I am new in R and struggling to find the solution. Can anybody help?

r matrix
2 Answers

In case you have all keys in each row sort in apply will work:

 t(apply(df, 1, sort))
#     [,1]     [,2]             [,3]              [,4]      
#[1,] "Age 54" "Col eyes Brown" "Col hair Brown"  "Gender F"
#[2,] "Age 74" "Col eyes Blue"  "Col hair Black"  "Gender M"
#[3,] "Age 56" "Col eyes Blue"  "Col hair Blonde" "Gender M"

If that is not the case you can try to subset the unique keys. Where I assume the key is everything but not the last word and getting it using sub.

t1 <- sub(" [^ ]*$", "", df)
t2 <- unique(as.vector(t1))
do.call(rbind, lapply(seq_len(nrow(df)), function(i) df[i,match(t1[i,], t2)]))
#     C1         C2       C3               C4               
#[1,] "Gender F" "Age 54" "Col eyes Brown" "Col hair Brown" 
#[2,] "Gender M" "Age 74" "Col eyes Blue"  "Col hair Black" 
#[3,] "Gender M" "Age 56" "Col eyes Blue"  "Col hair Blonde"
user_104885040

A base R option using

matrix(
  df[cbind(c(t(row(df))), c(apply(gsub("\\s\\w+$", "", df), 1, order)))],
  nrow(df),
  byrow = TRUE
)

or

matrix(c(t(df))[order(c(col(t(df))), c(t(df)))], nrow(df), byrow = TRUE)

gives

     [,1]     [,2]             [,3]              [,4]
[1,] "Age 54" "Col eyes Brown" "Col hair Brown"  "Gender F"
[2,] "Age 74" "Col eyes Blue"  "Col hair Black"  "Gender M"
[3,] "Age 56" "Col eyes Blue"  "Col hair Blonde" "Gender M"
user_121587570
Related