CODEWITHSUNDEEP
rdplyrdata.table
cephalopod
cephalopod

Reputation: 1906

multiply columns with similar names

The problem I am trying to solve is to multiply row-by-row the columns with similar names together. Consider the following sample df:

library(tidyverse)
library(lubridate)

sample_df <- data.frame(stringsAsFactors=FALSE,
                         Date = c("1/03/2018 0:00", "1/03/2018 4:00", "1/03/2018 8:00"),
                 EUR_USD_Open = c(0.892093896, 0.891937999, 0.891744285),
                 EUR_USD_High = c(0.89245654, 0.892283601, 0.892616906),
                  EUR_USD_Low = c(0.891803229, 0.89117644, 0.891483374),
                EUR_USD_Close = c(0.891942211, 0.891748495, 0.892405914),
                 USD_JPY_Open = c(1.887128916, 1.887340614, 1.887501691),
                 USD_JPY_High = c(1.887470444, 1.887677094, 1.887746746),
                  USD_JPY_Low = c(1.886890576, 1.887246812, 1.887167418),
                USD_JPY_Close = c(1.887338209, 1.887504095, 1.887210726),
                 USD_CHF_Open = c(0.997952231, 0.997969721, 0.997971242),
                 USD_CHF_High = c(0.99799974, 0.997989483, 0.998035047),
                  USD_CHF_Low = c(0.997949949, 0.997933211, 0.997968961),
                USD_CHF_Close = c(0.997970102, 0.997970862, 0.99799936),
                 USD_SEK_Open = c(1.092929855, 1.092928195, 1.092853491),
                 USD_SEK_High = c(1.092993997, 1.092943686, 1.093004716),
                  USD_SEK_Low = c(1.09291292, 1.092803475, 1.092767679),
                USD_SEK_Close = c(1.09292825, 1.09285338, 1.092896312),
                 USD_CAD_Open = c(1.022980632, 1.022990785, 1.022967577),
                 USD_CAD_High = c(1.023079216, 1.023053854, 1.02313861),
                  USD_CAD_Low = c(1.022959598, 1.022919695, 1.022958873),
                USD_CAD_Close = c(1.02299151, 1.022966852, 1.023073419),
                 GBP_USD_Open = c(0.962767254, 0.962746434, 0.962811407),
                 GBP_USD_High = c(0.96287142, 0.962841409, 0.962998227),
                  GBP_USD_Low = c(0.962725618, 0.962629918, 0.962640732),
                GBP_USD_Close = c(0.962747267, 0.962806408, 0.96284391)
             ) %>% 
    mutate(Date = dmy_hm(Date))

In this for each date, I want to multiply all the columns with Open, Close etc together.

The final output should look as follows:

output_df <- data.frame(stringsAsFactors=FALSE,
                     Date = c("1/03/2018 0:00", "1/03/2018 4:00", "1/03/2018 8:00"),
                     Open = c(1.808434992, 1.808329582, 1.808051308),
                     High = c(1.810060115, 1.80970432, 1.811075804),
                      Low = c(1.807469953, 1.806079386, 1.806720451),
                    Close = c(1.808339444, 1.808050604, 1.809484003)
             )%>% 
    mutate(Date = dmy_hm(Date))

Any ideas how to get this done efficiently?

Happy with either DT or Tidyverse solution.

Upvotes: 3

Views: 483

Answers (6)

s_baldur
s_baldur

Reputation: 33498

Another data.table alternative:

setDT(sample_df)
sample_df[, melt(.SD, id.vars = "Date")
          ][, prod(value), by = .(Date, substring(variable, 9, 13)) # Or tstrsplit(variable, "_")[[3]]
            ][, dcast(.SD, Date ~ substring, value.var = "V1")]

                  Date    Close     High      Low     Open
1: 2018-03-01 00:00:00 1.808339 1.810060 1.807470 1.808435
2: 2018-03-01 04:00:00 1.808051 1.809704 1.806079 1.808330
3: 2018-03-01 08:00:00 1.809484 1.811076 1.806720 1.808051

Upvotes: 2

NotAPhysicist
NotAPhysicist

