How to replace multiple string columns to binary values (0 and 1) in a dataframe?

Question

[biogamer.31]

Hello! I am trying to get a binary matrix but firt I need to replace multiple string columns to binary values (0 and 1). I tried to get it in R and python but the code didn't work. I was wondering if someone could help me.

I have a matrix of 29,584 rows x 982 columns, :

For each column that start with X, there are various string values. These values start in bb_, bpp_ and bp_. In addition, there are missing data (in blank). I would like to replace with 1 all the string values from each column that start witn X (or all columns except G) and to replace with 0 the missing data from the columns that start with X.

Please, I would be very grateful by their help.

Answer 1

[cpad0112]

Hope you know how to use R and load excel sheet in R, use following:

$ library(dplyr)
$ df %>% 
   mutate(across(!starts_with("G"), ~ifelse(.!="",1,0)))

Please post errors if there are any. Please do not post images of the data. Post data always.

[biogamer.31]

Thank you for your help cpad0112. This code work great! Also, thank you for the suggestions about how to post.

biogamer.31

Answer 2

[Sam]

Assuming your data is called dat, in R, do the following

dat <- read.table(xxx)
name <- dat$G
dat[dat!=""] <- 1
dat[dat==""] <- 0
dat$G <- name

[biogamer.31]

Thank you Sam. This code is an awesome help.