Grouping values in a string array matching a criteria in R

user_11203572 0
Viewed 60

I have a big data frame df1 that has a column Student which has string values for students name, Grades with numeric values for the grades of an exam. Another data frame df2 that has three columns Class numeric, From To as a range for the grade and Count that summarizes how many students has a specific grade between some value for a specific class.

As an example:

Stundent <- c("Mark", "Jhon", "Stuart", "Lillie", "Carl", "Jason", "Stewart","Jack")
Grades <- c(7,9,1,6,7,6,4,8)
df1 <- data.frame(Stundent,Grades)

Class <- c(101, 101, 201, 308, 507, 201, 507, 308)
from <- c(1,6,1,1,6,6,1,6)
to <- c(5,10,5,5,10,10,5,10)
Count <- c(0,2,1,0,1,1,1,2)
df2 <- data.frame(Class,from,to,Count)
df2 <- df2[order(df2$Class),]

And what I'm expecting is to get something like this

Students <- c("","Mark, Jhon", "Stuart", "Lillie", "","Carl, Jason", "Stewart", "Jack")
df3 <- data.frame(df2, Students)
grouping dataframe r arrays string
2 Answers

It is not entirely clear. Here is a for loop version. Based on the expected output, it could be 'Stundent' from the 'df1' are allocated to each row of 'df2' 'Students' column by comparing the 'Grades' against the 'from', 'to' in 'df2' and the number of 'Stundent' selected are limited by the 'Count' value. Once a 'Stundent' is selected, it is not repeated again

tmpdat <- df1
tmpdat$Stundent <- as.character(tmpdat$Stundent)

df2$Students <- ""
for(i in seq_len(nrow(df2))) {

       if(df2$Count[i] >0 ) {
        st1 <- head(tmpdat$Stundent[tmpdat$Grades >= df2$from[i] & 
                                    tmpdat$Grades <= df2$to[i]], df2$Count[i])
        tmpdat <- tmpdat[!tmpdat$Stundent %in% st1, ]
        df2$Students[i] <- toString(st1) 

      }

  }




df2
#  Class from to Count    Students
#1   101    1  5     0            
#2   101    6 10     2  Mark, Jhon
#3   201    1  5     1      Stuart
#6   201    6 10     1      Lillie
#4   308    1  5     0            
#8   308    6 10     2 Carl, Jason
#5   507    6 10     1        Jack
#7   507    1  5     1     Stewart

In the expected output, the Jack and 'Stewart for 507 are switched. From 'df1', Jack's Grades is 8 and Stewart 4

user_37322710

I am assuming that there should be another column in df1 which would be Class identifying which student is in which Class because there is no information to match the data. If my assumption is not correct, let me know and I'll delete the answer. 

#adding class column
df1$Class <- c(101, 101, 201, 201, 308, 308, 507, 507)

We can left_join df1 and df2 by Class and create comma-separated values of Stundent which are in range. 

library(dplyr)

df1 %>%
  left_join(df2, by = 'Class') %>%
  group_by(Class, from, to) %>%
  summarise(Stundent = toString(Stundent[Grades >= from & Grades <= to]))


#  Class  from    to Stundent     
#  <dbl> <dbl> <dbl> <chr>        
#1   101     1     5 ""           
#2   101     6    10 "Mark, Jhon" 
#3   201     1     5 "Stuart"     
#4   201     6    10 "Lillie"     
#5   308     1     5 ""           
#6   308     6    10 "Carl, Jason"
#7   507     1     5 "Stewart"    
#8   507     6    10 "Jack"
user_39629140
Related