Extracting data from a data frame with conditions

Question

Given the first data frame consisting of 4 columns

libId    studId    year    freq
 1000      3       2002     3
 1000      34      2002     2
 1000      52      2004     9
 1001      17      2003     5
 1001      34      2003     1
.
.
.
.

If I were to extract only data with maximum frequency based on year for each libId this would be the desired output.

 libId    studId    year    freq
 1000      3       2002     3
 1000      52      2004     9
 1001      17      2003     5
.
.
.
.

So far I've tried

data2<- by(data1, c(data1[c(1,2,3)]), function(df) max(df$Freq))

data2 <- as.data.frame(as.table(data2))

But my output is incorrect:

libId    studId    year    freq
 1000      3       2002     3
 1000      34      2002     2
 1000      52      2004     9
 1001      17      2003     5
 1001      34      2003     1
.
.
.
.

Answer 1

There is a good summary on StackOverflow from the user EDi that I'm referring to. Basically, what you want to do is group the values by year and then select the maximum frequency of each group:

# with dplyr
require(dplyr)
df %>% group_by(year) %>% summarise(freq = max(freq))

# data.table
require(data.table)
dt <- data.table(df)
dt[ , max(freq), by = year]

Check out the linked answer for further possibilities.

Answer 2

To accomplish the task, first attach data stored in a df object, then use aggregate function:

attach(df)    
aggregate(freq ~ year, FUN=max)

which outputs:

  year freq
1 2002    3
2 2003    5
3 2004    9

But if you want to show complete data frame without sorting, you must do an index vector like:

ind <- sort(aggregate(freq ~ year, FUN=function(x) {which(df$freq==max(x))})$freq)

Next:

df[ind,]

Output

  libId studId year freq
1  1000      3 2002    3
3  1000     52 2004    9
4  1001     17 2003    5

Answer 3

You can try using subset function. Below is an example.

output_df<- subset(input_df, freq>= 3)