Reputation: 57

What about using tempdf <- sample_df[grepl('Open', names(sample_df))] and then for (ii in 1:nrow(tempdf)) { sample_df$Open[[ii]] <- prod(tempdf[ii,])}

Definitely not the quickest or cleanest, but should get the job done.

Upvotes: 2

MichaelChirico
MichaelChirico

Reputation: 34703

You can try this in data.table:

setDT(sample_df)
sample_df[ , melt(.SD, id.vars = 'Date', variable.name = 'x',
                  measure.vars = patterns(Open = 'Open$', Close = 'Close$',
                                          High = 'High$', Low = 'Low$'))
           ][ , lapply(.SD, prod), by = Date, .SDcols = !'x']
#                   Date     Open    Close     High      Low
# 1: 2018-03-01 00:00:00 1.808435 1.808339 1.810060 1.807470
# 2: 2018-03-01 04:00:00 1.808330 1.808051 1.809704 1.806079
# 3: 2018-03-01 08:00:00 1.808051 1.809484 1.811076 1.806720

melt reshapes your data long; patterns in measure.vars will "stack" all the columns matching each pattern into a single column named as provided in patterns.

variable.name is just getting in the way here, so we rename it to x to make it more concise to exclude it in the next step (by default, it's named variable and we'd have to do .SDcols = !'variable'.

lapply(.SD, prod) does the multiplication -- within each Date, we want to multiply all the values together; this is exactly what prod does.

Without reshaping, your best bet is a loop-and-Reduce approach like:

out = data.table(Date = unique(sample_df$Date), key = 'Date')
cols = c('Open', 'Close', 'High', 'Low')
for (col in cols) {
  prod_dt = sample_df[ , .(Date, v = Reduce(`*`, .SD)), .SDcols = patterns(col)]
  # joins automatically since out is keyed
  out[prod_dt, (col) := i.v]
}

Upvotes: 3

fmarm
fmarm

Reputation: 4284

Not the quickest in terms of lines of code, but this works too

library(tidyverse)
sample_df %>% pivot_longer(-Date,"Type",'Value') %>% # convert to long format
              mutate(type_var=case_when(str_detect(Type, 'Open') ~ 'Open',
                                          str_detect(Type, 'Close') ~ 'Close',
                                          str_detect(Type, 'High') ~ 'High',
                                          str_detect(Type, 'Low') ~ 'Low',
                                          TRUE ~ 'Other')) %>% # identify type of value
              group_by(Date,type_var) %>%
              summarise(value=prod(value)) %>% # multiply all by group
              pivot_wider(id_cols='Date',names_from=type_var,values_from=value) # convert lines into columns

Upvotes: 2

G. Grothendieck
G. Grothendieck

Reputation: 269421

Convert to long form, separate the names into parts keeping only the third (last), perform the multplication and convert back to wide form.

library(dplyr)
library(tidyr)

sample_df %>%
  pivot_longer(-Date) %>%
  separate(name, c(NA, NA, "name")) %>%
  group_by(Date, name) %>%
  summarize(value = prod(value)) %>%
  ungroup %>%
  pivot_wider

giving:

# A tibble: 3 x 5
  Date                Close  High   Low  Open
  <dttm>              <dbl> <dbl> <dbl> <dbl>
1 2018-03-01 00:00:00  1.81  1.81  1.81  1.81
2 2018-03-01 04:00:00  1.81  1.81  1.81  1.81
3 2018-03-01 08:00:00  1.81  1.81  1.81  1.81

Upvotes: 2

Ronak Shah
Ronak Shah

Reputation: 388807

In base R, we can use split.default to split on similarity of the names

cbind(sample_df[1], sapply(split.default(sample_df[-1], 
           sub(".*_", "", names(sample_df)[-1])), Reduce, f = `*`))

#                 Date Close  High   Low  Open
#1 2018-03-01 00:00:00 1.808 1.810 1.807 1.808
#2 2018-03-01 04:00:00 1.808 1.810 1.806 1.808
#3 2018-03-01 08:00:00 1.809 1.811 1.807 1.808

Upvotes: 2

Related Questions