1
$\begingroup$

I have a data frame of the following format:
Symbol Date Time Profit
$BANKNIFTY 4/1/2010 9:55:00 -1.18% <br>$BANKNIFTY 4/1/2010 12:30:00 -2.84%
$BANKNIFTY 4/1/2010 12:45:00 7.17% <br>$BANKNIFTY 5/1/2010 11:40:00 -7.11%
ZEEL 26/6/2012 13:50:00 24.75%
ZEEL 27/6/2012 15:15:00 -1.90%
ZEEL 28/6/2012 9:45:00 37.58%
ZEEL 28/6/2012 14:55:00 23.95%
ZEEL 29/6/2012 14:20:00 -4.65%
ZEEL 29/6/2012 14:30:00 -6.01%
ZEEL 29/6/2012 14:55:00 -12.23%
ZEEL 29/6/2012 15:15:00 35.13%

What I'd like to achieve is convert that data frame into a data frame which has dates for row names, symbol names for columns and sum of percentage profit for each day. Like in the following:


Date BankNifty ZEEL
4/1/2010 3.15% 0
5/1/2010 -7.11% 0
26/6/2012 0 24.75%
27/6/2012 0 -1.90%
28/6/2012 0 61.53%
29/6/2012 0 12.24%

How can I achieve that in R? dplyr mutation or some apply function?

I'm a beginner in R programming. Thanks in advance.

The data in R is

structure(list(Symbol = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L), .Label = c("BANKNIFTY", "ZEEL"), class = "factor"), 
    Date = structure(c(5L, 5L, 5L, 6L, 1L, 2L, 3L, 3L, 4L, 4L, 
    4L, 4L), .Label = c("26/6/2012", "27/6/2012", "28/6/2012", 
    "29/6/2012", "4/1/2010", "5/1/2010"), class = "factor"), 
    Time = structure(c(10L, 2L, 3L, 1L, 4L, 8L, 9L, 7L, 5L, 6L, 
    7L, 8L), .Label = c("11:40:00", "12:30:00", "12:45:00", "13:50:00", 
    "14:20:00", "14:30:00", "14:55:00", "15:15:00", "9:45:00", 
    "9:55:00"), class = "factor"), Profit = structure(c(1L, 4L, 
    12L, 7L, 9L, 2L, 11L, 8L, 5L, 6L, 3L, 10L), .Label = c("-1.18%", 
    "-1.90%", "-12.23%", "-2.84%", "-4.65%", "-6.01%", "-7.11%", 
    "23.95%", "24.75%", "35.13%", "37.58%", "7.17%"), class = "factor")), .Names = c("Symbol", 
"Date", "Time", "Profit"), class = "data.frame", row.names = c(NA, 
-12L))
$\endgroup$
  • 3
    $\begingroup$ Please fix your formatting and use dput() to created a minimal reproducable example. This is really a question for Stack Overflow though $\endgroup$ – scribbles Aug 20 '15 at 17:37
  • 1
    $\begingroup$ Tough one. I think this would be fine on SO, but as R is a key data science tool, I don't find it off-topic for this site either. It's in the overlap, IMHO. $\endgroup$ – Sean Owen Aug 21 '15 at 9:52
  • $\begingroup$ @SeanOwen fair enough. $\endgroup$ – scribbles Aug 21 '15 at 21:22
1
$\begingroup$

The fastest way would be,

require(data.table)
data <- data.table(data)
# Remove the percentage from your file and convert the field to numeric.
data[, Profit := as.numeric(gsub("%", "", Profit))]

data
##           Symbol      Date     Time Profit
##  1: BANKNIFTY  4/1/2010  9:55:00  -1.18
##  2: BANKNIFTY  4/1/2010 12:30:00  -2.84
##  3: BANKNIFTY  4/1/2010 12:45:00   7.17
##  4: BANKNIFTY  5/1/2010 11:40:00  -7.11
##  5:      ZEEL 26/6/2012 13:50:00  24.75
##  6:      ZEEL 27/6/2012 15:15:00  -1.90
##  7:      ZEEL 28/6/2012  9:45:00  37.58
##  8:      ZEEL 28/6/2012 14:55:00  23.95
##  9:      ZEEL 29/6/2012 14:20:00  -4.65
## 10:      ZEEL 29/6/2012 14:30:00  -6.01
## 11:      ZEEL 29/6/2012 14:55:00 -12.23
## 12:      ZEEL 29/6/2012 15:15:00  35.13

# Melt the data so that we can easily dcast afterwards.
molten_data <- melt(data[, list(Symbol, Date, Profit)]

# Create a summary by date and Symbol.
dcast(molten_data, id = c("Symbol", "Date")), Date ~ variable + Symbol, fun = sum)

 ##         Date Profit_BANKNIFTY Profit_ZEEL
 ## 1: 26/6/2012             0.00       24.75
 ## 2: 27/6/2012             0.00       -1.90
 ## 3: 28/6/2012             0.00       61.53
 ## 4: 29/6/2012             0.00       12.24
 ## 5:  4/1/2010             3.15        0.00
 ## 6:  5/1/2010            -7.11        0.00
$\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.