CODEWITHSUNDEEP
r
Jstation
Jstation

Reputation: 407

How do I merge rows while also moving columns to the merged row?

Hello everyone and thanks for reading my question.

I have the following in R:

**type, status, count**
human, living, 36
human, living, 57
human, dead, 43
mouse, living, 4
mouse, dead 8

What I want to do is merge the rows based on 'type' (so 'type' would be exclusive) and then move the contents of 'status' and 'count' to the merged row and add some symbols as shown below:

**type, status, count**
human, living = "36, 57", dead = "43"
mouse, living = "4", dead = "8"

I did manage to merge the rows in R (sort of) but I cannot figure out how to move the status and count to the merged row and lay them out as shown.

I don't have to use R but I thought R was the most suitable way of doing this but I could use anything to get the job done. Any help would be greatly appreciated.

Many thanks.

Edit: This is the final solution which worked great (thanks loads to gersht):

rm(list=ls()); 

library(tidyr)
library(dplyr)

df <- read.table("D:/test.csv", header = TRUE, sep=",")

df <- df %>%
    group_by(type, status) %>% 
    mutate(count = paste(count, collapse = ", ")) %>% 
    ungroup() %>% 
    distinct() %>% 
    spread(status, count) %>% 
    mutate(dead = paste("dead = ", dead),
           living = paste("living = ", living))


write.table(df, col.names = FALSE)

Upvotes: 0

Views: 107

Answers (1)

user10191355
user10191355

Reputation:

This will return a dataframe with correct values, more or less. You can change column order and column names as needed:

library(tidyr)
library(dplyr)

df %>% 
    group_by(type, status) %>% 
    mutate(count = paste(count, collapse = ", ")) %>% 
    ungroup() %>% 
    distinct() %>% 
    spread(status, count) %>% 
    mutate(dead = paste("dead = ", dead),
           living = paste("living = ", living))

#### OUTPUT ####

# A tibble: 2 x 3
  type  dead       living          
  <chr> <chr>      <chr>           
1 human dead =  43 living =  36, 57
2 mouse dead =  8  living =  4

I've simply grouped by type and status so I can then collapse the values of count into a single string using mutate(). I use ungroup() as good practice, but it isn't strictly necessary.

This creates some duplicates, which I remove with distinct(). I then use the spread() function to move living and dead to their own columns, and then I use mutate again to add strings "living = " and "dead = " to their respective columns.

data

structure(list(type = c("human", "human", "human", "mouse", "mouse"
), status = c("living", "living", "dead", "living", "dead"), 
    count = c(36, 57, 43, 4, 8)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

Upvotes: 1

Related Questions