Nsryjdtyk

My knowledge of R and scripting in general is almost not existent. So I hope you will be patient with this basic question.

library(lubridate)

date.depature <- c("2016.06.16", "2016.11.16", "2017.01.05", "2017.01.12", "2017.02.25")

airport.departure <- c("CDG", "QNY", "QXO", "CDG", "QNY")

airport.arrival <- c("SYD", "CDG", "QNY", "SYD", "QXO")

amount <- c("1", "3", "1", "10", "5")

date.depature <- as_date(date.depature)

df <- data.frame(date.depature, airport.departure, airport.arrival, amount)



xtabs(as.integer(amount) ~ airport.arrival + airport.departure, df)

With this code we get the sum of the amount as a matrix with the airports as row/column. Now I need just the results for

1. 2017


2. 2017.01


3. until 2017.01

Since you're already using lubridate, I'm going to show you an approach using dplyr (part of the tidyverse alongside lubridate).

The solutions all apply. filter alongside month, year and as_date functions from lubridate to create conditions to filter your data, then use the pipe %>% to pass that long to xtabs

library(dplyr)

#> 

#> Attaching package: 'dplyr'

#> The following objects are masked from 'package:stats':

#> 

#>     filter, lag

#> The following objects are masked from 'package:base':

#> 

#>     intersect, setdiff, setequal, union

library(lubridate)

#> 

#> Attaching package: 'lubridate'

#> The following object is masked from 'package:base':

#> 

#>     date



date.depature <- c("2016.06.16", "2016.11.16", "2017.01.05", "2017.01.12", "2017.02.25")

airport.departure <- c("CDG", "QNY", "QXO", "CDG", "QNY")

airport.arrival <- c("SYD", "CDG", "QNY", "SYD", "QXO")

amount <- c("1", "3", "1", "10", "5")

date.depature <- as_date(date.depature)

df <- data.frame(date.depature, airport.departure, airport.arrival, amount)



# For 2017

df %>% 

  filter(year(date.depature) == 2017) %>% 

  xtabs(as.integer(amount) ~ airport.arrival + airport.departure, .)

#>                airport.departure

#> airport.arrival CDG QNY QXO

#>             CDG   0   0   0

#>             QNY   0   0   1

#>             QXO   0   4   0

#>             SYD   2   0   0



# 2017.01

df %>% 

  filter(year(date.depature) == 2017, month(date.depature) == 1) %>% 

  xtabs(as.integer(amount) ~ airport.arrival + airport.departure, .)

#>                airport.departure

#> airport.arrival CDG QNY QXO

#>             CDG   0   0   0

#>             QNY   0   0   1

#>             QXO   0   0   0

#>             SYD   2   0   0



# until 2017.01

df %>% 

  filter(date.depature <= as_date("2017.01.01")) %>% 

  xtabs(as.integer(amount) ~ airport.arrival + airport.departure, .)

#>                airport.departure

#> airport.arrival CDG QNY QXO

#>             CDG   0   3   0

#>             QNY   0   0   0

#>             QXO   0   0   0

#>             SYD   1   0   0

^{Created on 2018-11-19 by the reprex package (v0.2.1)}

Why don't you coerce amount to class "integer" when you create df? Just get rid of the double quotes in

amount <- c("1", "3", "1", "10", "5")

or

amount <- as.integer(c("1", "3", "1", "10", "5"))

This is because as.integer(df$amount) does not return

c(1, 3, 1, 10, 5)

When you create the dataframe df that vector is coerced to class "factor" and what you now have is

as.integer(df$amount)

#[1] 1 3 1 2 4

The right way would be

as.integer(as.character(df$amount))

#[1]  1  3  1 10  5

Or more simply:

date.depature <- c("2016.06.16", "2016.11.16", "2017.01.05", "2017.01.12", "2017.02.25")

airport.departure <- c("CDG", "QNY", "QXO", "CDG", "QNY")

airport.arrival <- c("SYD", "CDG", "QNY", "SYD", "QXO")

amount <- c(1, 3, 1, 10, 5)

date.depature <- as_date(date.depature)

df <- data.frame(date.depature, airport.departure, airport.arrival, amount)

Now the question.

This is basically a subsetting problem.

Subset the data extracting the years and months you want then run the same xtabs command.

df1 <- df[year(df$date.depature) == 2017, ]

df2 <- df1[month(df1$date.depature) == 1, ]

df3 <- cbind(df[year(df$date.depature) < 2017, ], df2)

Now xtabs, with the sub-dataframes above.

xtabs(amount ~ airport.arrival + airport.departure, df1)

xtabs(amount ~ airport.arrival + airport.departure, df2)

xtabs(amount ~ airport.arrival + airport.departure, df3)

You need to subset date.departure in your xtabs call. For year == 2017:

xtabs(as.integer(amount) ~ airport.arrival + airport.departure, df[year(df$date.depature)==2017,])

For year==2017 and month==1:

xtabs(as.integer(amount) ~ airport.arrival + airport.departure, df[year(df$date.depature)==2017 & month(df$date.departure)==1,])

And for anything before Jan 2017:

xtabs(as.integer(amount) ~ airport.arrival + airport.departure, df[df$date.depature<as_date("2017-01-01"),])