0

beginner and while i have attempted to search for an answer to this problem none seem to offer the solution that applys. it might be a simple one but i seem not to hack it. i have this data frame 

df <- data.frame(FROM = c("A","A","A","B","D","C","A","D"), 
                 TO = c("B","C","D","A","C","A","B","C"))

I would like to create a new data frame with an extra variable call it "FREQ" with all the unique values of "FROM" and "TO" Such that the new data set Looks like this. I would appreciate some assistance.

df2 <- data.frame(FROM = c("A","A","A","B","D","C"),
                  TO = c("B","C","D","A","C","A"),
                  FREQ = c(2,1,1,1,2,1))
Psidom
  • 209,562
  • 33
  • 339
  • 356
RLearner
  • 53
  • 1
  • 1
  • 7

2 Answers2

1

If you are using dplyr package, you can use count, which is a short cut for group_by(FROM, TO) %>% summarise(n = n()) and count the number of rows for each group:

library(dplyr)
df %>% count(FROM, TO)

#Source: local data frame [6 x 3]
#Groups: FROM [?]

#    FROM     TO     n
#  <fctr> <fctr> <int>
#1      A      B     2
#2      A      C     1
#3      A      D     1
#4      B      A     1
#5      C      A     1
#6      D      C     2
Psidom
  • 209,562
  • 33
  • 339
  • 356
1

We can use data.table. Convert the 'data.frame' to 'data.table' (setDT(df)), grouped by 'FROM', 'TO', we get the number of elements in each group (.N)

library(data.table)
setDT(df)[, .(FREQ = .N) ,.(FROM, TO)]
#   FROM TO FREQ
#1:    A  B    2
#2:    A  C    1
#3:    A  D    1
#4:    B  A    1
#5:    D  C    2
#6:    C  A    1

Another option is tally() from dplyr

library(dplyr)
df %>%
   group_by(FROM, TO) %>%
   tally()
#    FROM     TO     n
#   <fctr> <fctr> <int>
#1      A      B     2
#2      A      C     1
#3      A      D     1
#4      B      A     1
#5      C      A     1
#6      D      C     2

Or using table from base R, we just get the frequency of the dataset, convert to data.frame and remove the 0 elements in 'Freq' with subset.

subset(as.data.frame(table(df)), Freq !=0)
akrun
  • 874,273
  • 37
  • 540
  • 662
  • thanks i had got to the point of unto when i forgot had not subset the Data set after adding the subset to my code i got the result. However i have more than one method to it. this code worked for me. ` df3 <- subset(as.data.frame(with(df, table(FROM, TO))), Freq !=0) ` – RLearner Aug 18 '16 at 09:59
  • **Thanks** for the code, i forgot to subset the Data on my trial code . After adding the subset to my code i got the result. However you gave more than one method to my task. this code also worked for me too. ` df3 <- subset(as.data.frame(with(df, table(FROM, TO))), Freq !=0) ` – RLearner Aug 18 '16 at 10:08