CODEWITHSUNDEEP
r
user2614883
user2614883

Reputation: 685

Extract rows for the first occurrence of a variable in a data frame

I have a data frame with two variables, Date and Taxa and want to get the date for the first time each taxa occurs. There are 9 different dates and 40 different taxa in the data frame consisting of 172 rows, but my answer should only have 40 rows.

Taxa is a factor and Date is a date.

For example, my data frame (called 'species') is set up like this:

Date          Taxa
2013-07-12    A
2011-08-31    B
2012-09-06    C
2012-05-17    A
2013-07-12    C
2012-09-07    B

and I would be looking for an answer like this:

Date          Taxa
2012-05-17    A
2011-08-31    B
2012-09-06    C

I tried using:

t.first <-  species[unique(species$Taxa),]

and it gave me the correct number of rows but there were Taxa repeated. If I just use unique(species$Taxa) it appears to give me the right answer, but then I don't know the date when it first occurred.

Upvotes: 64

Views: 89505

Answers (6)

Vishal Sharma
Vishal Sharma

Reputation: 491

This is a good question. First of all, I want to highlight that the output you've mentioned is not correct as per your requirement. It should be:

Date          Taxa
2013-07-12    A
2011-08-31    B
2012-09-06    C

i.e., the first entry was not correct. Now talking about the code for this, all these are good answers but the solution I propose is more robust. To demonstrate better, I have used a new dataframe.

d <- data.frame(a = c(rep("A", 4), c(rep("B",4)), rep("C",4)), b=c(0,0,1,1,0,1,1,1,0,0,0,1))
d %>% group_by(a) %>% mutate(c = detect_index(.x = b, .f = p), d = row_number()) %>% mutate(e = ifelse(c==d,1,0)) %>% ungroup()

Keep Coding!

Upvotes: 0

s_baldur
s_baldur

Reputation: 33488

Here is a solution using data.table:

library(data.table)
setDT(species)
species[, .SD[which.min(Date)], by = Taxa]
#    Taxa       Date
# 1:    A 2012-05-17
# 2:    B 2011-08-31
# 3:    C 2012-09-06

Data:

species <- data.frame(
  Date = as.Date(c("2013-07-12", "2011-08-31", "2012-09-06", 
                   "2012-05-17", "2013-07-12", "2012-09-07")), 
  Taxa = c("A", "B", "C", "A", "C", "B")
)

Upvotes: 5

sbha
sbha

Reputation: 10422

Here is a dplyr option that is not dependent on the data being sorted in date order and accounts for ties: 

library(dplyr)
df %>% 
  mutate(Date = as.Date(Date)) %>% 
  group_by(Taxa) %>% 
  filter(Date == min(Date)) %>% 
  slice(1) %>% # takes the first occurrence if there is a tie
  ungroup()

# A tibble: 3 x 2
  Date       Taxa 
  <date>     <chr>
1 2012-05-17 A    
2 2011-08-31 B    
3 2012-09-06 C 

# sample data:
df <- read.table(text = 'Date          Taxa
                         2013-07-12    A
                         2011-08-31    B
                         2012-09-06    C
                         2012-05-17    A
                         2013-07-12    C
                         2012-09-07    B', header = TRUE, stringsAsFactors = FALSE)

And you could get the same by sorting by date as well:

df %>% 
  mutate(Date = as.Date(Date)) %>% 
  group_by(Taxa) %>% 
  arrange(Date) %>% 
  slice(1) %>% 
  ungroup()

Upvotes: 16

s_scolary
s_scolary

Reputation: 1399

This should do the trick:

# Create some dummy data:

# Create some dates 
Date=as.POSIXct(c("2013-07-12","2011-08-31","2012-09-06","2009-01-01",
                  "2012-05-17","2013-07-12","2012-09-07","2013-02-02"))

# Create unique taxa
Taxa=rep(c("A","B","C","D"),2)

# Combine the two into a dataframe
data=as.data.frame(list(Date=Date,Taxa=Taxa))

# this returns a numeric vector of the minimum dates
xx=tapply(data$Date,list(data$Taxa),min)

# And this will return a dataframe with the first occurence
# of your taxa (or variables)
as.data.frame(list(Date=as.POSIXct(xx,origin="1970-01-01"),
                   Taxa=names(xx)))

Note: You can add simplify=T in tapply to return a POSIXt object but it returns a list. More info can be found here: Unexpected behaviour of min, tapply and POSIXct/POSIXlt classes?

Upvotes: 2

Sven Hohenstein
Sven Hohenstein

Reputation: 81683

In the following command, duplicated creates a logical index for duplicated data$Taxa values. A subset of the data frame without the corresponding rows is created with:

data[!duplicated(data$Taxa), ]

The result:

        Date Taxa
1 2012-05-17    A
2 2011-08-31    B
3 2012-09-06    C

Upvotes: 21

alexwhan
alexwhan

Reputation: 16026

t.first <- species[match(unique(species$Taxa), species$Taxa),]

should give you what you're looking for. match returns indices of the first match in the compared vectors, which give you the rows you need.

Upvotes: 71

Related Questions