Create new_df using selected strings of old_df and average values based on spefic column

General
1

Hello R Forum,

I want to create a new_df from the old_df based on the strings in the 3rd column. The new_df should only consist of rows from the old_df that have the following strings in the "file" column:

F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200
F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201
F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202

I know you could work with the ID here...but I want the new_df to be generated using a function that works with the 3rd column. From the new_df I would like to determine then rows with identical value of the column "hour.inc" and calculate for all associated columns the average value. Again for explanation with some photos:

  1. Create new_df using the selected strings in column file of old_df
  2. Calculate in new_df using identical "hour.inc" the mean value for all columns (shown here only for columns hopur.inc = 0)

Average Data
Average Data1920×1724 476 KB

#and the data

structure(list(date = structure(c(1575244800, 1575244800, 1575244800,
1575244800, 1575244800, 1575244800, 1575244800, 1575244800, 1575248400,
1575248400, 1575248400, 1575248400, 1575248400, 1575248400, 1575248400,
1575248400, 1575252000, 1575252000, 1575252000, 1575252000, 1575252000,
1575252000, 1575252000, 1575252000, 1575255600, 1575255600, 1575255600,
1575255600, 1575255600, 1575255600, 1575255600, 1575255600, 1575259200,
1575259200, 1575259200, 1575259200, 1575259200, 1575259200, 1575259200,
1575259200, 1575262800, 1575262800, 1575262800, 1575262800, 1575262800,
1575262800, 1575262800, 1575262800), tzone = "UTC", class = c("POSIXct",
"POSIXt")), date_utc = structure(c(1575244800, 1575241200, 1575237600,
1575234000, 1575230400, 1575226800, 1575223200, 1575219600, 1575248400,
1575244800, 1575241200, 1575237600, 1575234000, 1575230400, 1575226800,
1575223200, 1575252000, 1575248400, 1575244800, 1575241200, 1575237600,
1575234000, 1575230400, 1575226800, 1575255600, 1575252000, 1575248400,
1575244800, 1575241200, 1575237600, 1575234000, 1575230400, 1575259200,
1575255600, 1575252000, 1575248400, 1575244800, 1575241200, 1575237600,
1575234000, 1575262800, 1575259200, 1575255600, 1575252000, 1575248400,
1575244800, 1575241200, 1575237600), tzone = "UTC", class = c("POSIXct",
"POSIXt")), file = c("F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
"F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205"), year = c(19,
19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19,
19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19,
19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19),
month = c(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, 12, 12, 12, 12,
12, 12, 12, 12, 12, 12), day = c(2, 1, 1, 1, 1, 1, 1, 1,
2, 2, 1, 1, 1, 1, 1, 1, 2, 2, 2, 1, 1, 1, 1, 1, 2, 2, 2,
2, 1, 1, 1, 1, 2, 2, 2, 2, 2, 1, 1, 1, 2, 2, 2, 2, 2, 2,
1, 1), hour = c(0, 23, 22, 21, 20, 19, 18, 17, 1, 0, 23,
22, 21, 20, 19, 18, 2, 1, 0, 23, 22, 21, 20, 19, 3, 2, 1,
0, 23, 22, 21, 20, 4, 3, 2, 1, 0, 23, 22, 21, 5, 4, 3, 2,
1, 0, 23, 22), min = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), hour.inc = c(0,
-1, -2, -3, -4, -5, -6, -7, 0, -1, -2, -3, -4, -5, -6, -7,
0, -1, -2, -3, -4, -5, -6, -7, 0, -1, -2, -3, -4, -5, -6,
-7, 0, -1, -2, -3, -4, -5, -6, -7, 0, -1, -2, -3, -4, -5,
-6, -7), lat = c(36.256, 36.535, 36.833, 37.131, 37.427,
37.747, 38.067, 38.371, 36.256, 36.513, 36.801, 37.095, 37.377,
37.685, 38.025, 38.324, 36.256, 36.481, 36.737, 37.024, 37.3,
37.577, 37.922, 38.247, 36.256, 36.451, 36.657, 36.909, 37.178,
37.423, 37.704, 38.065, 36.256, 36.422, 36.581, 36.786, 37.021,
37.238, 37.44, 37.708, 36.256, 36.4, 36.532, 36.713, 36.912,
37.061, 37.185, 37.351), lon = c(117.106, 116.821, 116.596,
116.385, 116.132, 115.868, 115.596, 115.33, 117.106, 116.817,
116.566, 116.35, 116.094, 115.83, 115.557, 115.323, 117.106,
116.845, 116.584, 116.367, 116.138, 115.88, 115.621, 115.349,
117.106, 116.874, 116.615, 116.38, 116.182, 115.947, 115.714,
115.466, 117.106, 116.873, 116.631, 116.363, 116.146, 115.954,
115.732, 115.54, 117.106, 116.84, 116.591, 116.29, 115.985,
115.737, 115.481, 115.23), height = c(1250, 1313, 1336.7,
1356.1, 1391, 1416.7, 1365, 1283.5, 1250, 1302.1, 1356.5,
1366.9, 1398.6, 1431, 1418.4, 1293, 1250, 1293.8, 1362.7,
1372.1, 1393.1, 1423.8, 1493.3, 1376.2, 1250, 1291.4, 1352.2,
1367.6, 1374.2, 1402.8, 1459.8, 1487, 1250, 1282.2, 1318.1,
1355.9, 1365.4, 1390.7, 1425.4, 1535.4, 1250, 1271.7, 1295.6,
1360.2, 1413.3, 1440.1, 1484.4, 1554.9), pressure = c(849.2,
859.5, 866.8, 865.8, 861.2, 860.1, 861.5, 879.5, 849.6, 861,
863.7, 864.7, 860.4, 858.3, 854, 878, 849.9, 858.8, 863.1,
864.1, 861.4, 858, 846.9, 863.8, 850.3, 856.2, 865, 863.7,
863.8, 860, 856.2, 846.1, 849.6, 856.7, 868.6, 865.3, 864.2,
862, 857.7, 848.1, 848.9, 858.7, 871.3, 865.9, 858.6, 856.2,
851.8, 843), theta = c(276.2, 275.5, 275, 274.8, 275, 275,
275.4, 275.3, 276.2, 275.6, 275.2, 274.9, 275, 275, 275.3,
275.2, 276.2, 275.7, 275.3, 275.1, 275, 275, 275.1, 275.3,
276.2, 275.9, 275.2, 275.2, 274.9, 275.1, 274.8, 274.9, 276.4,
275.9, 275.1, 275, 275.1, 275, 275.1, 274.9, 276.5, 275.9,
275, 274.9, 275.1, 274.9, 274.6, 274.6), airtemp = c(263.6,
263.8, 264, 263.7, 263.5, 263.4, 263.9, 265.4, 263.6, 264,
263.9, 263.7, 263.4, 263.2, 263.1, 265.1, 263.7, 264, 264,
263.8, 263.5, 263.2, 262.3, 264, 263.7, 263.9, 264, 263.9,
263.6, 263.5, 262.9, 262.1, 263.8, 263.9, 264.2, 263.9, 263.8,
263.5, 263.3, 262.2, 263.9, 264.1, 264.4, 263.8, 263.4, 263,
262.3, 261.5), rainfall = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
mixdepth = c(81.6, 186.2, 193.5, 177.6, 68.7, 54.2, 113.5,
489.4, 354.8, 173.4, 157.2, 141.4, 60.4, 43.4, 56.4, 378.6,
633.2, 359.9, 136.5, 116.1, 67.5, 28, 36.6, 265, 911.5, 557.2,
317.4, 94.5, 73.3, 46.1, 16.4, 53.9, 1047.3, 758.4, 491.3,
270.6, 59.5, 36.7, 33.2, 16.4, 1180.7, 959.2, 646.9, 402.8,
263.6, 28.7, 38.8, 38.9), relhumid = c(42.4, 40.4, 34.6,
36.2, 32.5, 27.7, 23.8, 25.2, 35.7, 40.2, 30.9, 31.4, 32.5,
24.8, 22.2, 20.9, 28.9, 35.7, 29.1, 26.4, 32, 23.6, 22.1,
23.5, 22, 30.6, 31.5, 24.3, 27.4, 28.6, 19.9, 24.7, 24.4,
25.7, 30.6, 28.4, 23.6, 30.6, 30.8, 22.7, 26.9, 28.6, 27.2,
28.7, 31.3, 36.6, 39.3, 37), spchumid = c(0.9, 0.9, 0.8,
0.8, 0.7, 0.6, 0.5, 0.6, 0.8, 0.9, 0.7, 0.7, 0.7, 0.5, 0.5,
0.5, 0.6, 0.8, 0.6, 0.6, 0.7, 0.5, 0.4, 0.5, 0.5, 0.7, 0.7,
0.5, 0.6, 0.6, 0.4, 0.5, 0.5, 0.6, 0.7, 0.6, 0.5, 0.7, 0.7,
0.4, 0.6, 0.6, 0.6, 0.6, 0.7, 0.8, 0.8, 0.7), h2omixra = c(0.9,
0.9, 0.8, 0.8, 0.7, 0.6, 0.5, 0.6, 0.8, 0.9, 0.7, 0.7, 0.7,
0.5, 0.5, 0.5, 0.6, 0.8, 0.6, 0.6, 0.7, 0.5, 0.4, 0.5, 0.5,
0.7, 0.7, 0.5, 0.6, 0.6, 0.4, 0.5, 0.5, 0.6, 0.7, 0.6, 0.5,
0.7, 0.7, 0.4, 0.6, 0.6, 0.6, 0.6, 0.7, 0.8, 0.8, 0.7), terrain = c(280.5,
118.5, 23.4, 23.4, 23.4, 23.4, 23.4, 23.4, 280.5, 125.2,
23.4, 23.4, 23.4, 23.4, 23.4, 23.4, 280.5, 152, 24.5, 23.4,
23.4, 23.4, 23.4, 23.4, 280.5, 176.3, 34.3, 23.4, 23.4, 23.4,
23.4, 23.4, 280.5, 181.4, 46.2, 23.4, 23.4, 23.4, 23.4, 23.4,
280.5, 166.2, 43.7, 23.4, 23.4, 23.4, 23.4, 23.4), sunflux = c(7,
4.7, 2.3, 0, 0, 0, 0, 0, 120.7, 7, 4.7, 2.3, 0, 0, 0, 0,
236.3, 117.5, 7, 4.7, 2.3, 0, 0, 0, 351.9, 228.7, 116.4,
7, 4.7, 2.1, 0, 0, 382, 340.5, 226.8, 114.7, 7, 4.2, 1, 0,
410.7, 371.3, 337.3, 222.5, 114.6, 5.9, 2, 1)), row.names = c(NA,
-48L), class = c("tbl_df", "tbl", "data.frame"))

