Summarize daily flux into hour

mhab · February 10, 2021, 7:34pm

Hye,

Can someone help me.
I have this daily data which measured flux every 3-4 mins data.
I would like to summarize them into hourly data.
Example of data set is below:

Item#	datetime	Obs#	micro.form	Exp_Flux
1	31/08/2018 12:48	1	HP	6.12
2	31/08/2018 12:52	2	PB	2.89
3	31/08/2018 12:55	3	FP	3.22
4	31/08/2018 12:59	4	HP	10.07
5	31/08/2018 13:02	5	PB	5.87
6	31/08/2018 13:06	6	FP	2.95
7	31/08/2018 13:09	7	HP	4.27
8	31/08/2018 13:13	8	PB	2.27
9	31/08/2018 13:16	9	FP	3.77
10	31/08/2018 13:20	10	HP	10.71
11	31/08/2018 13:24	11	PB	6.15
12	31/08/2018 13:27	12	FP	3.18
13	31/08/2018 13:31	13	HP	1.3
14	31/08/2018 13:34	14	PB	2.61
15	31/08/2018 13:38	15	FP	3.32
16	31/08/2018 13:41	16	HP	4.87
17	31/08/2018 13:45	17	PB	3.19
18	31/08/2018 13:48	18	FP	3.09
19	31/08/2018 13:52	19	HP	2.55
20	31/08/2018 13:55	20	PB	1.68
21	31/08/2018 13:59	21	FP	2.74
22	31/08/2018 14:02	22	HP	1.55
23	31/08/2018 14:06	23	PB	5.13
24	31/08/2018 14:09	24	FP	3.63
25	31/08/2018 14:13	25	HP	6.69
26	31/08/2018 14:16	26	PB	2.08
27	31/08/2018 14:20	27	FP	2.87
28	31/08/2018 14:23	28	HP	4.54
29	31/08/2018 14:27	29	PB	4.83
30	31/08/2018 14:30	30	FP	3
31	31/08/2018 14:34	31	HP	3.8
32	31/08/2018 14:38	32	PB	3.25
33	31/08/2018 14:41	33	FP	2.34
34	31/08/2018 14:45	34	HP	8.34
35	31/08/2018 14:48	35	PB	4.99
36	31/08/2018 14:52	36	FP	3.19
37	31/08/2018 14:55	37	HP	3.01
38	31/08/2018 14:59	38	PB	5.31
39	31/08/2018 15:02	39	FP	3.22
40	31/08/2018 15:06	40	HP	5.4
41	31/08/2018 15:09	41	PB	4.1
42	31/08/2018 15:13	42	FP	3.15
43	31/08/2018 15:16	43	HP	3.97
44	31/08/2018 15:20	44	PB	5.04
45	31/08/2018 15:23	45	FP	3.01
46	31/08/2018 15:27	46	HP	0.81
47	31/08/2018 15:30	47	PB	5.32
48	31/08/2018 15:34	48	FP	3.58
49	31/08/2018 15:37	49	HP	2.36
50	31/08/2018 15:41	50	PB	6.93
51	31/08/2018 15:44	51	FP	3.49
52	31/08/2018 15:48	52	HP	1.68
53	31/08/2018 15:51	53	PB	6.39
54	31/08/2018 15:55	54	FP	3.06
55	31/08/2018 15:58	55	HP	1.51
56	31/08/2018 16:02	56	PB	3.4
57	31/08/2018 16:05	57	FP	2.69
58	31/08/2018 16:09	58	HP	1.78
59	31/08/2018 16:12	59	PB	3.89
60	31/08/2018 16:16	60	FP	3.02
61	31/08/2018 16:19	61	HP	1.38
62	31/08/2018 16:23	62	PB	3.3
63	31/08/2018 16:26	63	FP	2.86
64	31/08/2018 16:30	64	HP	3.78
65	31/08/2018 16:33	65	PB	3.23
66	31/08/2018 16:37	66	FP	3.44
67	31/08/2018 16:40	67	HP	2.51
68	31/08/2018 16:44	68	PB	3.77
69	31/08/2018 16:47	69	FP	3
70	31/08/2018 16:51	70	HP	1.99
71	31/08/2018 16:55	71	PB	6.69
72	31/08/2018 16:58	72	FP	3.04
73	31/08/2018 17:02	73	HP	2.21
74	31/08/2018 17:05	74	PB	5.1
75	31/08/2018 17:09	75	FP	3.01
76	31/08/2018 17:12	76	HP	0.96
77	31/08/2018 17:16	77	PB	5.33
78	31/08/2018 17:19	78	FP	3.08
79	31/08/2018 17:23	79	HP	1.32
80	31/08/2018 17:26	80	PB	4.82
81	31/08/2018 17:30	81	FP	3.28
82	31/08/2018 17:33	82	HP	1.73
83	31/08/2018 17:37	83	PB	4.69
84	31/08/2018 17:40	84	FP	3.53
85	31/08/2018 17:44	85	HP	3.58
86	31/08/2018 17:47	86	PB	4.89
87	31/08/2018 17:51	87	FP	3.39
88	31/08/2018 17:54	88	HP	1.93
89	31/08/2018 17:58	89	PB	5.43
91	31/08/2018 18:04	90	HP	3.77
92	31/08/2018 18:08	91	PB	8
93	31/08/2018 18:11	92	FP	3.46
94	31/08/2018 18:15	93	HP	2.2
95	31/08/2018 18:18	94	PB	5.45
96	31/08/2018 18:22	95	FP	3.86
97	31/08/2018 18:25	96	HP	2.79
98	31/08/2018 18:29	97	PB	5.36
99	31/08/2018 18:32	98	FP	3.45
100	31/08/2018 18:36	99	HP	2.49
101	31/08/2018 18:39	100	PB	6.09
102	31/08/2018 18:43	101	FP	3.82
103	31/08/2018 18:46	102	HP	3.58
104	31/08/2018 18:50	103	PB	6.77
105	31/08/2018 18:53	104	FP	4.18
106	31/08/2018 18:57	105	HP	1.29
107	31/08/2018 19:01	106	PB	6.44
108	31/08/2018 19:04	107	FP	3.76
109	31/08/2018 19:08	108	HP	2.98
110	31/08/2018 19:11	109	PB	5.94
111	31/08/2018 19:15	110	FP	4.28
112	31/08/2018 19:18	111	HP	1.89
113	31/08/2018 19:22	112	PB	6.14
114	31/08/2018 19:25	113	FP	4
115	31/08/2018 19:29	114	HP	3.29
116	31/08/2018 19:32	115	PB	8.02
117	31/08/2018 19:36	116	FP	3.79
118	31/08/2018 19:39	117	HP	2.21
119	31/08/2018 19:43	118	PB	6.72
120	31/08/2018 19:46	119	FP	3.63
121	31/08/2018 19:50	120	HP	2.2
122	31/08/2018 19:53	121	PB	4.85
123	31/08/2018 19:57	122	FP	4.09
124	31/08/2018 20:00	123	HP	3.39
125	31/08/2018 20:04	124	PB	5.45
126	31/08/2018 20:07	125	FP	4.17
127	31/08/2018 20:11	126	HP	1.87
128	31/08/2018 20:15	127	PB	5.3
129	31/08/2018 20:18	128	FP	3.7
130	31/08/2018 20:22	129	HP	3.26
131	31/08/2018 20:25	130	PB	5.71
132	31/08/2018 20:29	131	FP	4.16
133	31/08/2018 20:32	132	HP	3.23
134	31/08/2018 20:36	133	PB	5.41
135	31/08/2018 20:39	134	FP	4.01
136	31/08/2018 20:43	135	HP	1.96
137	31/08/2018 20:46	136	PB	5.2
138	31/08/2018 20:50	137	FP	3.95
139	31/08/2018 20:53	138	HP	1.86
140	31/08/2018 20:57	139	PB	5.42
141	31/08/2018 21:00	140	FP	4.44
142	31/08/2018 21:04	141	HP	2.54
143	31/08/2018 21:07	142	PB	7.3
144	31/08/2018 21:11	143	FP	4.3
145	31/08/2018 21:14	144	HP	3.55
146	31/08/2018 21:18	145	PB	5.7
147	31/08/2018 21:21	146	FP	4.51
148	31/08/2018 21:25	147	HP	2.58
149	31/08/2018 21:28	148	PB	5.86
150	31/08/2018 21:32	149	FP	4.31
151	31/08/2018 21:36	150	HP	2.05
152	31/08/2018 21:39	151	PB	5.02
153	31/08/2018 21:43	152	FP	5.08
154	31/08/2018 21:46	153	HP	3.67
155	31/08/2018 21:50	154	PB	5.24
156	31/08/2018 21:53	155	FP	4.07
157	31/08/2018 21:57	156	HP	2.6
158	31/08/2018 22:00	157	PB	5.3
159	31/08/2018 22:04	158	FP	4.4
160	31/08/2018 22:07	159	HP	2.02
161	31/08/2018 22:11	160	PB	5.43
162	31/08/2018 22:14	161	FP	4.68
163	31/08/2018 22:18	162	HP	2.59
164	31/08/2018 22:21	163	PB	4.87
165	31/08/2018 22:25	164	FP	4.41
166	31/08/2018 22:28	165	HP	1.91
167	31/08/2018 22:32	166	PB	4.77
168	31/08/2018 22:35	167	FP	4.76
169	31/08/2018 22:39	168	HP	2.9
170	31/08/2018 22:42	169	PB	5.83
171	31/08/2018 22:46	170	FP	5.19
172	31/08/2018 22:50	171	HP	2.17
173	31/08/2018 22:53	172	PB	5.87
174	31/08/2018 22:57	173	FP	4.2
175	31/08/2018 23:00	174	HP	2.14
176	31/08/2018 23:04	175	PB	6.11
177	31/08/2018 23:07	176	FP	4.92
178	31/08/2018 23:11	177	HP	2.79
179	31/08/2018 23:14	178	PB	5.24
180	31/08/2018 23:18	179	FP	5.79
181	31/08/2018 23:21	180	HP	2.11
182	31/08/2018 23:25	181	PB	6.07
183	31/08/2018 23:28	182	FP	4.92
184	31/08/2018 23:32	183	HP	2.85
185	31/08/2018 23:35	184	PB	6.49
186	31/08/2018 23:39	185	FP	4.28
187	31/08/2018 23:42	186	HP	2.01
188	31/08/2018 23:46	187	PB	5.2
189	31/08/2018 23:49	188	FP	4.86
190	31/08/2018 23:53	189	HP	2.2
191	31/08/2018 23:56	190	PB	4.54

