CODEWITHSUNDEEP
rdplyrlapply
SCDCE
SCDCE

Reputation: 1643

How to functionally spread and coalesce data

Just a simple little example, how might I go about renaming a column based off of a group number?

dat_func <- function(i){

dat %>% filter(group_no == i) %>% mutate(WHERE[i] = WHERE) %>% select(-WHERE)

}

lapply(1:max(dat$group_no), function(i) dat_func(i))

Taking a sample input such as this:

> data.frame(uniqueID= c(123,234,345,345,456),group_no=c(1,1,1,2,1), WHERE=rep("test",5))
  uniqueID group_no WHERE
1      123        1  test
2      234        1  test
3      345        1  test
4      345        2  test
5      456        1  test

And producing this:

> data.frame(uniqueID=c(123,234,345,456),WHERE1=rep("test",4), WHERE2=c(NA,NA,"test",NA))
  uniqueID WHERE1 WHERE2
1      123  test   <NA>
2      234  test   <NA>
3      345  test   test
4      456  test   <NA>

FINAL SOLUTION:

My original problem was a little more complicated but here's the solution I came up with:

library(tidyverse)
library(dplyr)
library(magrittr)

Had some duplicate data that needed to be coalesced along with repeating group assignment.

dat <- data.frame(uniqueID= c(123,234,345,456,456),
           TEST1=c(1,1,1,NA,1),
           TEST2=c(1,1,1,1,NA),
           WHERE=rep("test",5))

Generate the repeating group value. 

dat %<>% mutate(DUPE = as.numeric(duplicated(uniqueID))+1)

Function to create the repeating group variables: 

rep_group <- function(i) {

  dat %>%
    mutate(DUPE = paste0(i, DUPE)) %>%
    spread(key = DUPE, value = i)

}

Function to coalesce the repeating values and NAs.

coalesce_by_column <- function(df) {
  return(dplyr::coalesce(!!! as.list(df)))
}

List of repeating group variables

rep_list <- c("WHERE")

Applying the repeating group function and coalescing everything:

lapply(rep_list, function(i) rep_group(i)) %>% 
  as.data.frame() %>% 
  select(-matches("[.]")) %>% 
  group_by(uniqueID) %>% 
  summarise_all(coalesce_by_column)

To summarize:

These steps take data that looks like this:

  uniqueID TEST1 TEST2 WHERE 
1      123     1     1  test    
2      234     1     1  test    
3      345     1     1  test    
4      456    NA     1  test    
5      456     1    NA  test

And generates data like this:

  uniqueID TEST1 TEST2 WHERE1 WHERE2
     <dbl> <dbl> <dbl> <fct>  <fct> 
1      123     1     1 test   NA    
2      234     1     1 test   NA    
3      345     1     1 test   NA    
4      456     1     1 test   test

Upvotes: 3

Views: 287

Answers (1)

Andrew
Andrew

Reputation: 5138

Seems like you want group_no to go from long to wide format. There are several ways to do this in R. Here's a solution using tidyverse (more specifically, tidyr::spread). 

library(tidyverse)

df1 <- data.frame(uniqueID= c(123,234,345,345,456),group_no=c(1,1,1,2,1), WHERE=rep("test",5))

# Long to wide
df1 <- df1 %>%
  spread(key = group_no, value = WHERE)

# Renaming columns
names(df1)[-1] <- paste0("WHERE", names(df1)[-1])
df1

  uniqueID WHERE1 WHERE2
1      123   test   <NA>
2      234   test   <NA>
3      345   test   test
4      456   test   <NA>

Or, you add the "column name" to group_no ahead of time with something like this:

df1 %>%
  mutate(group_no = paste0("WHERE", group_no)) %>%
  spread(key = group_no, value = WHERE)

  uniqueID WHERE1 WHERE2
1      123   test   <NA>
2      234   test   <NA>
3      345   test   test
4      456   test   <NA>

Upvotes: 3

Related Questions