Creating a categorical variable for a dataframe based on column value

Question

I have a dataframe created in R that is organized as follows:

> all_data[3945:3952,]
           Date btc_close eth_close vix_close gold_close DEXCHUS
3945 2016-11-01    729.27     10.77     18.56     122.73     828
3946 2016-11-02    742.46        NA     19.32     123.64     826
3947 2016-11-03    687.51        NA     22.08     124.30     827
3948 2016-11-04    702.54        NA     22.51     124.39     824
3949 2016-11-05    704.16        NA        NA         NA      NA
3950 2016-11-06    712.24        NA        NA         NA      NA
3951 2016-11-07    704.02        NA     18.71     122.15     835
3952 2016-11-08    709.15     10.87     18.74     121.64     843

How can I add a new column that has 3 levels? The levels would be -1 for decrease, 0 for no change, and 1 for increase. This direction column should be based upon the previous days value for btc_close. (Note- there will be many NA's- I would then like to subset the data according to only rows that have data for btc_close)

lmo · Accepted Answer · 2017-08-31 16:42:50Z

After dropping NA rows, you could do

dat$change <- c(0, sign(diff(dat$btc_close)))

for this example, you'd get

dat
           Date btc_close eth_close vix_close gold_close DEXCHUS change
3945 2016-11-01    729.27     10.77     18.56     122.73     828      0
3946 2016-11-02    742.46        NA     19.32     123.64     826      1
3947 2016-11-03    687.51        NA     22.08     124.30     827     -1
3948 2016-11-04    702.54        NA     22.51     124.39     824      1
3949 2016-11-05    704.16        NA        NA         NA      NA      1
3950 2016-11-06    712.24        NA        NA         NA      NA      1
3951 2016-11-07    704.02        NA     18.71     122.15     835     -1
3952 2016-11-08    709.15     10.87     18.74     121.64     843      1

data

dat <- 
structure(list(Date = structure(1:8, .Label = c("2016-11-01", 
"2016-11-02", "2016-11-03", "2016-11-04", "2016-11-05", "2016-11-06", 
"2016-11-07", "2016-11-08"), class = "factor"), btc_close = c(729.27, 
742.46, 687.51, 702.54, 704.16, 712.24, 704.02, 709.15), eth_close = c(10.77, 
NA, NA, NA, NA, NA, NA, 10.87), vix_close = c(18.56, 19.32, 22.08, 
22.51, NA, NA, 18.71, 18.74), gold_close = c(122.73, 123.64, 
124.3, 124.39, NA, NA, 122.15, 121.64), DEXCHUS = c(828L, 826L, 
827L, 824L, NA, NA, 835L, 843L)), .Names = c("Date", "btc_close", 
"eth_close", "vix_close", "gold_close", "DEXCHUS"), class = "data.frame", row.names = c("3945", 
"3946", "3947", "3948", "3949", "3950", "3951", "3952"))

Damiano Fantini · Accepted Answer · 2017-08-31 16:43:10Z

0

I would suggest the following strategy. You can compare values in btc_closed column/attribute across your table using ifelse. Remember to add a NA value (or a 0 if you prefer) since you start to compare from row 2.

df <- data.frame(btc_close = c(223, 222, 224, 224, 223, 223, 224), stuff = NA) 
df$direction <- c(NA, (sapply(2:nrow(df), (function(i){
  ifelse(df$btc_close[i] > df$btc_close[(i-1)], 1, 
         ifelse(df$btc_close[i] < df$btc_close[(i-1)], -1, 0))}))))

answered Aug 31, 2017 at 16:43

Damiano Fantini

1,97511 silver badges11 bronze badges

Collectives™ on Stack Overflow

Creating a categorical variable for a dataframe based on column value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related