CODEWITHSUNDEEP
rdplyrtidyr
csrvermaak
csrvermaak

Reputation: 298

How to gather then mutate a new column then spread to wide format again

Using tidyr/dplyr, I have some factor columns which I'd like to Z-score, and then mutate an average Z-score, whilst retaining the original data for reference.

I'd like to avoid using a for loop in tidyr/dplyr, thus I'm gathering my data and performing my calculation (Z-score) on a single column. However, I'm struggling with restoring the wide format.

Here is a MWE:

library(dplyr)
library(tidyr)

# Original Data
dfData <- data.frame(
  Name = c("Steve","Jwan","Ashley"),
  A = c(10,20,12),
  B = c(0.2,0.3,0.5)
) %>% tbl_df() 

# Gather to Z-score
dfLong <- dfData %>% gather("Factor","Value",A:B) %>% 
  mutate(FactorZ = paste0("Z_",Factor)) %>% 
  group_by(Factor) %>% 
  mutate(ValueZ = (Value - mean(Value,na.rm = TRUE))/sd(Value,na.rm = TRUE))

# Now go wide to do some mutations (eg Z)Avg = (Z_A + Z_B)/2)

# This does not work
dfWide <- dfLong %>% 
  spread(Factor,Value) %>%
  spread(FactorZ,ValueZ)%>% 
  mutate(Z_Avg = (Z_A+Z_B)/2)


# This is the desired result
dfDesired <- dfData %>% mutate(Z_A = (A - mean(A,na.rm = TRUE))/sd(A,na.rm = TRUE)) %>% mutate(Z_B = (B - mean(B,na.rm = TRUE))/sd(B,na.rm = TRUE)) %>% 
                    mutate(Z_Avg = (Z_A+Z_B)/2)

Thanks for any help/input!

Upvotes: 3

Views: 904

Answers (4)

biomiha
biomiha

Reputation: 1422

I would just do it all in wide format. No need to keep switching between the long and wide formats.

dfData %>% 
 mutate(Z_A=(A-mean(unlist(dfData$A)))/sd(unlist(dfData$A)),
        Z_B=(B-mean(unlist(dfData$B)))/sd(unlist(dfData$B))) %>% 
 mutate(Z_AVG=(Z_A+Z_B)/2)

Upvotes: 0

Sven Hohenstein
Sven Hohenstein

Reputation: 81683

Here is one approach with long and wide format. For z-transformation, you can use the base function scale. Furthermore, this approach includes a join to combine the original data frame and the one including the new values.

dfLong <- dfData %>%
  gather(Factor, Value, A:B) %>%
  group_by(Factor) %>%
  mutate(ValueZ = scale(Value))

#     Name Factor Value     ValueZ
#   <fctr>  <chr> <dbl>      <dbl>
# 1  Steve      A  10.0 -0.7559289
# 2   Jwan      A  20.0  1.1338934
# 3 Ashley      A  12.0 -0.3779645
# 4  Steve      B   0.2 -0.8728716
# 5   Jwan      B   0.3 -0.2182179
# 6 Ashley      B   0.5  1.0910895   


dfWide <- dfData %>% inner_join(dfLong %>% 
                                  ungroup %>%
                                  select(-Value) %>%
                                  mutate(Factor = paste0("Z_", Factor)) %>%
                                  spread(Factor, ValueZ) %>%
                                  mutate(Z_Avg = (Z_A + Z_B) / 2))

#     Name     A     B        Z_A        Z_B      Z_Avg
#   <fctr> <dbl> <dbl>      <dbl>      <dbl>      <dbl>
# 1  Steve    10   0.2 -0.7559289 -0.8728716 -0.8144003
# 2   Jwan    20   0.3  1.1338934 -0.2182179  0.4578378
# 3 Ashley    12   0.5 -0.3779645  1.0910895  0.3565625

Upvotes: 1

Sotos
Sotos

Reputation: 51582

Another approach using dplyr (version 0.5.0)

library(dplyr)

dfData  %>% 
   mutate_each(funs(Z = scale(.)), -Name) %>% 
   mutate(Z_Avg = (A_Z+B_Z)/2)

Upvotes: 3

Adam Quek
Adam Quek

Reputation: 7153

means <-function(x)mean(x, na.rm=T)
dfWide %>% group_by(Name) %>% summarise_each(funs(means)) %>% mutate(Z_Avg = (Z_A + Z_B)/2)

# A tibble: 3 x 6
    Name     A     B        Z_A        Z_B      Z_Avg
   <chr> <dbl> <dbl>      <dbl>      <dbl>      <dbl>
1 Ashley    12   0.5 -0.3779645  1.0910895  0.3565625
2   Jwan    20   0.3  1.1338934 -0.2182179  0.4578378
3  Steve    10   0.2 -0.7559289 -0.8728716 -0.8144003

Upvotes: 2

Related Questions