CODEWITHSUNDEEP
rexceldata-manipulation
Doniu
Doniu

Reputation: 115

R - function like COUNTIFS

I'm looking for R's version of Excel's "COUNTIFS" function

Scenario:

I have two columns full of values, I want to add a column that would store count of records that have same values in Col1 and Col2

It's like having to primary key split in two columns I guess

Col1 Col2 Col3 ColNew
A1   B1   EPP  2
A1   B2   EPP  1
A1   B1   EPP  2

In Excel i got it to work using below formula

=COUNTIFS(C:C,$C2,A:A,$A2,E:E,$E$2)>1

But it actually returned TRUE or FALSE instead of numbers

Any thoughts?

Upvotes: 2

Views: 5038

Answers (5)

moodymudskipper
moodymudskipper

Reputation: 47300

dplyr has a function called add_count that does just that:

library(dplyr)
df %>%
  group_by(Col1,Col2) %>%
  add_count

# # A tibble: 3 x 4
# # Groups:   Col1, Col2 [2]
#    Col1  Col2  Col3     n
#   <chr> <chr> <chr> <int>
# 1    A1    B1   EPP     2
# 2    A1    B2   EPP     1
# 3    A1    B1   EPP     2

data

df <- read.table(text="Col1 Col2 Col3
A1   B1   EPP
A1   B2   EPP
A1   B1   EPP",header=TRUE,stringsAsFactors=FALSE)

Upvotes: 3

Onyambu
Onyambu

Reputation: 79198

transform(dat,col=ave(do.call(paste,dat),Col2,Col3,FUN = length))
  Col1 Col2 Col3 col
1   A1   B1  EPP   2
2   A1   B2  EPP   1
3   A1   B1  EPP   2

Upvotes: 4

Mike H.
Mike H.

Reputation: 14360

ave might be a very useful function in this case:

df$ColNew <- ave(rep(1, nrow(df)), df$Col1, df$Col2, FUN = length)

df
#  Col1 Col2 Col3 ColNew
#1   A1   B1  EPP      2
#2   A1   B2  EPP      1
#3   A1   B1  EPP      2

Upvotes: 6

Eray Balkanli
Eray Balkanli

Reputation: 7960

Assuming your data frame is df, then please try:

library(plyr)
counts <- ddply(df, .(df$Col1, df$Col2), nrow)
names(counts) <- c("Col1", "Col2", "Freq")

Could you please try this as an alternative solution:

library(data.table)
dt <- data.table(df)
dt[, list(Freq =.N), by=list(Col1,Col2)]

According to the data provided in the question, I was expecting resultset like:

Col1 Col2 Freq
A1   B1   2
A1   B2   1

Upvotes: 1

Gregor Thomas
Gregor Thomas

Reputation: 145755

dplyr and data.table are two popular packages that make doing things "by group" very easy.

Using dplyr:

df %>% group_by(Col1, Col2) %>% mutate(ColNew = n())

Using data.table:

setDT(df)
df[, ColNew := .N, by = .(Col1, Col2)]

Upvotes: 5

Related Questions