The holiday matching for different states
up vote
-1
down vote
favorite
Currently I have lots of invoice with dates, but they are from different states. I would like to set up a holiday indicator that to check whether the invoice date is a holiday in the corresponding state.
For example I have table A and B as follows, if the date in Table A is the holiday corresponding to the holiday of that state in Table B, the column of the holidayIndicator should be set to 1, otherwise 0. The return should be a complete table A with 0 or 1 value in the column of holidayIndicator.
Table A:
date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0
Table B
State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018
The result should be like the following
date state holidayIndicator
1/1/2018 E 1
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 1
r
edited Nov 9 at 16:54
MrFlick
118k11128158
asked Nov 9 at 16:34
Cherry
53
add a comment |
up vote
-1
down vote
favorite
Currently I have lots of invoice with dates, but they are from different states. I would like to set up a holiday indicator that to check whether the invoice date is a holiday in the corresponding state.
For example I have table A and B as follows, if the date in Table A is the holiday corresponding to the holiday of that state in Table B, the column of the holidayIndicator should be set to 1, otherwise 0. The return should be a complete table A with 0 or 1 value in the column of holidayIndicator.
Table A:
date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0
Table B
State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018
The result should be like the following
date state holidayIndicator
1/1/2018 E 1
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 1
r
edited Nov 9 at 16:54
MrFlick
118k11128158
asked Nov 9 at 16:34
Cherry
53
Welcome at SO! Someone downvoted you question without leaving a comment why (not really a good welcome culture). Anyhow: Please always add your data (tables) as R code, in your case asdata.frame, e. g. code likea <- data.frame(date = c("1/1/2018", "2/1/2018", ...), state = c("E"; "F", "G" ...) ...)This makes it easier for us to prepare an answer. THX :-)
– R Yoda
Nov 9 at 17:31
Thank you so much for the advice!
– Cherry
Nov 9 at 17:50
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
Currently I have lots of invoice with dates, but they are from different states. I would like to set up a holiday indicator that to check whether the invoice date is a holiday in the corresponding state.
For example I have table A and B as follows, if the date in Table A is the holiday corresponding to the holiday of that state in Table B, the column of the holidayIndicator should be set to 1, otherwise 0. The return should be a complete table A with 0 or 1 value in the column of holidayIndicator.
Table A:
date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0
Table B
State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018
The result should be like the following
date state holidayIndicator
1/1/2018 E 1
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 1
r
edited Nov 9 at 16:54
MrFlick
118k11128158
asked Nov 9 at 16:34
Cherry
53
Currently I have lots of invoice with dates, but they are from different states. I would like to set up a holiday indicator that to check whether the invoice date is a holiday in the corresponding state.
For example I have table A and B as follows, if the date in Table A is the holiday corresponding to the holiday of that state in Table B, the column of the holidayIndicator should be set to 1, otherwise 0. The return should be a complete table A with 0 or 1 value in the column of holidayIndicator.
Table A:
date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0
Table B
State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018
The result should be like the following
date state holidayIndicator
1/1/2018 E 1
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 1
r
r
edited Nov 9 at 16:54
MrFlick
118k11128158
asked Nov 9 at 16:34
Cherry
53
edited Nov 9 at 16:54
MrFlick
118k11128158
asked Nov 9 at 16:34
Cherry
53
edited Nov 9 at 16:54
MrFlick
118k11128158
edited Nov 9 at 16:54
MrFlick
118k11128158
edited Nov 9 at 16:54
MrFlick
118k11128158
118k11128158
asked Nov 9 at 16:34
Cherry
53
asked Nov 9 at 16:34
Cherry
53
asked Nov 9 at 16:34
Cherry
53
53
Welcome at SO! Someone downvoted you question without leaving a comment why (not really a good welcome culture). Anyhow: Please always add your data (tables) as R code, in your case asdata.frame, e. g. code likea <- data.frame(date = c("1/1/2018", "2/1/2018", ...), state = c("E"; "F", "G" ...) ...)This makes it easier for us to prepare an answer. THX :-)
– R Yoda
Nov 9 at 17:31
Thank you so much for the advice!
– Cherry
Nov 9 at 17:50
add a comment |
Welcome at SO! Someone downvoted you question without leaving a comment why (not really a good welcome culture). Anyhow: Please always add your data (tables) as R code, in your case asdata.frame, e. g. code likea <- data.frame(date = c("1/1/2018", "2/1/2018", ...), state = c("E"; "F", "G" ...) ...)This makes it easier for us to prepare an answer. THX :-)
– R Yoda
Nov 9 at 17:31
Thank you so much for the advice!
– Cherry
Nov 9 at 17:50
Welcome at SO! Someone downvoted you question without leaving a comment why (not really a good welcome culture). Anyhow: Please always add your data (tables) as R code, in your case as
data.frame, e. g. code like a <- data.frame(date = c("1/1/2018", "2/1/2018", ...), state = c("E"; "F", "G" ...) ...) This makes it easier for us to prepare an answer. THX :-)– R Yoda
Nov 9 at 17:31
Welcome at SO! Someone downvoted you question without leaving a comment why (not really a good welcome culture). Anyhow: Please always add your data (tables) as R code, in your case as
data.frame, e. g. code like a <- data.frame(date = c("1/1/2018", "2/1/2018", ...), state = c("E"; "F", "G" ...) ...) This makes it easier for us to prepare an answer. THX :-)– R Yoda
Nov 9 at 17:31
Thank you so much for the advice!
– Cherry
Nov 9 at 17:50
Thank you so much for the advice!
– Cherry
Nov 9 at 17:50
add a comment |
3 Answers
3
active
oldest
votes
up vote
1
down vote
accepted
Assuming the two tables are df1 and df2
df1$holidayIndicator[interaction(df1[, c('date', 'state')]) %in% interaction(df2[, c('Holiday', 'State')])] <- 1
answered Nov 9 at 16:57
pooja p
1096
Thank you! That's helpful! Just here also need to match the state, i.e., if the row in table A is with state E, it should match the E state calendar in table B, any advice?
– Cherry
Nov 9 at 17:01
In that case, first, join the two data frames using 'inner_join' on date and State columns. The result will be a data frame. Use this as df2.
– pooja p
Nov 9 at 17:36
you mean inner_join the two columns in df1? I'm confused, could you please explain more?
– Cherry
Nov 9 at 17:42
I see the problem with that. You can try using 'interaction'. I will edit the answer with this.
– pooja p
Nov 9 at 18:20
add a comment |
up vote
0
down vote
I'm not as familiar with R, but wonder if you could use a package/library like 'bizdays' to determine if a given date is a holiday or not.
https://cran.r-project.org/web/packages/bizdays/bizdays.pdf
answered Nov 9 at 16:49
rs311
1359
add a comment |
up vote
0
down vote
A pure data.frame based solution (without using the packages dplyr or data.table would look like this:
a <- read.table(text = "date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0", header = TRUE, stringsAsFactors = FALSE)
b <- read.table(text = "State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018", header = TRUE, stringsAsFactors = FALSE)
b$isHoliday <- 1 # add a helper column (auto-fills all rows with the same value)
# "inner join" similar to SQL to "enrich" the helper column value
res <- merge(a, b, by.x = c("date", "state"), by.y = c("Holiday", "State"), all.x = TRUE)
res$holidayIndicator[res$isHoliday == 1] <- 1 # mark the holidays using the enriched helper column
# Optionally: Remove the helper column from the result
res$isHoliday <- NULL
answered Nov 9 at 18:00
R Yoda
3,9081840
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Assuming the two tables are df1 and df2
df1$holidayIndicator[interaction(df1[, c('date', 'state')]) %in% interaction(df2[, c('Holiday', 'State')])] <- 1
answered Nov 9 at 16:57
pooja p
1096
Thank you! That's helpful! Just here also need to match the state, i.e., if the row in table A is with state E, it should match the E state calendar in table B, any advice?
– Cherry
Nov 9 at 17:01
In that case, first, join the two data frames using 'inner_join' on date and State columns. The result will be a data frame. Use this as df2.
– pooja p
Nov 9 at 17:36
you mean inner_join the two columns in df1? I'm confused, could you please explain more?
– Cherry
Nov 9 at 17:42
I see the problem with that. You can try using 'interaction'. I will edit the answer with this.
– pooja p
Nov 9 at 18:20
add a comment |
up vote
1
down vote
accepted
Assuming the two tables are df1 and df2
df1$holidayIndicator[interaction(df1[, c('date', 'state')]) %in% interaction(df2[, c('Holiday', 'State')])] <- 1
answered Nov 9 at 16:57
pooja p
1096
Thank you! That's helpful! Just here also need to match the state, i.e., if the row in table A is with state E, it should match the E state calendar in table B, any advice?
– Cherry
Nov 9 at 17:01
In that case, first, join the two data frames using 'inner_join' on date and State columns. The result will be a data frame. Use this as df2.
– pooja p
Nov 9 at 17:36
you mean inner_join the two columns in df1? I'm confused, could you please explain more?
– Cherry
Nov 9 at 17:42
I see the problem with that. You can try using 'interaction'. I will edit the answer with this.
– pooja p
Nov 9 at 18:20
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Assuming the two tables are df1 and df2
df1$holidayIndicator[interaction(df1[, c('date', 'state')]) %in% interaction(df2[, c('Holiday', 'State')])] <- 1
answered Nov 9 at 16:57
pooja p
1096
Assuming the two tables are df1 and df2
df1$holidayIndicator[interaction(df1[, c('date', 'state')]) %in% interaction(df2[, c('Holiday', 'State')])] <- 1
answered Nov 9 at 16:57
pooja p
1096
edited Nov 9 at 18:24
answered Nov 9 at 16:57
pooja p
1096
answered Nov 9 at 16:57
pooja p
1096
answered Nov 9 at 16:57
pooja p
1096
1096
Thank you! That's helpful! Just here also need to match the state, i.e., if the row in table A is with state E, it should match the E state calendar in table B, any advice?
– Cherry
Nov 9 at 17:01
In that case, first, join the two data frames using 'inner_join' on date and State columns. The result will be a data frame. Use this as df2.
– pooja p
Nov 9 at 17:36
you mean inner_join the two columns in df1? I'm confused, could you please explain more?
– Cherry
Nov 9 at 17:42
I see the problem with that. You can try using 'interaction'. I will edit the answer with this.
– pooja p
Nov 9 at 18:20
add a comment |
Thank you! That's helpful! Just here also need to match the state, i.e., if the row in table A is with state E, it should match the E state calendar in table B, any advice?
– Cherry
Nov 9 at 17:01
In that case, first, join the two data frames using 'inner_join' on date and State columns. The result will be a data frame. Use this as df2.
– pooja p
Nov 9 at 17:36
you mean inner_join the two columns in df1? I'm confused, could you please explain more?
– Cherry
Nov 9 at 17:42
I see the problem with that. You can try using 'interaction'. I will edit the answer with this.
– pooja p
Nov 9 at 18:20
Thank you! That's helpful! Just here also need to match the state, i.e., if the row in table A is with state E, it should match the E state calendar in table B, any advice?
– Cherry
Nov 9 at 17:01
Thank you! That's helpful! Just here also need to match the state, i.e., if the row in table A is with state E, it should match the E state calendar in table B, any advice?
– Cherry
Nov 9 at 17:01
In that case, first, join the two data frames using 'inner_join' on date and State columns. The result will be a data frame. Use this as df2.
– pooja p
Nov 9 at 17:36
In that case, first, join the two data frames using 'inner_join' on date and State columns. The result will be a data frame. Use this as df2.
– pooja p
Nov 9 at 17:36
you mean inner_join the two columns in df1? I'm confused, could you please explain more?
– Cherry
Nov 9 at 17:42
you mean inner_join the two columns in df1? I'm confused, could you please explain more?
– Cherry
Nov 9 at 17:42
I see the problem with that. You can try using 'interaction'. I will edit the answer with this.
– pooja p
Nov 9 at 18:20
I see the problem with that. You can try using 'interaction'. I will edit the answer with this.
– pooja p
Nov 9 at 18:20
add a comment |
up vote
0
down vote
I'm not as familiar with R, but wonder if you could use a package/library like 'bizdays' to determine if a given date is a holiday or not.
https://cran.r-project.org/web/packages/bizdays/bizdays.pdf
answered Nov 9 at 16:49
rs311
1359
add a comment |
up vote
0
down vote
I'm not as familiar with R, but wonder if you could use a package/library like 'bizdays' to determine if a given date is a holiday or not.
https://cran.r-project.org/web/packages/bizdays/bizdays.pdf
answered Nov 9 at 16:49
rs311
1359
add a comment |
up vote
0
down vote
up vote
0
down vote
I'm not as familiar with R, but wonder if you could use a package/library like 'bizdays' to determine if a given date is a holiday or not.
https://cran.r-project.org/web/packages/bizdays/bizdays.pdf
answered Nov 9 at 16:49
rs311
1359
I'm not as familiar with R, but wonder if you could use a package/library like 'bizdays' to determine if a given date is a holiday or not.
https://cran.r-project.org/web/packages/bizdays/bizdays.pdf
answered Nov 9 at 16:49
rs311
1359
answered Nov 9 at 16:49
rs311
1359
answered Nov 9 at 16:49
rs311
1359
answered Nov 9 at 16:49
rs311
1359
1359
add a comment |
add a comment |
up vote
0
down vote
A pure data.frame based solution (without using the packages dplyr or data.table would look like this:
a <- read.table(text = "date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0", header = TRUE, stringsAsFactors = FALSE)
b <- read.table(text = "State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018", header = TRUE, stringsAsFactors = FALSE)
b$isHoliday <- 1 # add a helper column (auto-fills all rows with the same value)
# "inner join" similar to SQL to "enrich" the helper column value
res <- merge(a, b, by.x = c("date", "state"), by.y = c("Holiday", "State"), all.x = TRUE)
res$holidayIndicator[res$isHoliday == 1] <- 1 # mark the holidays using the enriched helper column
# Optionally: Remove the helper column from the result
res$isHoliday <- NULL
answered Nov 9 at 18:00
R Yoda
3,9081840
add a comment |
up vote
0
down vote
A pure data.frame based solution (without using the packages dplyr or data.table would look like this:
a <- read.table(text = "date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0", header = TRUE, stringsAsFactors = FALSE)
b <- read.table(text = "State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018", header = TRUE, stringsAsFactors = FALSE)
b$isHoliday <- 1 # add a helper column (auto-fills all rows with the same value)
# "inner join" similar to SQL to "enrich" the helper column value
res <- merge(a, b, by.x = c("date", "state"), by.y = c("Holiday", "State"), all.x = TRUE)
res$holidayIndicator[res$isHoliday == 1] <- 1 # mark the holidays using the enriched helper column
# Optionally: Remove the helper column from the result
res$isHoliday <- NULL
answered Nov 9 at 18:00
R Yoda
3,9081840
add a comment |
up vote
0
down vote
up vote
0
down vote
A pure data.frame based solution (without using the packages dplyr or data.table would look like this:
a <- read.table(text = "date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0", header = TRUE, stringsAsFactors = FALSE)
b <- read.table(text = "State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018", header = TRUE, stringsAsFactors = FALSE)
b$isHoliday <- 1 # add a helper column (auto-fills all rows with the same value)
# "inner join" similar to SQL to "enrich" the helper column value
res <- merge(a, b, by.x = c("date", "state"), by.y = c("Holiday", "State"), all.x = TRUE)
res$holidayIndicator[res$isHoliday == 1] <- 1 # mark the holidays using the enriched helper column
# Optionally: Remove the helper column from the result
res$isHoliday <- NULL
answered Nov 9 at 18:00
R Yoda
3,9081840
A pure data.frame based solution (without using the packages dplyr or data.table would look like this:
a <- read.table(text = "date state holidayIndicator
1/1/2018 E 0
2/1/2018 F 0
3/1/2018 G 0
4/1/2018 E 0
5/1/2018 F 0
6/1/2018 G 0", header = TRUE, stringsAsFactors = FALSE)
b <- read.table(text = "State Holiday
E 1/1/2018
E 3/1/2018
E 3/28/2018
F 5/26/2018
F 6/2/2018
F 7/1/2018
G 9/1/2018
G 6/1/2018
G 5/29/2018", header = TRUE, stringsAsFactors = FALSE)
b$isHoliday <- 1 # add a helper column (auto-fills all rows with the same value)
# "inner join" similar to SQL to "enrich" the helper column value
res <- merge(a, b, by.x = c("date", "state"), by.y = c("Holiday", "State"), all.x = TRUE)
res$holidayIndicator[res$isHoliday == 1] <- 1 # mark the holidays using the enriched helper column
# Optionally: Remove the helper column from the result
res$isHoliday <- NULL
answered Nov 9 at 18:00
R Yoda
3,9081840
edited Nov 9 at 18:33
answered Nov 9 at 18:00
R Yoda
3,9081840
answered Nov 9 at 18:00
R Yoda
3,9081840
answered Nov 9 at 18:00
R Yoda
3,9081840
3,9081840
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53229746%2fthe-holiday-matching-for-different-states%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Welcome at SO! Someone downvoted you question without leaving a comment why (not really a good welcome culture). Anyhow: Please always add your data (tables) as R code, in your case as
data.frame, e. g. code likea <- data.frame(date = c("1/1/2018", "2/1/2018", ...), state = c("E"; "F", "G" ...) ...)This makes it easier for us to prepare an answer. THX :-)– R Yoda
Nov 9 at 17:31
Thank you so much for the advice!
– Cherry
Nov 9 at 17:50