CODEWITHSUNDEEP
r
Fredrik Clement
Fredrik Clement

Reputation: 65

Return value in column for each value = a given month

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!

Upvotes: 1

Views: 51

Answers (2)

AnilGoyal
AnilGoyal

Reputation: 26218

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")

Upvotes: 0

TarJae
TarJae

Reputation: 78917

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>

Upvotes: 1

Related Questions