Many thanks,
Locardas

2

Is this the calculations you want to make?

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(stringr)
oldDF <- structure(list(date = structure(c(1575244800, 1575244800, 1575244800,
                                           1575244800, 1575244800, 1575244800, 1575244800, 1575244800, 1575248400,
                                           1575248400, 1575248400, 1575248400, 1575248400, 1575248400, 1575248400,
                                           1575248400, 1575252000, 1575252000, 1575252000, 1575252000, 1575252000,
                                           1575252000, 1575252000, 1575252000, 1575255600, 1575255600, 1575255600,
                                           1575255600, 1575255600, 1575255600, 1575255600, 1575255600, 1575259200,
                                           1575259200, 1575259200, 1575259200, 1575259200, 1575259200, 1575259200,
                                           1575259200, 1575262800, 1575262800, 1575262800, 1575262800, 1575262800,
                                           1575262800, 1575262800, 1575262800), tzone = "UTC", class = c("POSIXct",
                                                                                                         "POSIXt")), 
                        date_utc = structure(c(1575244800, 1575241200, 1575237600,
                                               1575234000, 1575230400, 1575226800, 1575223200, 1575219600, 1575248400,
                                               1575244800, 1575241200, 1575237600, 1575234000, 1575230400, 1575226800,
                                               1575223200, 1575252000, 1575248400, 1575244800, 1575241200, 1575237600,
                                               1575234000, 1575230400, 1575226800, 1575255600, 1575252000, 1575248400,
                                               1575244800, 1575241200, 1575237600, 1575234000, 1575230400, 1575259200,
                                               1575255600, 1575252000, 1575248400, 1575244800, 1575241200, 1575237600,
                                               1575234000, 1575262800, 1575259200, 1575255600, 1575252000, 1575248400,
                                               1575244800, 1575241200, 1575237600), tzone = "UTC", class = c("POSIXct",
                                                                                                             "POSIXt")), 
                        file = c("F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120203", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120204", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205", "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205",
                                 "F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120205"), 
                        year = c(19,
                                 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19,
                                 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19,
                                 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19, 19),
                        
                        month = c(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, 12, 12, 12, 12,
                                  12, 12, 12, 12, 12, 12), 
                        day = c(2, 1, 1, 1, 1, 1, 1, 1,
                                2, 2, 1, 1, 1, 1, 1, 1, 2, 2, 2, 1, 1, 1, 1, 1, 2, 2, 2,
                                2, 1, 1, 1, 1, 2, 2, 2, 2, 2, 1, 1, 1, 2, 2, 2, 2, 2, 2,
                                1, 1), 
                        hour = c(0, 23, 22, 21, 20, 19, 18, 17, 1, 0, 23,
                                 22, 21, 20, 19, 18, 2, 1, 0, 23, 22, 21, 20, 19, 3, 2, 1,
                                 0, 23, 22, 21, 20, 4, 3, 2, 1, 0, 23, 22, 21, 5, 4, 3, 2,
                                 1, 0, 23, 22), 
                        min = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
                        hour.inc = c(0,
                                     -1, -2, -3, -4, -5, -6, -7, 0, -1, -2, -3, -4, -5, -6, -7,
                                     0, -1, -2, -3, -4, -5, -6, -7, 0, -1, -2, -3, -4, -5, -6,
                                     -7, 0, -1, -2, -3, -4, -5, -6, -7, 0, -1, -2, -3, -4, -5,
                                     -6, -7), 
                        lat = c(36.256, 36.535, 36.833, 37.131, 37.427,
                                37.747, 38.067, 38.371, 36.256, 36.513, 36.801, 37.095, 37.377,
                                37.685, 38.025, 38.324, 36.256, 36.481, 36.737, 37.024, 37.3,
                                37.577, 37.922, 38.247, 36.256, 36.451, 36.657, 36.909, 37.178,
                                37.423, 37.704, 38.065, 36.256, 36.422, 36.581, 36.786, 37.021,
                                37.238, 37.44, 37.708, 36.256, 36.4, 36.532, 36.713, 36.912,
                                37.061, 37.185, 37.351), 
                        lon = c(117.106, 116.821, 116.596,
                                116.385, 116.132, 115.868, 115.596, 115.33, 117.106, 116.817,
                                116.566, 116.35, 116.094, 115.83, 115.557, 115.323, 117.106,
                                116.845, 116.584, 116.367, 116.138, 115.88, 115.621, 115.349,
                                117.106, 116.874, 116.615, 116.38, 116.182, 115.947, 115.714,
                                115.466, 117.106, 116.873, 116.631, 116.363, 116.146, 115.954,
                                115.732, 115.54, 117.106, 116.84, 116.591, 116.29, 115.985,
                                115.737, 115.481, 115.23), 
                        height = c(1250, 1313, 1336.7,
                                   1356.1, 1391, 1416.7, 1365, 1283.5, 1250, 1302.1, 1356.5,
                                   1366.9, 1398.6, 1431, 1418.4, 1293, 1250, 1293.8, 1362.7,
                                   1372.1, 1393.1, 1423.8, 1493.3, 1376.2, 1250, 1291.4, 1352.2,
                                   1367.6, 1374.2, 1402.8, 1459.8, 1487, 1250, 1282.2, 1318.1,
                                   1355.9, 1365.4, 1390.7, 1425.4, 1535.4, 1250, 1271.7, 1295.6,
                                   1360.2, 1413.3, 1440.1, 1484.4, 1554.9), 
                        pressure = c(849.2,
                                     859.5, 866.8, 865.8, 861.2, 860.1, 861.5, 879.5, 849.6, 861,
                                     863.7, 864.7, 860.4, 858.3, 854, 878, 849.9, 858.8, 863.1,
                                     864.1, 861.4, 858, 846.9, 863.8, 850.3, 856.2, 865, 863.7,
                                     863.8, 860, 856.2, 846.1, 849.6, 856.7, 868.6, 865.3, 864.2,
                                     862, 857.7, 848.1, 848.9, 858.7, 871.3, 865.9, 858.6, 856.2,
                                     851.8, 843), 
                        theta = c(276.2, 275.5, 275, 274.8, 275, 275,
                                  275.4, 275.3, 276.2, 275.6, 275.2, 274.9, 275, 275, 275.3,
                                  275.2, 276.2, 275.7, 275.3, 275.1, 275, 275, 275.1, 275.3,
                                  276.2, 275.9, 275.2, 275.2, 274.9, 275.1, 274.8, 274.9, 276.4,
                                  275.9, 275.1, 275, 275.1, 275, 275.1, 274.9, 276.5, 275.9,
                                  275, 274.9, 275.1, 274.9, 274.6, 274.6), 
                        airtemp = c(263.6,
                                    263.8, 264, 263.7, 263.5, 263.4, 263.9, 265.4, 263.6, 264,
                                    263.9, 263.7, 263.4, 263.2, 263.1, 265.1, 263.7, 264, 264,
                                    263.8, 263.5, 263.2, 262.3, 264, 263.7, 263.9, 264, 263.9,
                                    263.6, 263.5, 262.9, 262.1, 263.8, 263.9, 264.2, 263.9, 263.8,
                                    263.5, 263.3, 262.2, 263.9, 264.1, 264.4, 263.8, 263.4, 263,
                                    262.3, 261.5), 
                        rainfall = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
                                     0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
                        
                        mixdepth = c(81.6, 186.2, 193.5, 177.6, 68.7, 54.2, 113.5,
                                     489.4, 354.8, 173.4, 157.2, 141.4, 60.4, 43.4, 56.4, 378.6,
                                     633.2, 359.9, 136.5, 116.1, 67.5, 28, 36.6, 265, 911.5, 557.2,
                                     317.4, 94.5, 73.3, 46.1, 16.4, 53.9, 1047.3, 758.4, 491.3,
                                     270.6, 59.5, 36.7, 33.2, 16.4, 1180.7, 959.2, 646.9, 402.8,
                                     263.6, 28.7, 38.8, 38.9), 
                        relhumid = c(42.4, 40.4, 34.6,
                                     36.2, 32.5, 27.7, 23.8, 25.2, 35.7, 40.2, 30.9, 31.4, 32.5,
                                     24.8, 22.2, 20.9, 28.9, 35.7, 29.1, 26.4, 32, 23.6, 22.1,
                                     23.5, 22, 30.6, 31.5, 24.3, 27.4, 28.6, 19.9, 24.7, 24.4,
                                     25.7, 30.6, 28.4, 23.6, 30.6, 30.8, 22.7, 26.9, 28.6, 27.2,
                                     28.7, 31.3, 36.6, 39.3, 37), 
                        spchumid = c(0.9, 0.9, 0.8,
                                     0.8, 0.7, 0.6, 0.5, 0.6, 0.8, 0.9, 0.7, 0.7, 0.7, 0.5, 0.5,
                                     0.5, 0.6, 0.8, 0.6, 0.6, 0.7, 0.5, 0.4, 0.5, 0.5, 0.7, 0.7,
                                     0.5, 0.6, 0.6, 0.4, 0.5, 0.5, 0.6, 0.7, 0.6, 0.5, 0.7, 0.7,
                                     0.4, 0.6, 0.6, 0.6, 0.6, 0.7, 0.8, 0.8, 0.7), 
                        h2omixra = c(0.9,
                                     0.9, 0.8, 0.8, 0.7, 0.6, 0.5, 0.6, 0.8, 0.9, 0.7, 0.7, 0.7,
                                     0.5, 0.5, 0.5, 0.6, 0.8, 0.6, 0.6, 0.7, 0.5, 0.4, 0.5, 0.5,
                                     0.7, 0.7, 0.5, 0.6, 0.6, 0.4, 0.5, 0.5, 0.6, 0.7, 0.6, 0.5,
                                     0.7, 0.7, 0.4, 0.6, 0.6, 0.6, 0.6, 0.7, 0.8, 0.8, 0.7), 
                        terrain = c(280.5,
                                    118.5, 23.4, 23.4, 23.4, 23.4, 23.4, 23.4, 280.5, 125.2,
                                    23.4, 23.4, 23.4, 23.4, 23.4, 23.4, 280.5, 152, 24.5, 23.4,
                                    23.4, 23.4, 23.4, 23.4, 280.5, 176.3, 34.3, 23.4, 23.4, 23.4,
                                    23.4, 23.4, 280.5, 181.4, 46.2, 23.4, 23.4, 23.4, 23.4, 23.4,
                                    280.5, 166.2, 43.7, 23.4, 23.4, 23.4, 23.4, 23.4), 
                        sunflux = c(7,
                                    4.7, 2.3, 0, 0, 0, 0, 0, 120.7, 7, 4.7, 2.3, 0, 0, 0, 0,
                                    236.3, 117.5, 7, 4.7, 2.3, 0, 0, 0, 351.9, 228.7, 116.4,
                                    7, 4.7, 2.1, 0, 0, 382, 340.5, 226.8, 114.7, 7, 4.2, 1, 0,
                                    410.7, 371.3, 337.3, 222.5, 114.6, 5.9, 2, 1)), 
                   row.names = c(NA,-48L), class = c("tbl_df", "tbl", "data.frame"))

