Having tried all available solutions that I could find both on StackOverflow and elsewhere, I still find myself stuck with this challenge. I want to group time by the Hour in other to obtain 12 different hours, and I have used several groupby() syntaxes but all keep throwing KeyError: "time" at me. After double-checking the time column, it appears ok to me(or maybe I am wrong). I am attaching the data here, and the code used. I can really use your help. Thanks.
Here’s my CSV data:
time,PM1,PM2.5,PM10,NO2,O3
6/3/2022 15:00,26,39,46,35,22
6/3/2022 16:00,25,37,43,24,31
6/3/2022 17:00,25,37,44,38,25
6/3/2022 18:00,30,45,54,55,17
6/3/2022 19:00,35,52,63,63,7
6/3/2022 20:00,46,70,77,71,1
6/3/2022 21:00,55,83,88,76,1
6/3/2022 22:00,63,94,98,70,0
6/3/2022 23:00,85,126,133,74,0
6/4/2022 00:00,96,147,158,76,0
6/4/2022 01:00,127,227,247,77,1
6/4/2022 2.00,85,129,138,68,2
6/4/2022 03:00,73,111,118,58,3
6/4/2022 04:00,65,100,108,54,4
6/4/2022 05:00,69,103,109,53,5
6/4/2022 06:00,86,126,132,54,2
6/4/2022 07:00,71,106,112,59,3
6/4/2022 08:00,75,112,118,64,13
6/4/2022 09:00,53,80,85,56,23
6/4/2022 10:00,56,85,90,72,29
6/4/2022 11:00,48,78,87,36,65
6/4/2022 12:00,23,36,42,22,41
6/4/2022 13:00,17,24,25,25,34
6/4/2022 14:00,12,18,19,11,30
6/4/2022 15:00,12,18,19,26,29
6/4/2022 16:00,12,17,18,32,29
6/4/2022 17:00,12,18,18,33,24
6/4/2022 18:00,17,25,27,50,21
6/4/2022 19:00,22,33,37,39,20
6/4/2022 20:00,27,41,50,30,13
6/4/2022 21:00,32,47,58,48,3
6/4/2022 22:00,30,45,52,39,9
6/4/2022 23:00,23,32,36,26,14
6/5/2022 00:00,27,41,49,42,17
6/5/2022 01:00,28,44,53,33,16
6/5/2022 2.00,13,20,22,5,32
6/5/2022 03:00,9,14,15,5,30
6/5/2022 04:00,21,31,35,31,15
6/5/2022 05:00,16,23,25,30,17
6/5/2022 06:00,11,17,19,27,23
6/5/2022 07:00,12,21,23,25,40
6/5/2022 08:00,11,16,18,12,33
6/5/2022 09:00,11,16,18,16,31
6/5/2022 10:00,16,25,29,22,27
6/5/2022 11:00,19,30,34,3,43
6/5/2022 12:00,13,19,21,28,59
6/5/2022 13:00,9,15,18,8,44
6/5/2022 14:00,10,19,23,25,41
6/5/2022 15:00,8,15,18,41,42
6/5/2022 16:00,8,13,15,39,40
6/5/2022 17:00,7,13,16,33,39
6/5/2022 18:00,21,36,45,35,33
6/5/2022 19:00,24,38,47,39,18
6/5/2022 20:00,31,47,57,64,3
6/5/2022 21:00,46,66,73,63,2
6/5/2022 22:00,90,124,129,60,2
6/5/2022 23:00,69,98,103,48,7
6/6/2022 00:00,79,120,127,39,6
6/6/2022 01:00,90,144,156,40,7
6/6/2022 2.00,80,125,131,39,8
6/6/2022 03:00,74,109,115,47,6
6/6/2022 04:00,82,125,132,42,8
6/6/2022 05:00,81,126,133,33,8
6/6/2022 06:00,84,132,140,36,6
6/6/2022 07:00,75,113,121,50,4
6/6/2022 08:00,70,105,111,83,3
6/6/2022 09:00,47,78,88,60,16
6/6/2022 10:00,34,57,70,25,35
6/6/2022 11:00,27,43,52,13,60
6/6/2022 12:00,23,33,38,16,70
6/6/2022 13:00,10,14,14,20,55
6/6/2022 14:00,9,14,14,22,56
6/6/2022 15:00,13,18,19,30,53
6/6/2022 16:00,11,16,16,31,43
6/6/2022 17:00,10,15,15,21,46
6/6/2022 18:00,19,28,31,59,29
6/6/2022 19:00,25,38,44,53,17
6/6/2022 20:00,32,48,59,53,12
6/6/2022 21:00,29,44,52,45,11
6/6/2022 22:00,42,62,70,46,11
6/6/2022 23:00,94,151,160,52,6
6/7/2022 00:00,56,85,89,45,7
6/7/2022 01:00,43,64,72,33,14
6/7/2022 2.00,34,51,62,25,15
6/7/2022 03:00,30,46,56,23,19
6/7/2022 04:00,31,47,57,17,23
6/7/2022 05:00,35,53,65,29,14
6/7/2022 06:00,39,59,70,37,10
6/7/2022 07:00,43,65,74,42,9
6/7/2022 08:00,35,53,62,14,25
6/7/2022 09:00,22,33,36,1,27
6/7/2022 10:00,22,32,36,20,33
6/7/2022 11:00,22,33,36,23,38
6/7/2022 12:00,21,32,35,29,36
6/7/2022 13:00,22,33,36,38,44
6/7/2022 14:00,22,33,36,33,39
6/7/2022 15:00,22,32,36,35,40
6/7/2022 16:00,24,35,40,76,50
6/7/2022 17:00,20,30,33,52,32
6/7/2022 18:00,24,35,41,62,14
6/7/2022 19:00,42,65,74,62,13
6/7/2022 20:00,52,80,83,79,2
6/7/2022 21:00,46,70,76,52,10
6/7/2022 22:00,44,67,74,43,12
6/7/2022 23:00,39,58,69,40,12
6/8/2022 00:00,39,59,69,30,16
6/8/2022 01:00,35,55,64,27,18
6/8/2022 2.00,38,60,70,15,24
6/8/2022 03:00,41,65,73,18,19
6/8/2022 04:00,41,66,74,19,21
6/8/2022 05:00,39,62,72,27,16
6/8/2022 06:00,39,61,71,32,9
6/8/2022 07:00,43,70,79,36,8
6/8/2022 08:00,33,51,63,18,21
6/8/2022 09:00,27,42,50,1,30
6/8/2022 10:00,26,41,48,23,34
6/8/2022 11:00,23,36,42,10,36
6/8/2022 12:00,24,36,42,31,39
6/8/2022 13:00,21,31,34,14,35
6/8/2022 14:00,21,32,35,56,30
6/8/2022 15:00,22,33,37,28,30
6/8/2022 16:00,20,31,33,15,29
6/8/2022 17:00,22,33,37,40,21
6/8/2022 18:00,23,35,40,49,20
6/8/2022 19:00,27,41,49,45,14
6/8/2022 20:00,28,44,53,31,18
6/8/2022 21:00,28,44,53,22,22
6/8/2022 22:00,31,48,59,35,13
6/8/2022 23:00,35,52,63,37,11
6/9/2022 00:00,38,57,68,33,13
6/9/2022 01:00,37,59,68,25,16
6/9/2022 2.00,32,49,59,24,18
6/9/2022 03:00,34,54,66,24,17
6/9/2022 04:00,37,57,67,30,12
6/9/2022 05:00,39,59,69,36,9
6/9/2022 06:00,37,59,69,31,12
6/9/2022 07:00,39,61,71,32,7
6/9/2022 08:00,37,59,69,18,14
6/9/2022 09:00,30,46,56,20,18
6/9/2022 10:00,24,36,42,18,25
6/9/2022 11:00,21,33,36,13,28
6/9/2022 12:00,23,34,39,24,30
6/9/2022 13:00,21,32,35,24,25
6/9/2022 14:00,21,31,34,30,24
6/9/2022 15:00,20,30,33,27,27
6/9/2022 16:00,22,32,36,32,31
6/9/2022 17:00,22,33,37,37,26
6/9/2022 18:00,22,33,38,41,25
6/9/2022 19:00,26,40,47,36,18
6/9/2022 20:00,27,41,49,31,16
6/9/2022 21:00,24,38,44,24,20
6/9/2022 22:00,25,39,47,36,22
6/9/2022 23:00,30,47,56,19,24
6/10/2022 00:00,7,12,13,16,48
6/10/2022 01:00,13,21,23,13,35
6/10/2022 2.00,80,153,174,23,21
6/10/2022 03:00,21,32,36,21,19
6/10/2022 04:00,22,35,41,23,15
6/10/2022 05:00,24,39,47,33,10
6/10/2022 06:00,31,49,62,38,6
6/10/2022 07:00,35,55,67,42,5
6/10/2022 08:00,52,79,85,73,1
6/10/2022 09:00,55,86,93,93,1
6/10/2022 10:00,45,70,78,41,37
6/10/2022 11:00,31,48,59,58,24
6/10/2022 12:00,21,32,37,30,27
6/10/2022 13:00,16,24,25,34,35
6/10/2022 14:00,14,22,23,25,36
6/10/2022 15:00,16,24,26,43,37
6/10/2022 16:00,13,20,21,45,34
6/10/2022 17:00,14,19,21,41,30
6/10/2022 18:00,18,27,29,46,24
6/10/2022 19:00,17,26,27,42,23
6/10/2022 20:00,25,38,44,46,16
6/10/2022 21:00,26,38,45,53,9
6/10/2022 22:00,51,84,94,60,5
6/10/2022 23:00,29,43,52,45,9
6/11/2022 00:00,35,51,62,32,12
6/11/2022 01:00,72,129,146,34,10
6/11/2022 2.00,35,53,66,47,7
6/11/2022 03:00,32,50,61,25,15
6/11/2022 04:00,25,37,44,19,22
6/11/2022 05:00,26,40,47,26,16
6/11/2022 06:00,27,42,50,32,11
6/11/2022 07:00,28,44,53,30,15
6/11/2022 08:00,32,51,60,30,16
6/11/2022 09:00,28,43,53,32,15
6/11/2022 10:00,30,46,57,38,15
6/11/2022 11:00,9,13,14,24,44
6/11/2022 12:00,15,22,24,19,34
6/11/2022 13:00,9,14,15,10,38
6/11/2022 14:00,9,13,14,39,39
6/11/2022 15:00,8,11,12,37,32
6/11/2022 16:00,6,9,9,13,31
6/11/2022 17:00,13,18,19,39,30
6/11/2022 18:00,19,29,32,58,16
6/11/2022 19:00,26,40,46,59,11
6/11/2022 20:00,26,39,46,48,10
6/11/2022 21:00,32,48,59,50,7
6/11/2022 22:00,42,63,72,51,8
6/11/2022 23:00,38,57,68,41,11
6/12/2022 00:00,33,51,61,27,19
6/12/2022 01:00,37,56,65,23,19
6/12/2022 2.00,37,58,68,25,17
6/12/2022 03:00,29,44,53,27,14
6/12/2022 04:00,31,47,57,31,11
6/12/2022 05:00,29,45,54,34,12
6/12/2022 06:00,35,53,64,41,6
6/12/2022 07:00,34,51,62,39,6
6/12/2022 08:00,34,51,63,28,17
6/12/2022 09:00,29,43,52,20,25
6/12/2022 10:00,20,30,33,22,27
6/12/2022 11:00,17,24,25,14,28
6/12/2022 12:00,17,24,25,29,30
6/12/2022 13:00,16,23,24,41,33
6/12/2022 14:00,14,21,21,22,36
6/12/2022 15:00,12,18,19,11,31
6/12/2022 16:00,13,18,19,29,30
6/12/2022 17:00,14,20,22,34,31
6/12/2022 18:00,17,25,27,48,27
6/12/2022 19:00,22,33,37,49,13
6/12/2022 20:00,25,36,42,48,8
6/12/2022 21:00,30,45,55,42,8
6/12/2022 22:00,34,50,60,42,7
6/12/2022 23:00,32,47,57,34,10
6/13/2022 00:00,34,51,62,37,10
6/13/2022 01:00,29,44,53,28,13
6/13/2022 2.00,31,47,57,31,12
6/13/2022 03:00,25,38,44,26,11
6/13/2022 04:00,29,43,52,22,14
6/13/2022 05:00,29,45,54,22,13
6/13/2022 06:00,27,41,49,23,14
6/13/2022 07:00,27,41,48,20,14
6/13/2022 08:00,22,32,36,4,20
6/13/2022 09:00,18,26,28,0,19
6/13/2022 10:00,18,25,26,11,29
6/13/2022 11:00,19,28,30,13,35
6/13/2022 12:00,23,35,39,21,39
6/13/2022 13:00,17,24,25,56,38
6/13/2022 14:00,14,19,20,33,30
6/13/2022 15:00,13,18,19,22,31
6/13/2022 16:00,16,22,24,28,29
6/13/2022 17:00,15,22,23,31,30
6/13/2022 18:00,16,23,24,42,21
6/13/2022 19:00,21,31,34,48,17
6/13/2022 20:00,25,37,44,32,17
6/13/2022 21:00,29,44,53,28,16
6/13/2022 22:00,29,44,53,26,14
6/13/2022 23:00,29,43,51,36,11
6/14/2022 00:00,27,41,48,20,18
6/14/2022 01:00,28,43,52,32,11
6/14/2022 2.00,28,42,51,33,15
6/14/2022 03:00,27,41,49,29,13
6/14/2022 04:00,26,39,46,25,15
6/14/2022 05:00,28,42,50,32,10
6/14/2022 06:00,29,44,54,31,10
6/14/2022 07:00,30,47,58,23,11
6/14/2022 08:00,30,45,54,15,12
6/14/2022 09:00,24,36,42,7,21
6/14/2022 10:00,22,32,37,21,30
6/14/2022 11:00,19,27,29,6,32
6/14/2022 12:00,20,29,31,31,31
6/14/2022 13:00,19,27,29,20,29
6/14/2022 14:00,19,28,30,11,30
6/14/2022 15:00,18,27,28,18,28
6/14/2022 16:00,20,29,31,28,32
6/14/2022 17:00,21,31,35,55,29
6/14/2022 18:00,23,36,42,39,25
6/14/2022 19:00,25,39,46,36,21
6/14/2022 20:00,29,44,54,30,19
6/14/2022 21:00,29,45,54,27,21
6/14/2022 22:00,29,44,54,24,23
6/14/2022 23:00,24,37,43,13,27
6/14/2022 00:00,24,36,42,9,29
6/14/2022 01:00,25,38,45,6,31
6/14/2022 2.00,29,44,53,20,20
6/14/2022 03:00,31,47,57,15,20
6/14/2022 04:00,29,45,54,16,21
6/14/2022 05:00,48,74,82,33,7
6/14/2022 06:00,25,39,47,31,24
6/14/2022 07:00,22,34,39,20,23
6/14/2022 08:00,23,36,41,45,15
6/14/2022 09:00,26,40,47,42,12
6/14/2022 10:00,31,48,58,49,13
6/14/2022 11:00,35,54,64,67,10
6/14/2022 12:00,25,38,45,43,20
6/14/2022 13:00,11,15,16,37,23
6/14/2022 14:00,14,21,23,28,26
6/14/2022 15:00,15,23,25,29,27
6/14/2022 16:00,12,18,20,40,24
6/14/2022 17:00,17,25,27,58,19
6/14/2022 18:00,18,27,30,51,20
6/14/2022 19:00,28,42,49,54,12
6/14/2022 20:00,30,45,55,56,5
6/14/2022 21:00,46,68,77,54,6
6/14/2022 22:00,31,47,57,39,13
6/14/2022 23:00,33,52,60,30,14
6/15/2022 00:00,32,49,60,30,13
6/15/2022 01:00,26,41,49,21,21
6/15/2022 2.00,23,36,41,17,21
6/15/2022 03:00,21,32,35,16,22
6/15/2022 04:00,19,30,32,18,20
6/15/2022 05:00,21,32,36,22,19
6/15/2022 06:00,21,31,34,25,19
6/15/2022 07:00,21,33,37,31,16
6/15/2022 08:00,20,30,32,20,19
6/15/2022 09:00,18,27,28,24,21
6/15/2022 10:00,17,26,27,22,20
6/15/2022 11:00,20,29,31,36,16
6/15/2022 12:00,17,25,26,21,23
6/15/2022 13:00,15,22,23,36,29
6/15/2022 14:00,8,12,14,50,37
6/15/2022 15:00,17,28,33,44,34
6/15/2022 16:00,22,35,41,50,29
6/15/2022 17:00,19,31,36,49,25
6/15/2022 18:00,12,19,20,37,25
6/15/2022 19:00,21,32,36,48,14
6/15/2022 20:00,29,44,53,57,6
6/15/2022 21:00,29,42,50,60,3
6/15/2022 22:00,38,57,65,48,10
6/15/2022 23:00,41,59,67,41,7
Here’s the code I’m using.
import pandas as pd
df = pd.read_csv('alimosho.csv', parse_dates=["time"], index_col="time", sep=",")
df.groupby([df['time'].dt.hour]).sum()
>Solution :
As mentioned in the comments, some of the dates are mis-formatted. You can fix that either in your source, or by doing a find-and-replace in Pandas.
Example:
import pandas as pd
df = pd.read_csv('alimosho_stack.csv')
df['time'] = pd.to_datetime(df['time'].str.replace('2.00', '02:00', regex=False))
df.groupby([df['time'].dt.hour]).sum(numeric_only=True)
Explanation:
This code replaces all matches with 2.00 with 02:00 so that the dates can be consistently parsed. Also, it avoids putting time in the index_cols, so that the column can be retrieved with df['time']. Once that’s done, grouping by hour works.