Modying R data frame based on two columns

Question

I am attempting to modify the following R data frame:

Column1         Column2            Value1            Value2
Parent1         Child1             3                 12
Parent1         Child2             4                 12
Parent1         Child3             5                 12
Parent2         Child4             2                 9
Parent2         Child5             6                 9
Parent2         Child6             1                 9

I would like to place the 'Parent' entries above the 'Child' entries, as well as move the values from 'Value2' to 'Value1'. The new data frame would like this:

Column2         Value1            
Parent1         12
   Child1       3                
   Child2       4                
   Child3       5                 
Parent2         9
   Child4       2                 
   Child5       6                 
   Child6       1          

Can this be accomplished using dplyr? Also, is there a way add any additional white space to the 'Child' entries?

Thanks for any insight.

Answer 1

Prepare data

library(tidyverse)
data <- read_delim(
    "Column1         Column2            Value1            Value2
Parent1         Child1             3                 12
Parent1         Child2             4                 12
Parent1         Child3             5                 12
Parent2         Child4             2                 9
Parent2         Child5             6                 9
Parent2         Child6             1                 9",delim = " "
) %>%
    mutate_all(~str_remove_all(.x," "))
colnames(data) <- str_remove_all(colnames(data)," ")

use tidyr::nest() to "clean" data so we can iterate the data frame by rows.

nested_data <- data %>%
    group_by(Column1,Value2) %>%
    nest()

> nested_data
# A tibble: 2 x 3
  Column1 Value2 data            
  <chr>   <chr>  <list>          
1 Parent1 12     <tibble [3 x 2]>
2 Parent2 9      <tibble [3 x 2]>

Then construct the desired output using pmap_df().

pmap_df(nested_data,function(...){
    values = list(...)
    bind_rows(
        tibble(
            Column2 = values$Column1,
            Value1 = values$Value2
        )
        ,
        values$data %>%
            mutate(Column2 = paste0("  ",Column2)) # add white space
    )
})

# A tibble: 8 x 2
  Column2    Value1
  <chr>      <chr> 
1 Parent1    12    
2 "  Child1" 3     
3 "  Child2" 4     
4 "  Child3" 5     
5 Parent2    9     
6 "  Child4" 2     
7 "  Child5" 6     
8 "  Child6" 1 

Answer 2

Here's another way in dplyr. The group column can be dropped and arrange logic can be made more robust if needed. -

df %>% 
  mutate(group = group_indices(., Column1)) %>%
  {bind_rows(
    distinct(., Column = Column1, Value = Value2, group),
    select(., Column = Column2, Value = Value1, group) %>% 
      mutate(Column = paste0("   ", Column))
  )} %>% 
  arrange(group, desc(Column))

# A tibble: 8 x 3
  Column      Value group
  <chr>       <int> <int>
1 Parent1        12     1
2 "   Child3"     5     1
3 "   Child2"     4     1
4 "   Child1"     3     1
5 Parent2         9     2
6 "   Child6"     1     2
7 "   Child5"     6     2
8 "   Child4"     2     2

Data -

df <- structure(list(Column1 = c("Parent1", "Parent1", "Parent1", "Parent2", 
"Parent2", "Parent2"), Column2 = c("Child1", "Child2", "Child3", 
"Child4", "Child5", "Child6"), Value1 = c(3L, 4L, 5L, 2L, 6L, 
1L), Value2 = c(12L, 12L, 12L, 9L, 9L, 9L)), .Names = c("Column1", 
"Column2", "Value1", "Value2"), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))