-1

Given two data frames of the same dimension:

Categories.df1 <- data.frame(Categories)
Returns.df2 <- data.frame(Returns)

Categories.df1
#Fund             X31.05.2015     X30.04.2015     X31.03.2015
#1 3 Banken EU                                     Concentrated
#2 3 Banken GL                                     Stock Pickers
#3 3 Banken NAC                                    Stock Pickers
#4 3 Banken O                                      Factor Betters
#5 3V Invest      Stock Pickers   Stock Pickers    Moderately Active
#6 4Q EU          Stock Pickers   Stock Pickers    Stock Pickers

Returns.df2
#Fund             X31.05.2015    X30.04.2015      X31.03.2015
#1 3 Banken EU     0.01           0.02             
#2 3 Banken GL    -0.04          -0.01             0.03
#3 3 Banken NAC   -0.02          -0.01             0.04
#4 3 Banken O     -0.01                           -0.02
#5 3V Invest       0.01           0.02             0.00
#6 4Q EU          -0.01          -0.01             0.00

Can I replace values in Categories.df1, with values in Returns.df2, conditioned on "Stock Pickers" (else blank)?

Hence, I want to create a new data frame, or subset, that would look like:

Returns.StockPickers.df3
#Fund             X31.05.2015    X30.04.2015      X31.03.2015
#1 3 Banken EU                           
#2 3 Banken GL                                     0.03
#3 3 Banken NAC                                    0.04
#4 3 Banken O                                
#5 3V Invest       0.01           0.02             
#6 4Q EU          -0.01          -0.01             0.01

Any inputs?

Jaap
  • 81,064
  • 34
  • 182
  • 193
Oden
  • 13
  • 1
  • 7
  • When giving example data, it is much better to give the output of `dput(df)`: [see the info on how to give a reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610) – Jaap Feb 27 '16 at 15:24

1 Answers1

1

This should do it (I updated the snippets and included a function that takes the two different data.frames and returns the return_data.frame with the new values. However, the new values are a string and not numeric as of now):

df1 <- data.frame(fund = c("Fund 1", "Fund 2", "Fund 3", "Fund 4"),
                  X31.05.2015 = c("Stock Picker", "", "Stock Picker", ""))

df2 <- data.frame(fund = c("Fund 1", "Fund 2", "Fund 3", "Fund 4"),
                  X31.05.2015 = c(1, 2, 3, 4))


fun <- function(cat_df, ret_df, col_name) {
  ret_df[, col_name] <- ifelse(cat_df[, col_name] == "Stock Picker", ret_df[, col_name],
                               "") # change '""' to 0 or NA if you want to get Numerics 
  return(ret_df)
}

df3 <- fun(df1, df2, col_name = "X31.05.2015")

df3
# fund X31.05.2015
# 1 Fund 1           1
# 2 Fund 2            
# 3 Fund 3           3
# 4 Fund 4
David
  • 9,216
  • 4
  • 45
  • 78
  • I dont completely understand your merged_df. I am seeking the same output data_frame, as my example. So just replacing the "Stock Pickers" with the corresponding returns in Returns.df2, into a new subset. Also the dimension for my entire dataset is 2273*260, which makes the typing of column names alittle tedious. – Oden Feb 27 '16 at 14:53
  • Are you sure that the columns `Fund` are always in the same order? Or do you need a check in that? – David Feb 27 '16 at 14:59
  • Yes they are always in the same order. I made sure of that – Oden Feb 27 '16 at 15:00
  • Just like in your previous question, people come up with solutions that feel a bit safer. Overwriting dataframes (2273*360 is not that big) can make people nervous, and people tend to want to check what they're doing. Your direct approach could be done by `ifelse(df1[,-1]=="Stock Picker",1,NA)*df2[,2:ncol(df2)]` – Heroka Feb 27 '16 at 15:06
  • Thank you for input again Heroka. It gives me X31.05.2015 X30.04.2015 X31.03.2015 1 NA NA NA 2 NA NA NA 3 NA NA NA 4 NA NA NA 5 NA NA NA 6 NA NA NA – Oden Feb 27 '16 at 15:11
  • David, we are getting there. However, it seems like the df3 gives the sum of "Stock Pickers" and not the values them selves. Is it possible just to show the values in the same element as "Stock Pickers" in df3? – Oden Feb 27 '16 at 15:33