CODEWITHSUNDEEP
rfrequency
signalstone
signalstone

Reputation: 47

frequency count with adding the lowest time in R

group/Time/Boardstation/
1     0511      1                
1     0513      1
1     0515      1
1     0520      2
1     0525      3
1     0526      3
1     0540      5  
2     0511      1                
2     0513      1
2     0515      1
2     0520      2
2     0525      3
2     0526      3
2     0540      5

Currently the data set is like above.

group/Boardstation/Frequency
  1       1           3     
  1       2           1     
  1       3           2     
  1       4           0     
  1       5           1     
  2       1           3     
  2       2           1     
  2       3           2     
  2       4           0     
  2       5           1

I have made the frequency count and added the stations with are not in the dataset

Now I would like to make frequency count while adding the lowest time to the the second data as you can see below

group/Boardstation/Frequency/Time
  1        1           3     0511
  1        2           1     0520
  1        3           2     0525
  1        4           0     null
  1        5           1     0540
  2        1           3     0511
  2        2           1     0520
  2        3           2     0525
  2        4           0     null
  2        5           1     0540

currently i have managed to make the boardstation and frequency and count null as well but adding the time on a new column is difficult.

Any help would be helpful thanks!

Upvotes: 0

Views: 50

Answers (2)

Ape
Ape

Reputation: 1169

One option is to just join the two data frames, order the result by time and then remove rows duplicated in both group and boardstation:

Data:

time_df <- data.frame(t(matrix(c(1,0511,1,                
    1,0513,1,1,0515,1,1,0520,2,1,0525,3,1,0526,3,1,0540,5,2,0511,1,
    2,0513,1,2,0515,1,2,0520,2,2,0525,3,2,0526,3,2,0540,5), nrow = 3)))     
colnames(time_df) <- c("group","Time","Boardstation")

freq_df <- data.frame(t(matrix(c(1,1,3,     
    1,2,1,1,3,2,1,4,0,1,5,1,2,1,3,2,2,1,2,3,2,2,4,0,2,5,1), nrow = 3))) 
# alternative
# freq_df <- as.data.frame(with(time_df, table(group, factor(Boardstation,levels = 1:5))))
colnames(freq_df) <- c("group","Boardstation","Frequency")

Solution:

join_df <- merge(freq_df, time_df, by = c("group", "Boardstation"), all.x = TRUE)
join_df <- join_df[with(join_df, order(group, Boardstation, Time)),]
final_df <- join_df[!duplicated(join_df[,1:2]),]

     group Boardstation Frequency Time
1      1            1         3  511
4      1            2         1  520
5      1            3         2  525
7      1            4         0   NA
8      1            5         1  540
9      2            1         3  511
12     2            2         1  520
13     2            3         2  525
15     2            4         0   NA
16     2            5         1  540

Upvotes: 1

AntoniosK
AntoniosK

Reputation: 16121

Here's a dplyr solution:

library(dplyr)

df = read.table(text = "
group Time Boardstation
1     0511      1                
1     0513      1
1     0515      1
1     0520      2
1     0525      3
1     0526      3
1     0540      5  
2     0511      1                
2     0513      1
2     0515      1
2     0520      2
2     0525      3
2     0526      3
2     0540      5
", header=T)


expand.grid(group = seq(min(df$group), 
                        max(df$group)),                     # get all possible combinations of group and Boardstation as a dataframe
            Boardstation = seq(min(df$Boardstation), 
                               max(df$Boardstation))) %>%
  left_join(df, by=c("group", "Boardstation")) %>%          # join your original dataset
  mutate(counter = !is.na(Time)) %>%                        # flag when there's a Time value
  group_by(group, Boardstation) %>%                         # for each combination of group and Boardstation
  summarise(Freq = sum(counter),                            # count it only if there's a Time value
            Time = min(Time)) %>%                           # get the minimum Time value
  ungroup()                                                 # forget the grouping

# # A tibble: 10 x 4
#   group Boardstation  Freq  Time
#   <int>        <int> <int> <dbl>
# 1     1            1     3   511
# 2     1            2     1   520
# 3     1            3     2   525
# 4     1            4     0    NA
# 5     1            5     1   540
# 6     2            1     3   511
# 7     2            2     1   520
# 8     2            3     2   525
# 9     2            4     0    NA
# 10    2            5     1   540

This will work if Time is a numeric, or a character variable. If it is a factor variable you have to change it to a character variable.

Upvotes: 1

Related Questions