Hi, I have 2 data frames which are as follows
df_macro <- data.frame(
us_gdp = runif(12),
uk_gdp = c(NA,runif(11)),
jp_gdp = c(NA,NA,runif(10)),
us_inf = runif(12),
uk_inf = c(NA,NA,NA,runif(9)),
jp_inf = c(NA,runif(11)),
us_monetary_policy = runif(12),
uk_monetary_policy = runif(12),
jp_monetary_policy = runif(12),
row.names = c("Jan-78","Feb-78","Mar-78", "Apr-78", "May-78", "June-78", "July-78", "Aug-78", "Sep-78",
"Oct-78","Nov-78", "Dec-78"))
df_asset <- data.frame(
us_equity = runif(12),
uk_equity = runif(12),
jp_equity = runif(12),
us_bond = runif(12),
uk_bond = runif(12),
jp_bond = runif(12),
row.names = c("Jan-78","Feb-78","Mar-78", "Apr-78", "May-78", "June-78", "July-78", "Aug-78", "Sep-78",
"Oct-78","Nov-78", "Dec-78")
)
equity_data <- df_asset[,stringr::str_detect(names(df_asset), 'equity')]
gdp_data <- df_macro[,stringr::str_detect(names(df_macro), 'gdp')]
Now, I want to take monthly change of the variables in df_macro and then rank them accordingly. Once I do this, I need to find out the top and bottom country for each variable i.e. gdp, inflation and monetary policy.
After doing this, I need to extract values from the df_asset class. Here first I subset equity data and then for each month based on the rank of the country, I need to extract the top and bottom ranked countries' equity data. This I have to do for all macro themes and asset classes. i.e. For GDP, I have to get data for equity and bond separately, for inflation this has to be done separately etc.
I started writing a function which takes macro_theme and asset_class data and then it gives me the output which is a matrix containing the top and bottom asset class values for each month. This will not have any column names as the top and bottom ranked countries in each month are different. This is where I am stuck, I am providing you the code below
signal <- function(macro_theme = NULL, asset_class = NULL){
monthly_return <- diff(log(asset_class),1)*100
#calculate annual change in macro variable
macro_theme_pct <- zoo(matrix(ncol = ncol(macro_theme), nrow=nrow(macro_theme)), index(macro_theme))
#macro_theme_pct <- as.data.frame(macro_theme_pct)
colnames(macro_theme_pct) <- colnames(macro_theme)
for (i in 1:ncol(macro_theme_pct)){
macro_theme_pct[,i] <- Delt(macro_theme[,i], k=1, type="arithmetic")*100
}
macro_theme_pct <- na.omit(macro_theme_pct)
#ranking countries basd on macro indicators
rank_macro <- zoo(data.frame(macro_theme_pct, t(apply(-macro_theme_pct, 1, rank, ties.method='min'))), index(macro_theme_pct))
rank_macro <- rank_macro[,ncol(macro_theme_pct)+1:ncol(rank_macro)]
scaled_rank <- (rank_macro-apply(rank_macro, 1, mean))/apply(rank_macro, 1, sd)
column_names_long <- zoo(matrix(ncol = 3, nrow=nrow(rank_macro)), index(rank_macro))
for(i in 1:nrow(rank_macro)){
for(j in 1:ncol(rank_macro)){
if(rank_macro[i,j] == 1){
column_names[i,1] <- colnames(rank_macro)[j]
}else if(rank_macro[i,j] == 2){
column_names[i,2] <- colnames(rank_macro)[j]
}
}
}
st_date <- index(rank_macro)[1]
end_date <- index(rank_macro)[nrow(rank_macro)]
equity_data_r <- window(equity_data, index. = index(equity_data), st_date, end_date)
equity_final <- zoo(matrix(ncol = 2, nrow=nrow(equity_data_r)), index(equity_data_r))
for(i in nrow(equity_data_r)){
for(j in ncol(equity_data_r)){
ss <- substr(colnames(equity_data_r), 1, 3)
tt <- substr(column_names_long[i,j],1,3)
a <- ss %in% tt
equity_final[i,j] = equity_data_r[i, a]
}
}
}
Once I have the column names of the top and bottom period, How should I go about getting the values from equity data.
If this is too confusing (which I belive is the case) then Please let me know I will try and explain more clearly.
Any help would be greatly appreciated. If you are too busy to write the code, please drop me a hint on how to proceed.
Thanks.
