Spreadsheet: 597841d1542094545-missing-values-how-to-average-each-midnight-after-23-values-sample-worksheet-keebellah-
Sheet: BEFORE

Date | Time | Temperature | Wind speed | Wind direction | Humidity | Carbon | [NAME] (ppm) | [NAME] (ppm) | [NAME] (ppm) | Visbility | PM2.5 (ug/m^3) | PM 2.10
2017-01-01 00:00:00 | 00:00:00 | 13 | 0.3 | 164.1 | 0.2 |  |  |  |  | 3.8 | 0.6
2017-01-01 00:00:00 | 01:00:00 | 56 | 0.5 | 45 | 0.3 | 0 | 0.003 | 0.003 | 0.001 | 5.8 | 2.8 | 9 |  |  | Missing Values
2017-01-01 00:00:00 | 02:00:00 | 296 | 0.2 | 59.5 | 0.2 | 0.001 | 0.002 | 0.003 | 0 | 7 | 2.2 | 9 |  |  | Pattern repeated every 0:00
2017-01-01 00:00:00 | 03:00:00 | 286 | 0.6 | 15.3 | 0.2 | 0.001 | 0.003 | 0.004 | 0.001 | 6.4 | 0.4 | 9 |  |  | However some missing values here and there
2017-01-01 00:00:00 | 04:00:00 | 277 | 0.2 | 87.8 | 0.2 | 0 | 0.002 | 0.002 | 0 | 3.9 | -0.6 | 9 |  | Target: | Replace missing values
2017-01-01 00:00:00 | 05:00:00 | 295 | 0.7 | 15.9 | 0.3 | 0.003 | 0.002 | 0.005 | 0.001 | 6.6 | 2.5 | 9 |  |  | with average of same calendar
2017-01-01 00:00:00 | 06:00:00 | 307 | 0.5 | 149.8 | 0.5 | 0.004 | 0.002 | 0.006 | 0.001 | 6.4 | 2.7 | 9 |  |  | day (1:00 - 23:00)
2017-01-01 00:00:00 | 07:00:00 | 61 | 1.3 | 30.7 | 0.6 | 0 | 0 | 0 | 0.001 | 4.6 | 0.7 | 8
2017-01-01 00:00:00 | 08:00:00 | 71 | 1.8 | 29.7 | 0.9 | 0 | 0 | 0 | 0.001 | 3.9 | 1 | 8
2017-01-01 00:00:00 | 09:00:00 | 81 | 3.2 | 20.1 | 0.9 | 0 | 0 | 0 | 0.001 | 8.4 | 4.8 | 9
2017-01-01 00:00:00 | 10:00:00 | 87 | 3.9 | 17.6 | 1 | 0 | 0 | 0 | 0 | 8.8 | 5.4 | 7
2017-01-01 00:00:00 | 11:00:00 | 85 | 4.7 | 16.8 | 1.2 | 0 | 0 | 0 | 0 | 7 | 4.2 | 6
2017-01-01 00:00:00 | 12:00:00 | 88 | 5.5 | 14.6 | 1.4 | 0 | 0 | 0 | 0.001 | 5.9 | 2.1 | 5
2017-01-01 00:00:00 | 13:00:00 | 86 | 5 | 15.1 | 1.4 | 0 | 0 | 0 | 0.001 | 4 | 0.4 | 5
2017-01-01 00:00:00 | 14:00:00 | 88 | 4.9 | 16.7 | 1.4 | 0 | 0 | 0 | 0.001 | 4.4 | 0.9 | 4
2017-01-01 00:00:00 | 15:00:00 | 78 | 4 | 17.2 | 1.1 | 0 | 0 | 0 | 0.001 | 1.6 | -1.4 | 5
2017-01-01 00:00:00 | 16:00:00 | 84 | 3.9 | 17.8 | 1.1 | 0 | 0 | 0 | 0.001 | 5.4 | 2.4 | 6
2017-01-01 00:00:00 | 17:00:00 | 79 | 3.2 | 17.7 | 1 | 0 | 0 | 0 | 0.001 | 4 | 1.8 | 5
2017-01-01 00:00:00 | 18:00:00 | 82 | 2.8 | 16 | 0.8 | 0 | 0 | 0 | 0 | 2.1 | -0.2 | 6
2017-01-01 00:00:00 | 19:00:00 | 79 | 2.4 | 16.4 | 0.8 | 0 | 0 | 0 | 0 | 5.9 | 2.6 | 6
2017-01-01 00:00:00 | 20:00:00 | 77 | 2.6 | 16.1 | 0.8 | 0 | 0 | 0 | 0 | 5.9 | 2.9 | 10
2017-01-01 00:00:00 | 21:00:00 | 74 | 2.1 | 18 | 0.7 | 0 | 0 | 0 | 0 | 2.9 | -0.4 | 7
2017-01-01 00:00:00 | 22:00:00 | 71 | 1.9 | 18.3 | 0.7 | 0 | 0 | 0 | 0 | 5.6 | 2.3 | 6
2017-01-01 00:00:00 | 23:00:00 | 66 | 1.9 | 15.2 | 0.7 | 0 | 0 | 0 | 0 | 4.1 | 1.3 | 5
2017-01-02 00:00:00 | 00:00:00 | 74 | 1.9 | 19.4 | 0.7 |  |  |  |  | 5.8 | 3.4
2017-01-02 00:00:00 | 01:00:00 | 72 | 1.9 | 18.5 | 0.7 | 0 | 0 | 0 | 0.001 | 3.5 | -0.3 | 10
2017-01-02 00:00:00 | 02:00:00 | 67 | 1.6 | 19.5 | 0.7 | 0 | 0 | 0 | 0 | 3.2 | -0.7 | 12
2017-01-02 00:00:00 | 03:00:00 | 69 | 1.8 | 18.4 | 1 | 0 | 0 | 0 | 0 | 5.3 | 0.9 | 10
2017-01-02 00:00:00 | 04:00:00 | 69 | 2 | 17.5 | 0.8 | 0 | 0 | 0 | 0 | 9.2 | 5.3 | 10
2017-01-02 00:00:00 | 05:00:00 | 66 | 1.4 | 21.4 | 0.7 | 0 | 0 | 0 | 0 | 5.8 | 0.6 | 11
2017-01-02 00:00:00 | 06:00:00 | 69 | 1.9 | 20 | 0.8 | 0 | 0 | 0 | 0 | 6.3 | 1.6 | 9
2017-01-02 00:00:00 | 07:00:00 | 94 | 1.6 | 19.1 | 0.6 | 0 | 0.001 | 0.001 | 0 | 2.8 | -1.9 | 7
2017-01-02 00:00:00 | 08:00:00 | 98 | 1.2 | 21.3 | 0.4 | 0.001 | 0.001 | 0.002 | 0 | 5.3 | 0 | 8
2017-01-02 00:00:00 | 09:00:00 | 103 | 0.5 | 78.8 | 0.7 | 0.001 | 0.001 | 0.002 | 0 | 2.4 | -2.2 | 9
2017-01-02 00:00:00 | 10:00:00 | 100 | 0.7 | 67.7 | 0.7 | 0.001 | 0.001 | 0.002 | 0 | 6.8 | 0.9 | 10
2017-01-02 00:00:00 | 11:00:00 | 92 | 2.4 | 21.3 | 1 | 0.001 | 0.001 | 0.001 | 0 | 6.2 | 2.6 | 8
2017-01-02 00:00:00 | 12:00:00 | 86 | 3.3 | 18.2 | 1.3 | 0 | 0 | 0 | 0 | 8.5 | 3.9 | 7
2017-01-02 00:00:00 | 13:00:00 | 95 | 3.6 | 22.4 | 1.2 | 0 | 0 | 0 | 0 | 6.8 | 3.4 | 6
2017-01-02 00:00:00 | 14:00:00 | 88 | 4.1 | 15.6 | 1.3 | 0 | 0 | 0 | 0 | 5.8 | 2.9 | 5
2017-01-02 00:00:00 | 15:00:00 | 89 | 4 | 15.8 | 1.2 | 0 | 0 | 0 | 0 | 4.5 | 0.9 | 6
2017-01-02 00:00:00 | 16:00:00 | 87 | 2.9 | 23 | 1.5 | 0 | 0 | 0 | 0 | 7.4 | 4.6 | 7
2017-01-02 00:00:00 | 17:00:00 | 95 | 3 | 16 | 0.9 | 0 | 0 | 0 | 0 | 7.1 | 2.4 | 7
2017-01-02 00:00:00 | 18:00:00 | 131 | 2.3 | 15.6 | 1 | 0 | 0 | 0 | 0 | 5.2 | 0.9 | 7
2017-01-02 00:00:00 | 19:00:00 | 98 | 2.5 | 19.8 | 1 | 0 | 0.001 | 0.001 | 0 | 6.4 | 2.1 | 6
2017-01-02 00:00:00 | 20:00:00 | 89 | 2.6 | 17.7 | 0.9 | 0 | 0.001 | 0.001 | 0 | 5.8 | 0.9 | 6
2017-01-02 00:00:00 | 21:00:00 | 82 | 2.1 | 21.4 | 0.8 | 0 | 0.001 | 0.001 | 0 | 5.2 | 0.9 | 6
2017-01-02 00:00:00 | 22:00:00 | 87 | 2.8 | 16.5 | 0.9 | 0 | 0 | 0 | 0.001 | 5.1 | 0.2 | 5
2017-01-02 00:00:00 | 23:00:00 | 123 | 2.3 | 24.8 | 0.7 | 0.001 | 0.004 | 0.004 | 0.001 | 4 | 0.5 | 4
2017-01-03 00:00:00 | 00:00:00 | 145 | 2.1 | 17.6 | 0.7 |  |  |  |  | 0.7 | -1.8
2017-01-03 00:00:00 | 01:00:00 | 151 | 2.5 | 28 | 1 | 0.003 | 0.004 | 0.006 | 0.002 | 2.3 | 0.8 | 4
2017-01-03 00:00:00 | 02:00:00 | 128 | 1.1 | 47.9 | 0.9 | 0.01 | 0.01 | 0.02 | 0.002 |  |  | 8
2017-01-03 00:00:00 | 03:00:00 | 155 | 1.7 | 17.6 | 0.7 | 0.001 | 0.003 | 0.003 | 0.001 | 2.2 | -0.3 | 7
2017-01-03 00:00:00 | 04:00:00 | 152 | 1.3 | 20.8 | 0.6 | 0.001 | 0.004 | 0.005 | 0.001 | 6.8 | 2.5 | 10
2017-01-03 00:00:00 | 05:00:00 | 135 | 1.6 | 23.6 | 1 | 0.019 | 0.009 | 0.028 | 0.002 | 8 | 3.4 | 7
2017-01-03 00:00:00 | 06:00:00 | 108 | 1.2 | 29.9 | 0.5 | 0.005 | 0.006 | 0.011 | 0.003 | 4.5 | 1.7 | 7
2017-01-03 00:00:00 | 07:00:00 | 117 | 2.9 | 16.3 | 1.1 | 0.012 | 0.007 | 0.019 | 0.004 | 2.7 | 0.6 | 13
2017-01-03 00:00:00 | 08:00:00 | 119 | 3.3 | 16.9 | 1 | 0.039 | 0.012 | 0.05 | 0.017 | 5.2 | -2.4 | 15
2017-01-03 00:00:00 | 09:00:00 | 126 | 3 | 19.1 | 1.5 | 0.081 | 0.015 | 0.096 | 0.029 | 12.9 | 3 | 16
2017-01-03 00:00:00 | 10:00:00 | 105 | 4.9 | 15.7 | 1.6 | 0.007 | 0.004 | 0.011 | 0.008 | 11.4 | 2.6 | 16
2017-01-03 00:00:00 | 11:00:00 | 103 | 4.4 | 16.2 | 1.4 | 0.001 | 0.001 | 0.002 | 0.002 | 14.9 | 2.7 | 20
2017-01-03 00:00:00 | 12:00:00 | 106 | 4.3 | 16.2 | 1.4 | 0.005 | 0.004 | 0.009 | 0.003 | 15.7 | 3 | 15
2017-01-03 00:00:00 | 13:00:00 | 114 | 4.1 | 17.2 | 1.3 | 0.023 | 0.009 | 0.031 | 0.012 | 7.8 | 0.1 | 11
2017-01-03 00:00:00 | 14:00:00 | 125 | 4.2 | 17.4 | 1.4 | 0.014 | 0.007 | 0.021 | 0.01 | 7.4 | 1.6 | 11
2017-01-03 00:00:00 | 15:00:00 | 134 | 2.9 | 21.6 | 1.1 | 0.004 | 0.004 | 0.007 | 0.004 | 10.2 | 3.4 | 13
2017-01-03 00:00:00 | 16:00:00 | 120 | 3.4 | 16.5 | 1.1 | 0.027 | 0.01 | 0.037 | 0.014 | 13 | 5.3 | 16
2017-01-03 00:00:00 | 17:00:00 | 122 | 4.1 | 18.5 | 2.1 | 0.022 | 0.009 | 0.031 | 0.012 | 13 | 5.3 | 15
2017-01-03 00:00:00 | 18:00:00 | 126 | 4.4 | 16.6 | 1.6 | 0.015 | 0.007 | 0.021 | 0.013 | 9.6 | 4.3 | 12
2017-01-03 00:00:00 | 19:00:00 | 130 | 4.3 | 17.7 | 1.4 | 0.001 | 0.002 | 0.003 | 0.006 | 9.5 | 5.1 | 10
2017-01-03 00:00:00 | 20:00:00 | 130 | 3.9 | 16.5 | 1.3 | 0 | 0 | 0 | 0.002 | 6.3 | 2.6 | 11
2017-01-03 00:00:00 | 21:00:00 | 130 | 4.2 | 17.4 | 1.4 | 0.001 | 0 | 0.001 | 0.002 | 10.1 | 6.2 | 10
2017-01-03 00:00:00 | 22:00:00 | 128 | 4.5 | 16 | 1.6 | 0.006 | 0.002 | 0.008 | 0.002 | 6.4 | 3.8 | 6
2017-01-03 00:00:00 | 23:00:00 | 135 | 3.2 | 16 | 1.2 | 0.001 | 0.001 | 0.002 | 0.003 | 6.5 | 2.5 | 8