CODEWITHSUNDEEP
rdplyr
Ed_Gravy
Ed_Gravy

Reputation: 2033

Extract data based on a time series column in R

I have an annual daily timeseries pixel data in a data frame in such a way that each date occurs multiple times for each of the pixel. Now I would like to extract/subset this data based on a set of dates stored in another data frame. How can I do this in R using dplyr?

Sample data

X  Y  T        Value
X1 Y1 1/1/2004 1
X2 Y2 1/1/2004 2
X3 Y3 1/1/2004 3
X1 Y1 1/2/2004 4
X2 Y2 1/2/2004 5
X3 Y3 1/2/2004 6
X1 Y1 1/3/2004 7
X2 Y2 1/3/2004 8
X3 Y3 1/3/2004 9

Dates of interest

1/1/2004
1/2/2004

Code

library(dplyr)

X = c("X1", "X2", "X3", "X1", "X2", "X3", "X1", "X2", "X3")
Y = c("Y1", "Y2", "Y3", "Y1", "Y2", "Y3", "Y1", "Y2", "Y3")
T = c("1/1/2004", "1/2/2004", "1/3/2004", "1/1/2004", "1/2/2004", "1/3/2004","1/1/2004", "1/2/2004", "1/3/2004")
Value = c("1", "2", "3", "4", "5", "6", "7", "8", "9")
df = data.frame(X, Y, T, Value)

# Desired dates
TS = read.csv("TS.csv")
TS
"1/1/2004", "1/2/2004"
#stuck...___

Upvotes: 0

Views: 441

Answers (2)

U13-Forward
U13-Forward

Reputation: 71620

Base R:

> df[df$T %in% TS,]
   X  Y        T Value
1 X1 Y1 1/1/2004     1
2 X2 Y2 1/2/2004     2
4 X1 Y1 1/1/2004     4
5 X2 Y2 1/2/2004     5
7 X1 Y1 1/1/2004     7
8 X2 Y2 1/2/2004     8
>

If TS is "1/1/2004, 1/2/2004", use stringr:

> df[df$T %in% stringr::str_split(TS, ", ", simplify=TRUE),]
   X  Y        T Value
1 X1 Y1 1/1/2004     1
2 X2 Y2 1/2/2004     2
4 X1 Y1 1/1/2004     4
5 X2 Y2 1/2/2004     5
7 X1 Y1 1/1/2004     7
8 X2 Y2 1/2/2004     8
>

Upvotes: 1

Kra.P
Kra.P

Reputation: 15153

If your TS is TS = c("1/1/2004", "1/2/2004"), simply using filter,

library(dplyr)
df %>%
  filter(T %in% TS)

   X  Y        T Value
1 X1 Y1 1/1/2004     1
2 X2 Y2 1/2/2004     2
3 X1 Y1 1/1/2004     4
4 X2 Y2 1/2/2004     5
5 X1 Y1 1/1/2004     7
6 X2 Y2 1/2/2004     8

if your TS is TS = ("1/1/2004, 1/2/2004")

library(stringr)

df %>%
  filter(T %in% str_split(gsub("\\s+", "", TS), ",", simplify = TRUE))

Upvotes: 1

Related Questions