Return value in column for each value = a given month

Question

I am trying to create a column that only returns a value when month = 06.

E.g the 'Hold' column below.

  Date  Permno      Value             Hold
2000-01 10026   Big, Growth 
2000-02 10026   Small, Value    
2000-03 10026   Neutral, Neutral    
2000-04 10026   Big, Value  
2000-05 10026   Big, Value  
2000-06 10026   Big, Value          Big, Value
2000-07 10026   Big, Value  
2000-08 10026   Big, Value  
2000-09 10026   Small, Value    
2000-10 10026   Small, Neutral  
2000-11 10026   Neutral, Neutral    
2000-12 10026   Big, Growth 
2001-01 10026   Small, Value    
2001-02 10026   Neutral, Neutral    
2001-03 10026   Big, Value  
2001-04 10026   Big, Value  
2001-05 10026   Small, Value    
2001-06 10026   Small, Neutral      Small, Neutral
2001-07 10026   Neutral, Neutral    
2001-08 10026   Big, Growth 
2001-09 10026   Small, Value    
2001-10 10026   Neutral, Neutral    
2001-11 10026   Big, Value  
2001-12 10026   Small, Neutral  
2000-01 10030   Neutral, Neutral    
2000-02 10030   Small, Neutral

Do you know how I can do this? Additionally, the 'Hold' column should also be dependent on 'Permno', as I need to forward fill the values from the 'Hold' and want to avoid filling values from different Permnos into each other

Thank you!

