Max function problem

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

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.

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))

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
      
    }
  }
}

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")

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)))

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?

This may help you get a handle on it.

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