lars · February 10, 2021, 8:40pm

Hi @mhab,

May I advise, for the next time, to provide a reproducible example?

FAQ: How to do a minimal reproducible example ( reprex ) for beginners Guides & FAQs

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

However, this time it was relatively easy to create a sample dataset from your example and hopefully the following code provides what you're looking for?

With mutate() I've added an extra column hour to the dataset and two examples of counting is provided.

library(tidyverse)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

df <- tibble::tribble(
        ~Item,          ~datetime, ~Obs, ~micro.form, ~Exp_Flux,
           1L, "31/08/2018 12:48",   1L,        "HP",      6.12,
           2L, "31/08/2018 12:52",   2L,        "PB",      2.89,
           3L, "31/08/2018 12:55",   3L,        "FP",      3.22,
           4L, "31/08/2018 12:59",   4L,        "HP",     10.07,
           5L, "31/08/2018 13:02",   5L,        "PB",      5.87,
           6L, "31/08/2018 13:06",   6L,        "FP",      2.95,
           7L, "31/08/2018 13:09",   7L,        "HP",      4.27,
           8L, "31/08/2018 13:13",   8L,        "PB",      2.27,
           9L, "31/08/2018 13:16",   9L,        "FP",      3.77,
           1L, "31/08/2018 13:20",  10L,        "HP",     10.71,
           1L, "31/08/2018 13:24",  11L,        "PB",      6.15,
           1L, "31/08/2018 13:27",  12L,        "FP",      3.18,
           1L, "31/08/2018 13:31",  13L,        "HP",       1.3,
           1L, "31/08/2018 13:34",  14L,        "PB",      2.61,
           1L, "31/08/2018 13:38",  15L,        "FP",      3.32,
           1L, "31/08/2018 13:41",  16L,        "HP",      4.87,
           1L, "31/08/2018 13:45",  17L,        "PB",      3.19,
           1L, "31/08/2018 13:48",  18L,        "FP",      3.09,
           1L, "31/08/2018 13:52",  19L,        "HP",      2.55,
           2L, "31/08/2018 13:55",  20L,        "PB",      1.68
        )

