Gin_Salmon
Reputation: 847
Creating new data table based on ifelse on old data table
I'm trying to subset my data table using an ifelse statement, but I am not getting the result that I was looking for.
My initial data table looks like this:
head(Data_copy, n = 18)
Company Date DOW variable value Year Month End_of_Month
1: ASXRI 1991-09-06 Friday RI NA 1991 Sep 0
2: ASXRI 1991-09-09 Monday RI NA 1991 Sep 0
3: ASXRI 1991-09-10 Tuesday RI NA 1991 Sep 0
4: ASXRI 1991-09-11 Wednesday RI NA 1991 Sep 0
5: ASXRI 1991-09-12 Thursday RI NA 1991 Sep 0
6: ASXRI 1991-09-13 Friday RI NA 1991 Sep 0
7: ASXRI 1991-09-16 Monday RI NA 1991 Sep 0
8: ASXRI 1991-09-17 Tuesday RI NA 1991 Sep 0
9: ASXRI 1991-09-18 Wednesday RI NA 1991 Sep 0
10: ASXRI 1991-09-19 Thursday RI NA 1991 Sep 0
11: ASXRI 1991-09-20 Friday RI NA 1991 Sep 0
12: ASXRI 1991-09-23 Monday RI NA 1991 Sep 0
13: ASXRI 1991-09-24 Tuesday RI NA 1991 Sep 0
14: ASXRI 1991-09-25 Wednesday RI NA 1991 Sep 0
15: ASXRI 1991-09-26 Thursday RI NA 1991 Sep 0
16: ASXRI 1991-09-27 Friday RI NA 1991 Sep 0
17: ASXRI 1991-09-30 Monday RI NA 1991 Sep 1
18: ASXRI 1991-10-01 Tuesday RI NA 1991 Oct 0
This is 18 rows out of 250,000.
What i want is to split this data table based on an ifelse function as follows:
Data1 <- ifelse("Weekly" == "Weekly", Data_copy[End_of_Month ==1,], Data_copy)
*The "Weekly" == "Weekly" bit is going to be used in a function later.
I want Data1 to be a new data table which only contains the rows where End_of_Month ==1.
When i run the above code, I find that I get a list of the Company names and that is it.
I'll show you what the output looks like:
Data1[[1]]
[1] "ASXRI" "ASXRI" "ASXRI" "ASXRI" "ASXRI" "ASXRI" "ASXRI" "ASXRI" "ASXRI" "ASXRI" "ASXRI"
Now if I scroll further down I get:
[1387] "AANRI" "AANRI" "AANRI" "AANRI" "AANRI" "AANRI" "APARI" "APARI" "APARI" "APARI" "APARI"
[1398] "APARI" "APARI" "APARI" "APARI" "APARI" "APARI" "APARI" "APARI" "APARI" "APARI" "APARI"
Each of these entries is just one of the Company names.
I get my desired result if I do:
Data2 <- Data_copy[End_of_Month == 1, ]
Company Date DOW variable value Year Month End_of_Month
1: ASXRI 1991-09-30 Monday RI NA 1991 Sep 1
2: ASXRI 1991-10-31 Thursday RI NA 1991 Oct 1
3: ASXRI 1991-11-29 Friday RI NA 1991 Nov 1
4: ASXRI 1991-12-31 Tuesday RI NA 1991 Dec 1
5: ASXRI 1992-01-31 Friday RI NA 1992 Jan 1
6: ASXRI 1992-02-28 Friday RI NA 1992 Feb 1
Essentially I'd like to replicate Data2, but using an ifelse statement.
Here's the first 100 rows:
dput(head(Data_copy, n = 100))
structure(list(Company = c("ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI",
"ASXRI", "ASXRI", "ASXRI", "ASXRI", "ASXRI"), Date = structure(c(7918,
7921, 7922, 7923, 7924, 7925, 7928, 7929, 7930, 7931, 7932, 7935,
7936, 7937, 7938, 7939, 7942, 7943, 7944, 7945, 7946, 7949, 7950,
7951, 7952, 7953, 7956, 7957, 7958, 7959, 7960, 7963, 7964, 7965,
7966, 7967, 7970, 7971, 7972, 7973, 7974, 7977, 7978, 7979, 7980,
7981, 7984, 7985, 7986, 7987, 7988, 7991, 7992, 7993, 7994, 7995,
7998, 7999, 8000, 8001, 8002, 8005, 8006, 8007, 8008, 8009, 8012,
8013, 8014, 8015, 8016, 8019, 8020, 8021, 8022, 8023, 8026, 8027,
8028, 8029, 8030, 8033, 8034, 8035, 8036, 8037, 8040, 8041, 8042,
8043, 8044, 8047, 8048, 8049, 8050, 8051, 8054, 8055, 8056, 8057
), class = "Date"), DOW = c("Friday", "Monday", "Tuesday", "Wednesday",
"Thursday", "Friday", "Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Monday",
"Tuesday", "Wednesday", "Thursday", "Friday", "Monday", "Tuesday",
"Wednesday", "Thursday", "Friday", "Monday", "Tuesday", "Wednesday",
"Thursday", "Friday", "Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Monday",
"Tuesday", "Wednesday", "Thursday", "Friday", "Monday", "Tuesday",
"Wednesday", "Thursday", "Friday", "Monday", "Tuesday", "Wednesday",
"Thursday", "Friday", "Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Monday",
"Tuesday", "Wednesday", "Thursday", "Friday", "Monday", "Tuesday",
"Wednesday", "Thursday", "Friday", "Monday", "Tuesday", "Wednesday",
"Thursday", "Friday", "Monday", "Tuesday", "Wednesday", "Thursday"
), variable = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("RI",
"VO", "MV", "TD", "ND"), class = "factor"), value = c(NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
NA_real_), Year = c("1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1991", "1991", "1991",
"1991", "1991", "1991", "1991", "1991", "1992", "1992", "1992",
"1992", "1992", "1992", "1992", "1992", "1992", "1992", "1992",
"1992", "1992", "1992", "1992", "1992", "1992"), Month = c("Sep",
"Sep", "Sep", "Sep", "Sep", "Sep", "Sep", "Sep", "Sep", "Sep",
"Sep", "Sep", "Sep", "Sep", "Sep", "Sep", "Sep", "Oct", "Oct",
"Oct", "Oct", "Oct", "Oct", "Oct", "Oct", "Oct", "Oct", "Oct",
"Oct", "Oct", "Oct", "Oct", "Oct", "Oct", "Oct", "Oct", "Oct",
"Oct", "Oct", "Oct", "Nov", "Nov", "Nov", "Nov", "Nov", "Nov",
"Nov", "Nov", "Nov", "Nov", "Nov", "Nov", "Nov", "Nov", "Nov",
"Nov", "Nov", "Nov", "Nov", "Nov", "Nov", "Dec", "Dec", "Dec",
"Dec", "Dec", "Dec", "Dec", "Dec", "Dec", "Dec", "Dec", "Dec",
"Dec", "Dec", "Dec", "Dec", "Dec", "Dec", "Dec", "Dec", "Dec",
"Dec", "Jan", "Jan", "Jan", "Jan", "Jan", "Jan", "Jan", "Jan",
"Jan", "Jan", "Jan", "Jan", "Jan", "Jan", "Jan", "Jan", "Jan"
), End_of_Month = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0)), .Names = c("Company", "Date", "DOW", "variable", "value",
"Year", "Month", "End_of_Month"), class = c("data.table", "data.frame"
), row.names = c(NA, -100L), .internal.selfref = <pointer: 0x00000000001f0788>)
Upvotes: 3
Views: 540
Answers (1)
Weihuang Wong
Reputation: 13128
Other users have noted that ifelse is inappropriate for your purposes. It may be useful to explain why. From ?ifelse, ifelse(test, yes, no) returns a
vector of the same length and attributes (including dimensions and ‘"class"’) as ‘test’ and data values from the values of ‘yes’ or ‘no’
In other words, if your test vector is length 1, ifelse(...) will return a vector of length 1. For example,
> ifelse(TRUE, 1:3, 7:9)
[1] 1
> ifelse(c(TRUE, FALSE), 1:3, 7:9)
[1] 1 8
In your case,
ifelse("Weekly" == "Weekly", Data_copy[End_of_Month ==1,], Data_copy)
will return a vector of length one. More precisely, since the test returns TRUE, ifelse will return the first element from your yes argument; since it is a dataframe (a type of list), ifelse returns the first element of the dataframe, which is the first column. This is why you get a list of the company names. If you really want to use the ifelse construction, try
ifelse("Weekly" == "Weekly", list(Data_copy[End_of_Month ==1,]), list(Data_copy))
although as others have said, you may be better off using if {} else {}.
Upvotes: 2
Related Questions
- Subset with condition in data table
- How to create data.table with additional rows from table based on a condition
- Subset data.table by one variable based on conditional values in other variables
- Creating Subset of data table in R based on another column condition
- Operating on data table based off of multiple row and column conditions
- New column in R using if statement
- Create new variable in data table based on conditions on other variables
- R data.table: adding new column for subset of rows conditional on all rows
- Subsetting a data.table using another data.table
- Subsetting data.table with a condition