newDF <- oldDF |> filter(str_detect(file,"Tai19120200|Tai19120201|Tai19120202"))
Summary <- newDF |> group_by(file,hour.inc) |> 
  summarize(across(.cols = lat:sunflux,.fns = mean))
#> `summarise()` has grouped output by 'file'. You can override using the `.groups` argument.
Summary
#> # A tibble: 24 x 15
#> # Groups:   file [3]
#>    file     hour.inc   lat   lon height pressure theta airtemp rainfall mixdepth
#>    <chr>       <dbl> <dbl> <dbl>  <dbl>    <dbl> <dbl>   <dbl>    <dbl>    <dbl>
#>  1 F:/Carl~       -7  38.4  115.  1284.     880.  275.    265.        0    489. 
#>  2 F:/Carl~       -6  38.1  116.  1365      862.  275.    264.        0    114. 
#>  3 F:/Carl~       -5  37.7  116.  1417.     860.  275     263.        0     54.2
#>  4 F:/Carl~       -4  37.4  116.  1391      861.  275     264.        0     68.7
#>  5 F:/Carl~       -3  37.1  116.  1356.     866.  275.    264.        0    178. 
#>  6 F:/Carl~       -2  36.8  117.  1337.     867.  275     264         0    194. 
#>  7 F:/Carl~       -1  36.5  117.  1313      860.  276.    264.        0    186. 
#>  8 F:/Carl~        0  36.3  117.  1250      849.  276.    264.        0     81.6
#>  9 F:/Carl~       -7  38.3  115.  1293      878   275.    265.        0    379. 
#> 10 F:/Carl~       -6  38.0  116.  1418.     854   275.    263.        0     56.4
#> # ... with 14 more rows, and 5 more variables: relhumid <dbl>, spchumid <dbl>,
#> #   h2omixra <dbl>, terrain <dbl>, sunflux <dbl>

