How do I join two table on a column and group by on another column-CodePudding

Lets say I have two franchise of dance schools.

I have two tables. First table tells about students roll no. every

Table 1 =

Roll No.    Center ID   Name    Date
1   A   Anna    10/10/2020
1   A   Anna    11/10/2020
1   B   Anna    12/10/2020
2   A   Bella   12/10/2020
2   B   Bella   13/10/2020
3   A   Catty   10/10/2020

Table 2 =

Roll no.    Center ID   Report
1   A   Did well
1   A   Sick 
1   B   Needs more twist
2   A   Practice required
2   B   Did well
3   A   Needs more practice

Result table expected: I want in the result it should pick Center id as A only but Report should be from both the centers

Roll no.    Center ID   Report  Name
1   A   Did well ,Sick ,Needs more twist    Anna
2   A   Practice required,Did well  Bella
3   A   Needs more practice Catty

Could someone pls help.

CodePudding user response：

By dplyr,

t1 %>%
  mutate(Report = t2$Report) %>%
  group_by(Roll_No.) %>%
  summarise(Center_ID = "A",
    Report = paste0(Report, collapse = ', '),
    Name = unique(Name)
    )

  Roll_No. Center_ID Report                           Name 
     <int> <chr>     <chr>                            <chr>
1        1 A         Did well, Sick, Needs more twist Anna 
2        2 A         Practice required, Did well      Bella
3        3 A         Needs more practice              Catty

CodePudding user response：

library(tidyverse)

a <- tribble(
  ~Roll, ~Center, ~Name, ~Date,
  1, "A", "Anna", "10/10/2020",
  1, "B", "Anna", "12/10/2020",
  3, "A", "Catty", "10/10/2020"
)

b <- tribble(
  ~Roll, ~Center, ~Report,
  1, "A", "Dis well",
  1, "A", "Sick",
  1, "B", "Needs more twist",
  3, "A", "Needs more practice"
)

a %>%
  left_join(b) %>%
  group_by(Roll, Center) %>%
  summarise(
    Report = c(Report, Name %>% unique()) %>% paste0(collapse = ",")
  )
#> Joining, by = c("Roll", "Center")
#> `summarise()` has grouped output by 'Roll'. You can override using the `.groups` argument.
#> # A tibble: 3 x 3
#> # Groups:   Roll [2]
#>    Roll Center Report                   
#>   <dbl> <chr>  <chr>                    
#> 1     1 A      Dis well,Sick,Anna       
#> 2     1 B      Needs more twist,Anna    
#> 3     3 A      Needs more practice,Catty

^{Created on 2021-10-05 by the reprex package (v2.0.0)}

CodePudding user response：

With dplyr package:

library(dplyr)
cbind(df1, Report=df2$Report) %>% group_by(Name) %>%
  summarize(RollNo=first(RollNo), CenterID=first(CenterID), Report=paste(toString(Report), first(Name), collapse='  '))

Output:

  Name  RollNo CenterID Report                               
  <chr>  <dbl> <chr>    <chr>                                
1 Anna       1 A        Did well, Sick, Needs more twist Anna
2 Bella      2 A        Practice Required, Did well Bella    
3 Cathy      3 A        Needs more practice Cathy

CodePudding user response：

update: With the hint of @Park many thanks!: Logic:

left_join by RollNo.
filter, group_by and summarise

library(dplyr)
table1 %>% 
  left_join(table2, by=c("RollNo."="Rollno.")) %>% 
  filter(CenterID.x== "A") %>% 
  group_by(RollNo., CenterID=CenterID.x, Name) %>% 
  summarise(Report = paste(unique(Report), collapse = ", "))

output:

  RollNo. CenterID Name  Report                          
    <dbl> <chr>    <chr> <chr>                           
1       1 A        Anna  Did well, Sick, Needs more twist
2       2 A        Bella Practice required, Did well     
3       3 A        Catty Needs more practice

First answer: We could try this: It is depending on whether Date should be considered or not, so you may modify the code:


table1 %>% 
  left_join(table2, by=c("CenterID", "RollNo."="Rollno.")) %>% 
  filter(CenterID == "A") %>% 
  group_by(RollNo., CenterID, Name) %>% 
  summarise(Report = paste(unique(Report), collapse = ", "))

    RollNo. CenterID Name  Report             
    <dbl> <chr>    <chr> <chr>              
1       1 A        Anna  Did well, Sick     
2       2 A        Bella Practice required  
3       3 A        Catty Needs more practice