All geek questions in one place

Apply a function to all rows and columns in a data frame, except for the first column based on the value in the first column

Hello, and I hope I can explain this a bit simply. I know that this can be done with a loop, but it will take a lot of time, and I need this analysis to be performed as part of the web page, so some function of the application should work much better, I hope.

I have 2 data frames. Data frame A has a list of individual “anchors” and category values ​​for each of them (these are weighted average values ​​from ddply already executed).

anchor ecomax ecomin volume price runtime 1 9482 0.12981362 0.5714286 0.12981362 0.1324330 1.00000000 2 9488 0.01458662 0.5544864 0.01458662 0.2967270 0.04166667 3 9549 0.09734398 0.5721429 0.09734398 0.1219376 1.00000000 4 9574 0.00902656 0.5505136 0.00902656 0.1455307 0.14652568 5 9575 0.00902656 0.5505136 0.00902656 0.1460919 0.14652568 6 9576 0.07608863 0.5613563 0.07608863 0.1114813 1.00000000

Data frame B is a larger data frame with the same category values ​​(ignore the names for now), but there are several entries for each anchor.

  anchor ecomax_max_med ecomin_min_med volume_med price_med run_time_minimum_med 1 9482 0.12981362 0.5714286 0.12981362 0.1120882 1.00000000 2 9482 0.12981362 0.5714286 0.12981362 0.1686777 1.00000000 3 9488 0.01552049 0.5550000 0.01552049 0.2925363 0.04166667 4 9488 0.01292292 0.5535714 0.01292292 0.3041928 0.04166667 5 9549 0.09734398 0.5721429 0.09734398 0.1238916 1.00000000 6 9549 0.09734398 0.5721429 0.09734398 0.1184564 1.00000000

I want to subtract category values ​​for B from their facilities (Data Frame A) based on its anchored anchor; those. the first 2 lines of B (anchor 9482) will differ from the first row of A (average value of anchor 9482), the next 2 lines of B (anchor 9488) will differ from the next row of A (value of anchor 9488), and so on.

The end result is that each row / column (except the binding) of the new Data Frame C is the difference between the values ​​in Data Frame B and the corresponding associated tools (Data Frame A). Hope this is pretty straight forward; it can be easily done with a long cycle. I assume that this requires some combination of “match” or “by”, but I'm not sure, and it was very unpleasant. Help!

+4
source share
2 answers

Here is the data.table solution.

It works by merging A and B with an anchor (which is set as a key). Then it evaluates the expression e that we created as

list(ecomax_diff = ecomax_max_med - ecomax, ecomin_diff = ecomin_min_med - ecomin, volume_diff = volume_med - volume, price_diff = price_med - price, runtime_diff = run_time_minimum_med - runtime)

using mapply , sprintf and parse .

The solution depends on skipping the corresponding column names for each data.table to map.

 library(data.table) DA <- data.table(A) DB <- data.table(B) setkey(DA, 'anchor') setkey(DB, 'anchor') .calls <- mapply(sprintf, as.list(names(DA)[-1]), as.list(names(DB)[-1]), as.list(names(DA)[-1]), MoreArgs = list(fmt = '%s_diff = %s - %s')) .e <- parse(text = sprintf('list(%s)', paste(.calls, collapse =', '))) DA[DB, eval(.e)] ## anchor ecomax_diff ecomin_diff volume_diff price_diff runtime_diff ## 1: 9482 0.00000000 0.0000000 0.00000000 -0.0203448 0 ## 2: 9482 0.00000000 0.0000000 0.00000000 0.0362447 0 ## 3: 9488 0.00093387 0.0005136 0.00093387 -0.0041907 0 ## 4: 9488 -0.00166370 -0.0009150 -0.00166370 0.0074658 0 ## 5: 9549 0.00000000 0.0000000 0.00000000 0.0019540 0 ## 6: 9549 0.00000000 0.0000000 0.00000000 -0.0034812 0

The second, less effective, but possibly easier to use solution

  # calculate the difference between the respective columns (merged appropriately DIFF <- DB[, names(DB)[-1],with = F] - DA[DB][, names(DA)[-1], with = F] # combine with the anchor column from DB DC <- cbind(DB[,list(anchor)],DIFF) # rename with the names from A (otherwise they will have the same as DB setnames(DC, names(DA)) # It creates the correct output ! DC ## anchor ecomax ecomin volume price runtime ## 1: 9482 0.00000000 0.0000000 0.00000000 -0.0203448 0 ## 2: 9482 0.00000000 0.0000000 0.00000000 0.0362447 0 ## 3: 9488 0.00093387 0.0005136 0.00093387 -0.0041907 0 ## 4: 9488 -0.00166370 -0.0009150 -0.00166370 0.0074658 0 ## 5: 9549 0.00000000 0.0000000 0.00000000 0.0019540 0 ## 6: 9549 0.00000000 0.0000000 0.00000000 -0.0034812 0
  • Note. It might even become simple if -.data.table ignores character columns in future versions.
+2
source 
 datmer <- merge(datA, datB) str(datmer) #------------------ 'data.frame': 6 obs. of 11 variables: $ anchor : int 9482 9482 9488 9488 9549 9549 $ ecomax : num 0.1298 0.1298 0.0146 0.0146 0.0973 ... $ ecomin : num 0.571 0.571 0.554 0.554 0.572 ... $ volume : num 0.1298 0.1298 0.0146 0.0146 0.0973 ... $ price : num 0.132 0.132 0.297 0.297 0.122 ... $ runtime : num 1 1 0.0417 0.0417 1 ... $ ecomax_max_med : num 0.1298 0.1298 0.0155 0.0129 0.0973 ... $ ecomin_min_med : num 0.571 0.571 0.555 0.554 0.572 ... $ volume_med : num 0.1298 0.1298 0.0155 0.0129 0.0973 ... $ price_med : num 0.112 0.169 0.293 0.304 0.124 ... $ run_time_minimum_med: num 1 1 0.0417 0.0417 1 ... datmer2 <- cbind(datmer[,1, drop=FALSE], as.matrix(datmer[, 2:6]) - as.matrix(datmer[7:11]) ) datmer2 #-------- anchor ecomax ecomin volume price runtime 1 9482 0.00000000 0.0000000 0.00000000 0.0203448 0 2 9482 0.00000000 0.0000000 0.00000000 -0.0362447 0 3 9488 -0.00093387 -0.0005136 -0.00093387 0.0041907 0 4 9488 0.00166370 0.0009150 0.00166370 -0.0074658 0 5 9549 0.00000000 0.0000000 0.00000000 -0.0019540 0 6 9549 0.00000000 0.0000000 0.00000000 0.0034812 0

If you want to use the differences in the order in which @mnel did this (BA), you would also get the column names the same as the second data block:

  str( cbind(datmer[,1, drop=FALSE], as.matrix(datmer[7:11]) - as.matrix(datmer[2:6]) ) ) 'data.frame': 6 obs. of 6 variables: $ anchor : int 9482 9482 9488 9488 9549 9549 $ ecomax_max_med : num 0 0 0.000934 -0.001664 0 ... $ ecomin_min_med : num 0 0 0.000514 -0.000915 0 ... $ volume_med : num 0 0 0.000934 -0.001664 0 ... $ price_med : num -0.02034 0.03624 -0.00419 0.00747 0.00195 ... $ run_time_minimum_med: num 0 0 0 0 0 0
+1
source

Source: https://habr.com/ru/post/1433932/

More articles:


All Articles