creating new variable based on other variables from different columns in df

Question

I am trying to create a new variable (incomeX) in a data frame based on the last character of the colnames so if:

      income1 income2 income3 income4 income5
1        0       1       0       0       0
2        1       0       0       0       0
3        1       0       0       0       0
4        1       0       0       0       0
5        1       0       0       0       0
6        1       0       0       0       0
7        0       1       0       0       0
8        1       0       0       0       0
9        1       0       0       0       0
10       0       0       0       1       0

I would get:

     income1 income2 income3 income4 income5 incomeX
1        0       1       0       0       0      2
2        1       0       0       0       0      1
3        1       0       0       0       0      1
4        1       0       0       0       0      1
5        1       0       0       0       0      1
6        1       0       0       0       0      1
7        0       1       0       0       0      2
8        1       0       0       0       0      1
9        1       0       0       0       0      1
10       0       0       0       1       0      4

Data frames show up in several languages (R and Python with Pandas for instance) so please specify a language tag — paisanco
– paisanco, Commented Jan 27, 2018 at 19:35
income1 income2 income3 income4 income5 0 1 0 0 0 1 0 0 0 0 1 0 0 0 0 1 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 1 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 1 0 0 — Mario Perez
– Mario Perez, Commented Jan 27, 2018 at 19:47
i don't want to just do this, because I may need to do it for several other columns. — Mario Perez
– Mario Perez, Commented Jan 27, 2018 at 19:49
income$incomeX <- ifelse(income$income1 == 1, income$incomeX <- 1, ifelse(income$income2 == 1, income$incomeX <- 2, ifelse(income$income3 == 1, income$incomeX <- 3, ifelse(income$income4 == 1, income$incomeX <- 4, ifelse(income$income5 == 1, income$incomeX <- 5, NA))))) — Mario Perez
– Mario Perez, Commented Jan 27, 2018 at 19:49

Onyambu · Accepted Answer · 2018-01-28 06:57:56Z

1

You can use which from base r to solve this:

s=which(data==1,arr.ind = T)
data$IncomeX[s[,1]]=s[,2]
data
   income1 income2 income3 income4 income5 IncomeX
1        0       1       0       0       0       2
2        1       0       0       0       0       1
3        1       0       0       0       0       1
4        1       0       0       0       0       1
5        1       0       0       0       0       1
6        1       0       0       0       0       1
7        0       1       0       0       0       2
8        1       0       0       0       0       1
9        1       0       0       0       0       1
10       0       0       0       1       0       4

answered Jan 28, 2018 at 6:57

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Len Greski · Accepted Answer · 2018-01-27 23:48:07Z

Here is an approach using the tidyverse. We convert the data to a tidy data form, separate the characters income from numbers, select rows where the value == 1, and then use the rowId to merge the results back into the original data.

inputData <- " rowId     income1 income2 income3 income4 income5
1        0       1       0       0       0
2        1       0       0       0       0
3        1       0       0       0       0
4        1       0       0       0       0
5        1       0       0       0       0
6        1       0       0       0       0
7        0       1       0       0       0
8        1       0       0       0       0
9        1       0       0       0       0
10       0       0       0       1       0
"
data <- read.table(text=inputData,header=TRUE)
library(dplyr)
library(tidyr)
data %>% gather(variable,value,-rowId) %>%
     extract(variable,into = c("varname", "number"), 
             regex = "([A-Za-z]+)([0-9]+)") %>%
     filter(value == 1) %>% rename(incomeX = number) %>%
     select(-value,-varname) %>%
     left_join(data,.) %>% arrange(rowId)

...and the output:

+      left_join(data,.) %>% arrange(rowId)
Joining, by = "rowId"
   rowId income1 income2 income3 income4 income5 incomeX
1      1       0       1       0       0       0       2
2      2       1       0       0       0       0       1
3      3       1       0       0       0       0       1
4      4       1       0       0       0       0       1
5      5       1       0       0       0       0       1
6      6       1       0       0       0       0       1
7      7       0       1       0       0       0       2
8      8       1       0       0       0       0       1
9      9       1       0       0       0       0       1
10    10       0       0       0       1       0       4
>

Collectives™ on Stack Overflow

creating new variable based on other variables from different columns in df

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related