Max function problem

General
, ,
1

Hi everybody!

My database consists of a values column and a counter column. My goal is to take the values in the first column and transpose them to another db, to calculate the maximum, according to these rules:

  • the range of values to calculate the maximum is given by the counter column: the values with equal counter number will be a row in the new db;
  • the maximum of the wave values must be always in column 4 of the new db;
  • the new db must have 3 columns before the maximum and 3 columns after the maximum.

The database is below:
Database.pdf (24.7 KB)

The link to the excel version is here

Thanks

2

What have you done so far? Also, ideally you should supply data in your post in an R-readible format.

Have a look at how to create a reproducible example (reprex)

A handy way to supply sample data is to use the dput() function. See ?dput. If you have a very large data set then something like head(dput(myfile), 100) will likely supply enough data for us to work with.

3

This?

list(0.958637952804565, 1.10243439674377, 1.05450189113617, 1.05450260639191, 
    0.958638370037079, 0.982608795166016, 1.03053653240204, 0.958638727664948, 
    1.10243511199951, 7.93277168273926, 14.9068918228149, 2.82799935340881, 
    2.61229753494263, 308.059814453125, 6.15928316116333, 1.53382396697998, 
    1.07846760749817, 1.84538328647614, 1.53383386135101, 1.0065792798996, 
    1.10244357585907, 1.05450701713562, 1.34209549427032, 0.93467116355896, 
    0.910705864429474, 0.910706222057343, 0.982604801654816, 
    21.5215301513672, 2.87593150138855, 2.30074262619019, 7.81294059753418, 
    6.37497043609619, 4.38579416275024, 1.7255631685257, 1.0065792798996, 
    0.862774074077606, 1.00657081604004, 0, 0.862782835960388, 
    0.958638370037079, 1.03054535388947, 0.934681057929993, 1.0305460691452, 
    0.982613205909729, 0.958638370037079, 0.958647131919861, 
    0.934672296047211, 3.52300834655762, 14.2598123550415, 2.49247431755066, 
    3.23541450500488, 299.144470214844, 11.3599166870117, 1.3420946598053, 
    1.03054571151733, 1.36606991291046, 1.62969708442688, 0.982617199420929, 
    1.05450260639191, 0.886740148067474, 1.00657045841217, 0.934676647186279, 
    0.982613205909729, 0.934681415557861, 0.910706222057343, 
    23.3908843994141, 7.59723997116089, 3.23542332649231, 4.86511182785034, 
    7.42948436737061, 3.73871040344238, 4.12216806411743, 0.838812410831451, 
    0.958638370037079, 0.910706222057343, 0, 1.03053510189056, 
    1.0065690279007, 0.886748909950256, 0.958637595176697, 0.958642721176147, 
    1.00657963752747, 0.982613563537598, 0.910705089569092, 0.886740148067474, 
    1.82142519950867, 14.9308576583862, 2.97179579734802, 2.01314568519592, 
    303.650085449219, 12.917706489563, 1.53383350372314, 1.12640082836151, 
    2.01314568519592, 1.74951827526093, 0.934672296047211, 0.982604444026947, 
    1.0065701007843, 1.4619345664978, 1.00656974315643, 0.982604444026947, 
    0.934671521186829, 1.07847309112549, 25.236270904541, 7.98070096969604, 
    2.37264585494995, 4.26596355438232, 8.38812732696533, 4.43371915817261, 
    3.8106095790863, 0.814841568470001, 0.81484192609787, 0, 
    0.790875494480133, 0.934671938419342, 0.862774074077606, 
    0.862774074077606, 1.03053653240204, 0.982603669166565, 0.934671938419342, 
    0.862773716449738, 0.814851105213165, 0.886740148067474, 
    9.10710334777832, 13.4928979873657, 2.89989829063416, 1.94124794006348, 
    358.939819335938, 5.48823404312134, 1.12641072273254, 1.05450224876404, 
    2.22884845733643, 1.43795895576477, 1.05450260639191, 1.12640047073364, 
    1.03053653240204, 1.50985682010651, 1.03054535388947, 1.03054535388947, 
    1.10243475437164, 1.15036654472351, 21.7851581573486, 3.09162640571594, 
    2.46850872039795, 9.01124382019043, 3.81060481071472, 4.09820127487183, 
    1.17434132099152, 0.982614278793335, 1.05451285839081, 0.910714983940125, 
    0, 0.862782835960388, 0.862773716449738, 0.838808000087738, 
    0.814846336841583, 1.19829940795898, 1.17433190345764, 1.03054094314575, 
    1.05450296401978, 1.0065701007843, 4.31388854980469, 12.5342512130737, 
    2.08504796028137, 2.30074739456177, 308.155700683594, 12.7259798049927, 
    1.24624025821686, 1.10244393348694, 1.29417204856873, 1.70159518718719, 
    1.0065690279007, 1.31812942028046, 0.910705864429474, 1.07846868038177, 
    0.886748909950256, 0.910706222057343, 0.958638370037079, 
    1.10243511199951, 23.2231216430664, 3.47508311271667, 2.15694975852966, 
    4.74527835845947, 8.14846611022949, 4.67337989807129, 4.48165941238403, 
    1.07847785949707, 1.27020716667175, 1.15037715435028, 0, 
    0.83881676197052, 0.838808000087738, 0.958637952804565, 0.982613205909729, 
    0.982613563537598, 1.03053689002991, 1.12640118598938, 1.05450224876404, 
    1.03053617477417, 3.09162616729736, 13.4449615478516, 3.18749070167542, 
    2.06108593940735, 297.778411865234, 12.7259836196899, 1.43796956539154, 
    1.05450224876404, 1.50985825061798, 1.0545117855072, 0.838808000087738, 
    0.982604444026947, 1.05450260639191, 1.58176052570343, 0.862773716449738, 
    0.886739075183868, 0.934671938419342, 0.958637952804565, 
    22.2405128479004, 5.32046365737915, 3.71474456787109, 5.63202333450317, 
    6.49480152130127, 1.31812906265259, 3.93043088912964, 1.00657486915588, 
    1.00657081604004, 0.934671938419342, 0, 0.862774431705475, 
    0.862773358821869, 0.886744558811188, 0.838808000087738, 
    0.83880615234375, 0.862773358821869, 1.05450189113617, 0.982604801654816, 
    1.0065701007843, 14.1399803161621, 8.57984924316406, 2.20487475395203, 
    73.5518646240234, 304.4169921875, 3.64284634590149, 1.27020633220673, 
    1.10243403911591, 2.08504366874695, 1.34209549427032, 1.24623155593872, 
    1.15036725997925, 1.94124746322632, 1.17433190345764, 0.982604026794434, 
    0.910706222057343, 0.934672296047211, 3.85853362083435, 25.4759311676025, 
    2.85196733474731, 2.37264394760132, 7.88483858108521, 2.5883309841156, 
    4.36182832717896, 1.10243511199951, 1.07846796512604, 1.05450224876404, 
    0, 1.03053545951843, 0.838817119598389, 0.814850687980652, 
    0.814851462841034, 0.814841210842133, 0.790875494480133, 
    0.838808000087738, 0.838808000087738, 0.886749267578125, 
    1.05451142787933, 9.37072944641113, 12.0789003372192, 3.04368662834167, 
    2.20488238334656, 312.445648193359, 5.67995405197144, 1.10243916511536, 
    1.17434132099152, 1.70159447193146, 1.46192538738251, 1.12640047073364, 
    1.12641036510468, 1.05451214313507, 1.48590099811554, 1.12640082836151, 
    1.22226512432098, 1.12640118598938, 1.17434322834015, 24.4693565368652, 
    4.00233840942383, 2.78005957603455, 7.62120962142944, 2.99576210975647, 
    4.21802425384521, 1.31812977790833, 1.41399359703064, 1.27019727230072, 
    1.19829833507538, 0, 0.934682130813599, 0.958637952804565, 
    0.934670805931091, 1.00658071041107, 1.05450260639191, 1.03053653240204, 
    1.00657045841217, 1.03053545951843, 0.982604026794434, 2.68420362472534, 
    16.033296585083, 2.66023683547974, 1.98917889595032, 292.074462890625, 
    16.9440078735352, 1.50986707210541, 1.05450260639191, 1.67762875556946, 
    1.98917961120605, 0.958642721176147, 1.05450224876404, 1.00657081604004, 
    1.27019762992859, 0.958638727664948, 0.958638370037079, 0.934671521186829, 
    0.910706222057343, 21.0901412963867, 4.17009258270264, 2.1569516658783, 
    5.87169027328491, 7.21378946304321, 3.13955903053284, 4.36182832717896, 
    1.03053653240204, 0.934671938419342, 0.886739432811737, 0, 
    0.790875852108002, 0.838812410831451, 0.838817119598389, 
    0.886749625205994, 0.958647489547729, 0.814850687980652, 
    0.838817894458771, 0.814851820468903, 0.862774074077606, 
    2.5403995513916, 12.3664884567261, 3.40318584442139, 2.51644110679626, 
    281.241821289063, 13.6127243041992, 1.60572147369385, 1.15036582946777, 
    1.70158684253693, 1.84539198875427, 0.982604444026947, 1.03054094314575, 
    0.862773001194, 1.27019691467285, 0.910706222057343, 0.81484192609787, 
    0.814846336841583, 1.03054571151733, 26.0750827789307, 4.19405841827393, 
    2.25281381607056, 4.96097946166992, 7.93277168273926, 4.09819412231445, 
    4.07423639297485, 0.790885031223297, 0.982604444026947, 0.910706222057343, 
    0, 0.814851105213165, 0.886739790439606, 0.910705864429474, 
    0.910706222057343, 0.910706222057343, 0.862774074077606, 
    0.886749267578125, 0.862774074077606, 0.862774074077606, 
    14.9548234939575, 10.9764566421509, 2.06107783317566, 168.337646484375, 
    346.477416992188, 2.94783091545105, 1.19830822944641, 1.05450224876404, 
    1.70158684253693, 1.2222695350647, 0.958637595176697, 1.15036654472351, 
    1.58176457881927, 1.22226178646088, 1.00657045841217, 0.958638370037079, 
    0.862774074077606, 3.93043184280396, 20.8744468688965, 2.87592267990112, 
    2.39661049842834, 9.01124382019043, 2.37263631820679, 3.78663468360901, 
    0.982604801654816, 0.958637952804565, 0.910705864429474, 
    0.934672296047211, 0, 0.958638727664948, 0.958638727664948, 
    1.05450189113617, 0.862774074077606, 0.814846336841583, 0.83881676197052, 
    0.86278361082077, 0.910710573196411, 1.12640082836151, 1.82141757011414, 
    14.355676651001, 2.51643323898315, 2.8759241104126, 293.488464355469, 
    8.19639778137207, 1.39002680778503, 0.982613921165466, 1.43796002864838, 
    1.53382396697998, 0.934681415557861, 1.24623954296112, 1.07846760749817, 
    1.19829905033112, 0.934671521186829, 0.862773001194, 0.910705864429474, 
    0.958637952804565, 21.4975643157959, 3.49904131889343, 2.44454336166382, 
    5.12874174118042, 8.12450122833252, 3.83457493782043, 3.97837138175964, 
    0.862773716449738, 0.886739790439606, 0.766918957233429, 
    0, Counter = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
    1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
    2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
    3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
    3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 
    4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 
    4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 
    5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 
    5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 
    6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 
    6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 
    7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 
    7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 
    8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 
    8, 8, 8, 8, 8, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 
    9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 
    9, 9, 9, 9, 9, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 
    10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 
    10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 11, 11, 11, 
    11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 
    11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 
    11, 11, 11, 11, 11, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 
    12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 
    12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12))