Hi Manu, I am very new to R and don't know what a posixct variable is .. So I am afraid I don't know how to answer that question — Fredrik Clement, Mar 07 '21 at 15:02
Start here: [How to extract month from a Year-Month type object in R?](https://stackoverflow.com/questions/47597492/how-to-extract-month-from-a-year-month-2017-10-type-object-in-r). Then [Replace missing values (NA) with most recent non-NA by group](https://stackoverflow.com/questions/23340150/replace-missing-values-na-with-most-recent-non-na-by-group) — Henrik, Mar 07 '21 at 15:03
I tried the "month" function (Lubridate package) an got the following error: My code was: FF5_class$Month <- FF5_class %>% month(date) Error in as.POSIXlt.default(x, tz = tz(x)) : do not know how to convert 'x' to class “POSIXlt” — Fredrik Clement, Mar 07 '21 at 15:13
mmmm, please could you type this in your console `dput(head(FF5_class))` and copy the result and paste it in your question. In that way we will see a sample of your dataset (I assume its name is FF5_class) and we can help you better — Manu, Mar 07 '21 at 15:25
Hi Manu, I tried this, but as I have a very big data frame, the output was very big - too big to post in here I reckon... — Fredrik Clement, Mar 07 '21 at 15:32
By using `head` the data will be only 6 rows. Unless you have many columns in your data frame. — Manu, Mar 07 '21 at 15:34
I have imported a file earlier, which I have merged into this .. When running this function, i get a lot of output - should indicate something is wrong I guess? — Fredrik Clement, Mar 07 '21 at 15:39
I just tried, but there are > 65,000 characters which is too long for me to post .. — Fredrik Clement, Mar 07 '21 at 15:51

TarJae · Answer 1 · 2021-03-07T17:49:29.413

You can use yearmonfunction from zoo. Then search for string Jun with R grepl function in Date column and apply desired condition with case_whenfrom dplyr package.

library(zoo)
library(dplyr)

# your data
Date <- c("2000-01", "2000-02", "2000-03", "2000-04", "2000-05", "2000-06", 
"2000-07", "2000-08", "2000-09", "2000-10", "2000-11", "2000-12", "2001-01", 
"2001-02", "2001-03", "2001-04", "2001-05", "2001-06", "2001-07", "2001-08", 
"2001-09", "2001-10", "2001-11", "2001-12", "2000-01", "2000-02")

Permno <- c(10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 
            10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 
            10026, 10026, 10026, 10026, 10026, 10026, 10030, 10030)

Value <- c("Big, Growth", "Small, Value", "Neutral, Neutral", "Big, Value", "Big, Value",
  "Big, Value", "Big, Value", "Big, Value", "Small, Value", "Small, Neutral", 
  "Neutral, Neutral", "Big, Growth", "Small, Value", "Neutral, Neutral", 
  "Big, Value", "Big, Value", "Small, Value", "Small, Neutral", 
  "Neutral, Neutral", "Big, Growth", "Small, Value", "Neutral, Neutral",
  "Big, Value",  "Small, Neutral", "Neutral, Neutral", "Small, Neutral") 

df <- data.frame(Date, Permno, Value)

# code for your desired output
df1 <- df %>% 
  mutate(Date = as.yearmon(Date),
         Hold = case_when(grepl("Jun", Date) ~ Value))

# Output:
> df1
       Date Permno            Value           Hold
1  Jan 2000  10026      Big, Growth           <NA>
2  Feb 2000  10026     Small, Value           <NA>
3  Mar 2000  10026 Neutral, Neutral           <NA>
4  Apr 2000  10026       Big, Value           <NA>
5  May 2000  10026       Big, Value           <NA>
6  Jun 2000  10026       Big, Value     Big, Value
7  Jul 2000  10026       Big, Value           <NA>
8  Aug 2000  10026       Big, Value           <NA>
9  Sep 2000  10026     Small, Value           <NA>
10 Oct 2000  10026   Small, Neutral           <NA>
11 Nov 2000  10026 Neutral, Neutral           <NA>
12 Dec 2000  10026      Big, Growth           <NA>
13 Jan 2001  10026     Small, Value           <NA>
14 Feb 2001  10026 Neutral, Neutral           <NA>
15 Mar 2001  10026       Big, Value           <NA>
16 Apr 2001  10026       Big, Value           <NA>
17 May 2001  10026     Small, Value           <NA>
18 Jun 2001  10026   Small, Neutral Small, Neutral
19 Jul 2001  10026 Neutral, Neutral           <NA>
20 Aug 2001  10026      Big, Growth           <NA>
21 Sep 2001  10026     Small, Value           <NA>
22 Oct 2001  10026 Neutral, Neutral           <NA>
23 Nov 2001  10026       Big, Value           <NA>
24 Dec 2001  10026   Small, Neutral           <NA>
25 Jan 2000  10030 Neutral, Neutral           <NA>
26 Feb 2000  10030   Small, Neutral           <NA>

Bravo! I like it when I can delete my snarky comments. And give upvotes. — IRTFM, Mar 07 '21 at 17:51

score 0 · Answer 2 · answered Mar 10 '21 at 10:24

Why not simply this?

FF5_class$HOLD <- ifelse(substr(FF5_class$Date, 6,7) =="06", FF5_class$Value, NA)
      Date Permno            Value           HOLD
1  2000-01  10026      Big, Growth           <NA>
2  2000-02  10026     Small, Value           <NA>
3  2000-03  10026 Neutral, Neutral           <NA>
4  2000-04  10026       Big, Value           <NA>
5  2000-05  10026       Big, Value           <NA>
6  2000-06  10026       Big, Value     Big, Value
7  2000-07  10026       Big, Value           <NA>
8  2000-08  10026       Big, Value           <NA>
9  2000-09  10026     Small, Value           <NA>
10 2000-10  10026   Small, Neutral           <NA>
11 2000-11  10026 Neutral, Neutral           <NA>
12 2000-12  10026      Big, Growth           <NA>
13 2001-01  10026     Small, Value           <NA>
14 2001-02  10026 Neutral, Neutral           <NA>
15 2001-03  10026       Big, Value           <NA>
16 2001-04  10026       Big, Value           <NA>
17 2001-05  10026     Small, Value           <NA>
18 2001-06  10026   Small, Neutral Small, Neutral
19 2001-07  10026 Neutral, Neutral           <NA>
20 2001-08  10026      Big, Growth           <NA>
21 2001-09  10026     Small, Value           <NA>
22 2001-10  10026 Neutral, Neutral           <NA>
23 2001-11  10026       Big, Value           <NA>
24 2001-12  10026   Small, Neutral           <NA>
25 2000-01  10030 Neutral, Neutral           <NA>
26 2000-02  10030   Small, Neutral           <NA>

dput(FF5_class) used

FF5_class <- structure(list(Date = c("2000-01", "2000-02", "2000-03", "2000-04", 
"2000-05", "2000-06", "2000-07", "2000-08", "2000-09", "2000-10", 
"2000-11", "2000-12", "2001-01", "2001-02", "2001-03", "2001-04", 
"2001-05", "2001-06", "2001-07", "2001-08", "2001-09", "2001-10", 
"2001-11", "2001-12", "2000-01", "2000-02"), Permno = c(10026, 
10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 
10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 10026, 
10026, 10026, 10026, 10026, 10026, 10030, 10030), Value = c("Big, Growth", 
"Small, Value", "Neutral, Neutral", "Big, Value", "Big, Value", 
"Big, Value", "Big, Value", "Big, Value", "Small, Value", "Small, Neutral", 
"Neutral, Neutral", "Big, Growth", "Small, Value", "Neutral, Neutral", 
"Big, Value", "Big, Value", "Small, Value", "Small, Neutral", 
"Neutral, Neutral", "Big, Growth", "Small, Value", "Neutral, Neutral", 
"Big, Value", "Small, Neutral", "Neutral, Neutral", "Small, Neutral"
), HOLD = c(NA, NA, NA, NA, NA, "Big, Value", NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, "Small, Neutral", NA, NA, NA, NA, 
NA, NA, NA, NA)), row.names = c(NA, -26L), class = "data.frame")

Return value in column for each value = a given month

2 Answers2