1
$\begingroup$

Here is the structure of my data.frames:

> str(c)
'data.frame':   21633 obs. of  20 variables:
 $ Trade        : num  7e+12 7e+12 7e+12 7e+12 7e+12 ...
     $ New          : Factor w/ 2 levels "N","Y": 1 1 1 1 1 1 1 1 1 1 ...
 $ Amended      : Factor w/ 2 levels "N","Y": 1 1 1 1 1 1 1 1 1 1 ...
     $ Unwound      : Factor w/ 2 levels "N","Y": 1 1 1 1 1 1 1 1 1 1 ...
 $ Fixed        : Factor w/ 2 levels "N","Y": 1 1 1 1 1 1 1 1 1 1 ...
     $ FX.Fixed     : Factor w/ 2 levels "N","Y": 1 1 1 1 1 1 1 1 1 1 ...
 $ Branch       : Factor w/ 1 level "nil": 1 1 1 1 1 1 1 1 1 1 ...
     $ Currency     : Factor w/ 8 levels "AUD","EUR","USD",..: 1 1 1 1 1 1 1 3 1 1 ...
 $ Product      : Factor w/ 4 levels "A","B","C",..: 2 2 2 2 2 2 2 2 2 2 ...
     $ Today.NPV    : num  444 222 0 0 -77777 ...
 $ Today.Unreal : num  13.4 5555.54 0 0 6666.36 ...
     $ Today.Real   : num  0 0 0 0 0 0 0 0 0 0 ...
 $ MTD.Real     : num  0 0 0 0 0 0 0 0 0 0 ...
     $ MTD.Unreal   : num  222 -333 0 0 -444 ...
 $ New.Deals.P.L: num  0 0 0 0 0 0 0 0 0 0 ...
     $ Amend.P.L    : num  0 0 0 0 0 0 0 0 0 0 ...
 $ Unwind.P.L   : num  0 0 0 0 0 0 0 0 0 0 ...
     $ Fixing.P.L   : num  0 0 0 0 0 0 0 0 0 0 ...
 $ Carry.P.L    : num  16.6 81.9 0 0 -319.3 ...
     $ FX.Fixing.P.L: num  0 0 0 0 0 0 0 0 0 0 ...
> str(predict)
'data.frame':   735755 obs. of  7 variables:
 $ X.Financial.Object.  : num  7e+12 7e+12 7e+12 7e+12 7e+12 ...
     $ X.Scaling.Currency.  : Factor w/ 7 levels "AUD","CAD","EUR",..: 4 4 4 4 4 4 4 4 4 4 ...
 $ X.Maturity..date..   : Factor w/ 108 levels "12Aug2011","12Aug2014",..: 22 98 95 88 85 ...
     $ X.Rate.Change.       : num  0 0 0 0 0 0 0 0 0 0 ...
 $ X.Env.COB.Date.      : int  20110815 20110815 20110815 20110815 20110815  ...
     $ X.Predict.           : num  74.36 -3.84 16.77 4.66 11.88 ...
 $ X.Reporting.Currency.: Factor w/ 7 levels "AUD","CAD","EUR",..: 4 4 4 4 4 4 4 4 4 4 ...

Goal 1: aggregate the first data frame 'c' to sum all numeric values by Currency.

I do not need text fields other than the currency field

I tried to use

> aggregate(x=c, by=list("Currency"), FUN="sum")
Error in FUN(X[[1L]], ...) : arguments must have same length

It is apparently incorrect.

Goal 2: I would like to merge the data.frame c with predict using this relationship: "c.Trade == predict.Financial.Object"

I tried:

> cc = merge(c, predict, by=c("Trade","Financial.Object"))
Error in fix.by(by.x, x) : 'by' must specify valid column(s)

but it does not work.

One further question: why the field names in data frame 'predict' have the dot in them? What is the proper way to refer to them?

$\endgroup$
  • $\begingroup$ replace str with dput() and you'll get much better answers. $\endgroup$ – Chase Aug 18 '11 at 12:13
  • $\begingroup$ see the help page for ?data.frame and ?make.names for some light reading on why you get .'s in your column names. There are also a few questions on CV and SO that discuss this in further detail. $\endgroup$ – Chase Aug 18 '11 at 12:15
2
$\begingroup$

I am not an R expert, and since I don’t have your data, I cannot experiment with it. But here is what I would try:

1) I believe your x in the aggregate function should be c$Branch, not c.

2) I believe the argument by must only be used if the columns have the same name in both dataframes. Try using by.x="Trade", by.y="predict.Financial.Object". See documentation.

PS: Convention suggests writing cc <- merge(…) instead of cc = merge(…).

| cite | improve this answer | |
$\endgroup$
  • $\begingroup$ colnames(predict)[1] <- "Trade" would be another way of fixing merge $\endgroup$ – Alex Aug 19 '11 at 8:53
0
$\begingroup$

Answer to Goal 1:

aggregate(. ~ Currency, 
          data=subset(c,select=c("Currency",
                                 names(sapply(c,is.numeric)))),sum)
| cite | improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.