How to reorder this dataframe?-CodePudding

I have the following dataframe:

df =structure(list(texts = c("Text 1", 
"Text 2", 
"Text 3", 
"Text 4", 
"Text 5", 
"Text 6", 
"Text 7", 
"Text 8", 
"Text 9", 
"Text 10"
), E = c(1, 0, 0, 0, 0, 0, 0, 0, 0, 0), S = c(0, 0, 0, 1, 0, 
0, 1, 0, 0, 1), G = c(0, 1, 1, 0, 1, 1, 0, 1, 1, 0)), row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10"), class = "data.frame")

     texts E S G
1   Text 1 1 0 0
2   Text 2 0 0 1
3   Text 3 0 0 1
4   Text 4 0 1 0
5   Text 5 0 0 1
6   Text 6 0 0 1
7   Text 7 0 1 0
8   Text 8 0 0 1
9   Text 9 0 0 1
10 Text 10 0 1 0

As you can see now I have 3 columns (E, S, G) where 1 or 0 says which text belongs to which column. What I would like to get is the following:

     texts Class
1   Text 1  E 
2   Text 2  G 
3   Text 3  G 
4   Text 4  S 
5   Text 5  G 
6   Text 6  G 
7   Text 7  S 
8   Text 8  G 
9   Text 9  G 
10 Text 10  S

Can anyone help me?

Thanks!

CodePudding user response：

If you only have a single 1 in a row like in this example you can use max.col in base R -

cbind(df[1], Class = names(df)[-1][max.col(df[-1])])

#     texts Class
#1   Text 1     E
#2   Text 2     G
#3   Text 3     G
#4   Text 4     S
#5   Text 5     G
#6   Text 6     G
#7   Text 7     S
#8   Text 8     G
#9   Text 9     G
#10 Text 10     S

Another data.table option -

library(data.table)

melt(setDT(df), id.vars = 'texts')[value == 1][
  ,value:= NULL][gtools::mixedorder(texts)]

CodePudding user response：

Spread it to long, filter for ones and then drop the extra column

df %>%
  pivot_longer(cols = E:G, names_to = 'Class') %>%
  filter(value == 1) %>%
  select(-value)

CodePudding user response：

Using ifelse

df$E = ifelse(df$E == 1, "E", NA)
df$S = ifelse(df$S == 1, "S", NA)
df$G = ifelse(df$G == 1, "G", NA)

class = do.call(pmax, c(df[-1], na.rm=TRUE))
df = cbind.data.frame(texts = df[,1], class = class)


    texts class
1   Text 1     E
2   Text 2     G
3   Text 3     G
4   Text 4     S
5   Text 5     G
6   Text 6     G
7   Text 7     S
8   Text 8     G
9   Text 9     G
10 Text 10     S

CodePudding user response：

We could use c_across after grouping by row

library(dplyr)
df %>%
   rowwise %>% 
   transmute(texts, Class = names(.)[-1][which.max(c_across(E:G))]) %>% 
   ungroup

-output

# A tibble: 10 x 2
   texts   Class
   <chr>   <chr>
 1 Text 1  E    
 2 Text 2  G    
 3 Text 3  G    
 4 Text 4  S    
 5 Text 5  G    
 6 Text 6  G    
 7 Text 7  S    
 8 Text 8  G    
 9 Text 9  G    
10 Text 10 S

Or using pmap

library(purrr)
 df %>% 
  transmute(texts, Class = pmap_chr(across(E:G), 
         ~ names(which.max(c(...)))))
     texts Class
1   Text 1     E
2   Text 2     G
3   Text 3     G
4   Text 4     S
5   Text 5     G
6   Text 6     G
7   Text 7     S
8   Text 8     G
9   Text 9     G