Creating multiple columns by computation in R

Question

Assume I have a data frame 100 X 100, here is a small sample of my data

df<-read.table (text=" Id   san1    san2    san3
1   A   A   A
2   A   A   A
3   A   M   M
4   M   A   A
", header=TRUE)

I want to transpose it first. and the calculate the sum of rows and then create two new columns as you can see below

San     1   2   3   4   A   M
san1    A   A   A   M   3   1
san2    A   A   M   A   3   1
san3    A   A   M   A   3   1

I have used df(t) to transpose, and then I have used mutate and rowsums, but it does not work for me.

Onyambu · Accepted Answer · 2020-11-10 21:08:16Z

3

Another base R solution:

A <- data.frame(t(df[-1]))
names(A) <- df[,1]
cbind(A, as.data.frame.matrix(t(table(stack(df[-1])))))
     1 2 3 4 A M
san1 A A A M 3 1
san2 A A M A 3 1
san3 A A M A 3 1

answered Nov 10, 2020 at 21:08

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

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

Comments

Matt · Accepted Answer · 2020-11-10 21:05:10Z

3

Here is a dplyr solution:

library(dplyr)

df %>% 
  pivot_longer(-Id) %>% 
  pivot_wider(names_from = Id, values_from = value) %>% 
  rename(San = name) %>% 
  mutate(A = rowSums(. == "A"),
         M = rowSums(. == "M"))

Which gives us:

  San   `1`   `2`   `3`   `4`       A     M
  <chr> <chr> <chr> <chr> <chr> <dbl> <dbl>
1 san1  A     A     A     M         3     1
2 san2  A     A     M     A         3     1
3 san3  A     A     M     A         3     1

answered Nov 10, 2020 at 21:05

Matt

7,4412 gold badges16 silver badges35 bronze badges

Comments

akrun · Accepted Answer · 2020-11-10 20:52:45Z

2

We can use

library(data.table)
library(tidyr)
data.table::transpose(df, make.names = 'Id', keep.names = 'San') %>%
     mutate(A = rowSums(.[-1] == 'A'), M = rowSums(.[2:5] == 'M'))

-output

#    San 1 2 3 4 A M
#1 san1 A A A M 3 1
#2 san2 A A M A 3 1
#3 san3 A A M A 3 1

answered Nov 10, 2020 at 20:52

akrun

891k38 gold badges590 silver badges700 bronze badges

6 Comments

akrun Over a year ago

@user330 is it the same data on your post

user330 Over a year ago

Error: unexpected '=' in: "data.table::transpose(df, make.names = 'Id', keep.names = 'San') %>% mutate(1 ="

user330 Over a year ago

A and M hypothetical and i want to use it for any binary data , e.g., 0,1

akrun Over a year ago

@user330 in that case, you can just do rowSums(.[2:5]) and rowSums(!.[2:5])

user330 Over a year ago

Error: unexpected ')' in: "data.table::transpose(df, make.names = 'Id', keep.names = 'San') %>% mutate(A =rowSums(.[2:5]), M = rowSums(!.[2:5])))"

|

Dubukay · Accepted Answer · 2020-11-10 20:34:51Z

0

Does this do what you want?

a_col <- rowSums(t(df)=="A")
a_col[1] <- "A"
m_col <- rowSums(t(df)=="M")
m_col[1] <- "M"
cbind(t(df), a_col, m_col)

answered Nov 10, 2020 at 20:34

Dubukay

2,1011 gold badge11 silver badges18 bronze badges

1 Comment

user330 Over a year ago

Sorry, it does not work for me as it does not reflect my output. also does not work for large data

Konrad · Accepted Answer · 2020-11-10 21:43:05Z

0

Another solution that works independently of the A, M and remaining values using table and map_dfr offered via purrr:

library("tidyverse")
df <- read.table (text = " Id   san1    san2    san3
1   A   A   A
2   A   A   A
3   A   M   M
4   M   A   A
", header = TRUE)
df %>%
    pivot_longer(-Id) %>%
    pivot_wider(names_from = Id, values_from = value) %>%
    rename(San = name) %>%
    bind_cols(map_dfr(df, table)[-1, -c(1:nrow(df))])

Results

# A tibble: 3 x 7
  San   `1`   `2`   `3`   `4`   A       M      
  <chr> <chr> <chr> <chr> <chr> <table> <table>
1 san1  A     A     A     M     3       1      
2 san2  A     A     M     A     3       1      
3 san3  A     A     M     A     3       1

More interesting example

Adding pesky Z value.

df_two <- df
df_two[2,3] <- "Z"

sum_those_letters <- function(.data, nice_id_col = Id, pretty_name = "San") {
    .data %>%
        pivot_longer(-{{nice_id_col}}) %>%
        pivot_wider(names_from = {{nice_id_col}}, values_from = value) %>%
        rename(pretty_name = name) %>%
        bind_cols(map_dfr(df_two, table)[-1, -c(1:nrow(.data))])
}

Results

sum_those_letters(df_two, Id)

# A tibble: 3 x 8
  San   `1`   `2`   `3`   `4`   A       M       Z      
  <chr> <chr> <chr> <chr> <chr> <table> <table> <table>
1 san1  A     A     A     M     3       1       NA     
2 san2  A     Z     M     A     2       1        1     
3 san3  A     A     M     A     3       1       NA

edited Nov 10, 2020 at 21:43

answered Nov 10, 2020 at 21:30

Konrad

18.8k17 gold badges117 silver badges186 bronze badges

2 Comments

user330 Over a year ago

Sorry, it does not work for my data, I did not get sums in my cloumns

Konrad Over a year ago

@user330 I've included the data, it's from your post.

Collectives™ on Stack Overflow

Creating multiple columns by computation in R

5 Answers 5

Comments

Comments

6 Comments

1 Comment

Results

More interesting example

Results

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

6 Comments

1 Comment

Results

More interesting example

Results

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related