4

I tried this function, but it doesn't work because it write only the first max value in the new db

db_new <- 1
db_new <- as.data.frame(db_new)
func <- function(database){

  for (i in 1:nrow(database)) {
    while (database[i,8] < database[i+1, 8]){
      maxx<- max(database[1:i,3])
      db_new$MAX <<- maxx
      
    }
  }
}
5

Almost but your copy missed some of the output. There is a bit missing at the top and at the bottom.

Here is an small example of what it should look like:

> dput(head(iris, 10))
structure(list(Sepal.Length = c(5.1, 4.9, 4.7, 4.6, 5, 5.4, 4.6, 
5, 4.4, 4.9), Sepal.Width = c(3.5, 3, 3.2, 3.1, 3.6, 3.9, 3.4, 
3.4, 2.9, 3.1), Petal.Length = c(1.4, 1.4, 1.3, 1.5, 1.4, 1.7, 
1.4, 1.5, 1.4, 1.5), Petal.Width = c(0.2, 0.2, 0.2, 0.2, 0.2, 
0.4, 0.3, 0.2, 0.2, 0.1), Species = structure(c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L), .Label = c("setosa", "versicolor", "virginica"
), class = "factor")), row.names = c(NA, 10L), class = "data.frame")
6

containing what information ?

Here is a start.

library(tidyverse)

#fake some data 
set.seed(42)
(example_data <- tibble(
  value = rnorm(1000),
  count = sample.int(10,size=1000,replace=TRUE)
) %>% arrange(count))

#get the per count group maximums
(result <-group_by(example_data,
                   count) %>% summarise(max=max(value)))
7

Ok, it works for a piece of the problem now I try to solve it. Thanks!

I have a question, what does the %>% command mean? When is it used?

8

This may help you get a handle on it.
https://blog.revolutionanalytics.com/2014/07/magrittr-simplifying-r-code-with-pipes.html

9

It is very useful to me, thank you very much!