df <- df %>%
  mutate(datetime = dmy_hm(datetime),
         hour = hour(datetime)
  )
df %>% head()
#> # A tibble: 6 x 6
#>     Item datetime              Obs micro.form Exp_Flux  hour
#>    <int> <dttm>              <int> <chr>         <dbl> <int>
#>  1     1 2018-08-31 12:48:00     1 HP             6.12    12
#>  2     2 2018-08-31 12:52:00     2 PB             2.89    12
#>  3     3 2018-08-31 12:55:00     3 FP             3.22    12
#>  4     4 2018-08-31 12:59:00     4 HP            10.1     12
#>  5     5 2018-08-31 13:02:00     5 PB             5.87    13
#>  6     6 2018-08-31 13:06:00     6 FP             2.95    13

## count # of readings per hour per `micro.form` 
df %>% 
  count(hour, micro.form)
#> # A tibble: 6 x 3
#>    hour micro.form     n
#>   <int> <chr>      <int>
#> 1    12 FP             1
#> 2    12 HP             2
#> 3    12 PB             1
#> 4    13 FP             5
#> 5    13 HP             5
#> 6    13 PB             6

## sum up all `Exp_Flux` per hour per `micro.form`
df %>% 
  count(hour, micro.form, wt = Exp_Flux)
#> # A tibble: 6 x 3
#>    hour micro.form     n
#>   <int> <chr>      <dbl>
#> 1    12 FP          3.22
#> 2    12 HP         16.2 
#> 3    12 PB          2.89
#> 4    13 FP         16.3 
#> 5    13 HP         23.7 
#> 6    13 PB         21.8

^{Created on 2021-02-10 by the reprex package (v1.0.0)}

HTH

mhab · February 10, 2021, 9:43pm

Hi @lars,

Thank you for your advice. I really appreciate it.

And also for the code. It was helpful.

Cheers,
MHA

system · March 3, 2021, 9:43pm

This topic was automatically closed 21 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.