CODEWITHSUNDEEP
rdplyrdatatable
chippycentra
chippycentra

Reputation: 3432

Merge two table objects in order to create a new dataframe in r

I have 2 table objects such as :

type1:

   0    1    2    3   12   20 
1318  841    4    1    1    1


type1<- structure(c(`0` = 1318L, `1` = 841L, `2` = 4L, `3` = 1L, `12` = 1L, 
`20` = 1L), .Dim = 6L, .Dimnames = structure(list(c("0", "1", 
"2", "3", "12", "20")), .Names = ""), class = "table")

and

type2:

  0   1   2  12  15 
 93 178   1   4   1



type2<-structure(c(`0` = 93L, `1` = 178L, `2` = 1L, `12` = 4L, `15` = 1L
), .Dim = 5L, .Dimnames = structure(list(c("0", "1", "2", "12", 
"15")), .Names = ""), class = "table")

and I would like to merge them in order to get a dataframe such as :

x  y2     y
1  type1  841
2  type1  4
3  type1  1
4  type1  0
5+ type1  2
1  type2  178
2  type2  1
3  type2  0
4  type2  0
5+ type2  5

Where I sum all columns in type1 or type2 >= 5 and add this count in the 5+ row.

Does someone have an idea please?

Upvotes: 1

Views: 124

Answers (2)

iago
iago

Reputation: 3256

I would do this (binding and then ...):

library(dplyr)
rbind(data.frame(y = type1, y2 = "type1"), data.frame(y = type2, y2 = "type2")) %>% 
  mutate(x = ifelse(as.numeric(as.character(y.Var1)) < 5, as.character(y.Var1), "5+")) %>% 
  group_by(x,y2) %>% 
  summarise(y = sum(y.Freq), .groups = "drop") %>% 
  arrange(y2, desc(y)) 
# A tibble: 9 × 3
  x     y2        y
  <chr> <chr> <int>
1 0     type1  1318
2 1     type1   841
3 2     type1     4
4 5+    type1     2
5 3     type1     1
6 1     type2   178
7 0     type2    93
8 5+    type2     5
9 2     type2     1

Upvotes: 1

det
det

Reputation: 5232

library(tidyverse)

list(type1 = type1, type2 = type2) %>%
  map(~tibble(x = as.integer(names(.x)), y = .x)) %>%
  map(~complete(.x, x = 1:5, fill = list(y = 0))) %>%
  imap_dfr(~
        filter(.x, x > 0) %>%
          group_by(x = ifelse(x >= 5, "5+", x)) %>% 
          summarise(y = sum(y)) %>%
          mutate(y2 = .y)
        
  ) %>%
  select(x, y2, y)

output:

   x     y2        y
   <chr> <chr> <dbl>
 1 1     type1   841
 2 2     type1     4
 3 3     type1     1
 4 4     type1     0
 5 5+    type1     2
 6 1     type2   178
 7 2     type2     1
 8 3     type2     0
 9 4     type2     0
10 5+    type2     5

Upvotes: 1

Related Questions