CODEWITHSUNDEEP
rdataframemerge
cgxytf
cgxytf

Reputation: 431

Merge multiple TRUE/FALSE columns while keeping remaining columns in a dataframe

I have a dataframe in where I want to merge 3 TRUE/FALSE columns into one column, while keeping the rest of my original dataframe columns.

Here's an example of the data (though the real data have more columns):

table <- "year type red blue yellow
1  2019    A TRUE FALSE FALSE
2  2019    A FALSE TRUE FALSE
3  2019    B FALSE TRUE FALSE
4  2019    B TRUE FALSE FALSE
5  2020    C FALSE FALSE TRUE 
6  2020    C FALSE FALSE TRUE
7  2020    D FALSE TRUE FALSE
8  2021    D TRUE FALSE FALSE
9  2021    D TRUE FALSE FALSE
10 2021    D FALSE TRUE FALSE"

df <- read.table(text=table, header = TRUE)
df

I want to merge the color columns to look like this:

table <- "year type color
1  2019    A red
2  2019    A blue
3  2019    B blue
4  2019    B red
5  2020    C yellow 
6  2020    C yellow
7  2020    D blue
8  2021    D red
9  2021    D red
10 2021    D blue"

I've tried using melt() from this solution, but this only kept one specified column besides those being merged, and dropped the rest.

Any help is appreciated.

Upvotes: 0

Views: 325

Answers (4)

ThomasIsCoding
ThomasIsCoding

Reputation: 101247

Another base R option using ´which+order`

with(
  data.frame(which(df[-(1:2)] == "TRUE", arr.ind = TRUE)),
  cbind(df[1:2], color = names(df)[-(1:2)][col[order(row)]])
)

gives

   year type  color
1  2019    A    red
2  2019    A   blue
3  2019    B   blue
4  2019    B    red
5  2020    C yellow
6  2020    C yellow
7  2020    D   blue
8  2021    D    red
9  2021    D    red
10 2021    D   blue

Upvotes: 1

akrun
akrun

Reputation: 887048

We could use the vectorized max.col

cbind(df[1:2], color = names(df)[-(1:2)][max.col(df[-(1:2)], 'first')])

Upvotes: 1

Rui Barradas
Rui Barradas

Reputation: 76402

Here is a base R solution.

df2 <- df[1:2]
df2$color <- apply(df[3:5], 1, function(k) names(df[3:5])[k])
df2
#   year type  color
#1  2019    A    red
#2  2019    A   blue
#3  2019    B   blue
#4  2019    B    red
#5  2020    C yellow
#6  2020    C yellow
#7  2020    D   blue
#8  2021    D    red
#9  2021    D    red
#10 2021    D   blue

Upvotes: 2

Snipeskies
Snipeskies

Reputation: 288

table <- "year type red blue yellow
1  2019    A TRUE FALSE FALSE
2  2019    A FALSE TRUE FALSE
3  2019    B FALSE TRUE FALSE
4  2019    B TRUE FALSE FALSE
5  2020    C FALSE FALSE TRUE 
6  2020    C FALSE FALSE TRUE
7  2020    D FALSE TRUE FALSE
8  2021    D TRUE FALSE FALSE
9  2021    D TRUE FALSE FALSE
10 2021    D FALSE TRUE FALSE"

df <- read.table(text=table, header = TRUE)
df

df2 <- cbind(df[1:2],  colnames(df)[apply(df[,3:5], 1, function(x) which(x)) + 2])
colnames(df2)[3] <- 'color'
df2

   year type  color
1  2019    A    red
2  2019    A   blue
3  2019    B   blue
4  2019    B    red
5  2020    C yellow
6  2020    C yellow
7  2020    D   blue
8  2021    D    red
9  2021    D    red
10 2021    D   blue

Upvotes: 2

Related Questions