Created on 2022-02-12 by the reprex package (v2.0.1)

3

Hello FJCC,

the first part of the code works perfect.
But the second one doesn't work the way I thought it would.
Therefore I have again a photo with a more detailed illustration.

Average Data 1
Average Data 12470×802 112 KB

As you can see I want to create an "average of newDF" from the "newDF"
where for each row with the same hour.inc the average values are calculated.

Example: In the whole table 3 times the value -2 appears in the column hour.inc.
From all 3 rows the average value is to be calculated and written into a "final Df".
The same is to be done for all hour.inc values.

Best wishes,
Locardas

4

If you want the average of all of the columns to the right of hour.inc, use

Summary <- newDF |> group_by(hour.inc) |> 
  summarize(across(.cols = lat:sunflux,.fns = mean))

If you want to also get the average of the columns to the left of hour.inc, such as the date and year, use

Summary2 <- newDF |> group_by(hour.inc) |> 
  summarize(across(.cols = -file,.fns = mean))
5

Thank you i will remebr this for future codes.
As seen in the the graph above, i want to average all rows that show the same hour.inc value.
So if i have
ID......File...........................................................................................hour.inc.............remaingn columns..
1)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200............0.......................
2)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200...........-1.......................
3)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120200...........-2.......................
4)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201............0.......................
5)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201...........-1.......................
6)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120201............-2......................
7)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202.............0......................
8)F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202.............-1.....................
9F:/Carlo/R/R_Input/Hysplit_1250m/Mount_Tai19120202.............-2.....................

I want to calculate the calcualtete the average for:

hou.inc =0 ...means row 1,4 and 7
hou.inc =-1 ...means row 2,5 and 8
hou.inc =-2 ...means row 3,6 and 9

and so on... In the end it is a row specific average if the identical value appears in column hou.inc.

Best wishes,
Locardas

6

I think the code in my last post does what you want. If that is not true, please explain how it fails.

7

You are right and i have to apoligize !!
Sorry ! And thank you for your help!
I hope i will one day reach your R LvL!

Best wishes,
Locardas

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.