CODEWITHSUNDEEP
rgroup-bydata.tableaggregatesubtraction
Ayelavan
Ayelavan

Reputation: 993

subtracting values a in column in R based on values in another column

I have data that looks like the following

#    View  date    value1 Value2 
#     a  2012-10-01 21.01  2.00
#     b  2012-10-01 22.04  3.03
#     c  2012-10-01 22.65  7.61
#     a  2012-11-01 23.11  8.46
#     b  2012-11-01 35.21  9.00
#     c  2012-11-01 35.21  9.00

structure(list(View = c("a", "b", "c", "a", "b", "c"), date = c("2012-10-01", 
"2012-10-01", "2012-10-01", "2012-11-01", "2012-11-01", "2012-11-01"
), value1 = c(21.01, 22.04, 22.65, 23.11, 35.21, 35.21), Value2 = c(2, 
3.03, 7.61, 8.46, 9, 9)), .Names = c("View", "date", "value1", 
"Value2"), row.names = c(NA, -6L), class = "data.frame")

I want to create a new View "D" which is the subtraction of "a" from "c" for any given date. i.e end up with a dataset that looks like this?

#    View  date    value1 Value2 
#     a 2012-10-01 21.01  2.00
#     b 2012-10-01 22.04  3.03
#     c 2012-10-01 22.65  7.61
#     D 2012-10-01  1.61  5.61
#     a 2012-11-01 23.11  8.46
#     b 2012-11-01 35.21  9.00
#     c 2012-11-01 35.21  9.00
#     D 2012-10-01 12.1   0.54

I know a bit about R but I have no idea how I can approach this. Any suggestions would be greatly appreciated.

Upvotes: 1

Views: 789

Answers (1)

akuiper
akuiper

Reputation: 214957

You can rbind a new calculated row with .SD (sub data.table which comes from a unique date) after grouping your data.table by date:

df[, rbind(.SD, 
     .(View = "D", value1 = value1[View == "c"] - value1[View == "a"], 
                   Value2 = Value2[View == "c"] - Value2[View == "a"])), date]

#         date View value1 Value2
#1: 2012-10-01    a  21.01   2.00
#2: 2012-10-01    b  22.04   3.03
#3: 2012-10-01    c  22.65   7.61
#4: 2012-10-01    D   1.64   5.61
#5: 2012-11-01    a  23.11   8.46
#6: 2012-11-01    b  35.21   9.00
#7: 2012-11-01    c  35.21   9.00
#8: 2012-11-01    D  12.10   0.54

To avoid hard coding column names, but still assume you have date and View columns to manipulate:

# drop View column so that you can do subtraction
df[, rbind(.SD, { dt = .SD[, !"View", with = F];      
                 # subtract row c and row a and assign a new View column as D           
                 (dt[View == "c"] - dt[View == "a"])[, View := "D"][] }), date]

#         date View value1 Value2
#1: 2012-10-01    a  21.01   2.00
#2: 2012-10-01    b  22.04   3.03
#3: 2012-10-01    c  22.65   7.61
#4: 2012-10-01    D   1.64   5.61
#5: 2012-11-01    a  23.11   8.46
#6: 2012-11-01    b  35.21   9.00
#7: 2012-11-01    c  35.21   9.00
#8: 2012-11-01    D  12.10   0.54

Upvotes: 1

Related Questions