CODEWITHSUNDEEP
r
user330
user330

Reputation: 1270

Creating multiple columns by computation in R

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.

Upvotes: 1

Views: 56

Answers (5)

Konrad
Konrad

Reputation: 18585

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

Upvotes: 0

Onyambu
Onyambu

Reputation: 79208

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

Upvotes: 3

Matt
Matt

Reputation: 7385

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

Upvotes: 3

akrun
akrun

Reputation: 887028

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

Upvotes: 2

Dubukay
Dubukay

Reputation: 2071

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)

enter image description here

Upvotes: 0

Related Questions