Pandas Python - Counting and Grouping Date-Time-Index

user_13359534 0
Viewed 248

I am reading a very large excel file into a dataframe

Date    Lane    Lane Name   Direction   DirectionName   Speed (mph) Headway (s) Gap (s) Flags   Flag Text
0   2018-02-02 00:00:03.000 6   SB_NS   2   South   38.525  NaN NaN 5   Friday
1   2018-02-02 00:00:22.010 5   SB_MID  2   South   32.310  NaN NaN 5   Friday
2   2018-02-02 00:00:22.020 4   SB_OS   2   South   44.739  NaN NaN 5   Friday
3   2018-02-02 00:00:36.040 6   SB_NS   2   South   33.554  NaN NaN 5   Friday
4   2018-02-02 00:00:49.070 6   SB_NS   2   South   39.768  12.300  11.847  5   Friday
... ... ... ... ... ... ... ... ... ... ...
503763  2018-02-27 23:59:00.090 2   NB_MID  1   North   32.932  4.415   3.833   2   Tuesday
503764  2018-02-27 23:59:29.090 6   SB_NS   2   South   29.825  65.500  64.700  2   Tuesday
503765  2018-02-27 23:59:32.050 4   SB_OS   2   South   29.205  236.000 235.848 2   Tuesday
503766  2018-02-27 23:59:33.070 6   SB_NS   2   South   37.283  3.330   3.462   2   Tuesday
503767  2018-02-27 23:59:58.050 1   NB_NS   1   North   36.661  76.000  75.669  2   Tuesday
503768 rows × 10 columns

I drop the columns I don't need. I'm only interested in certain dates and data that is [DirectionName = South]. I have also left 'Flag text' in which is simply the day of the week. I also set the DateTime format and make it the index.

The code below is what I am using to specify which dates to use:

#df.sort_index(inplace=True)
df = df.loc[(df.DirectionName =="South")] 

# Specify dates to use
myDates = ['2018-02-02', '2018-02-09', '2018-02-16', '2018-02-23']
df_in = df[pd.to_datetime(df.index.date).isin(myDates)]
df

Which gives me this output:

                DirectionName   FlagText   
Date        
2018-02-02 00:00:03.000 South   Friday
2018-02-02 00:00:22.010 South   Friday
2018-02-02 00:00:22.020 South   Friday
2018-02-02 00:00:36.040 South   Friday
2018-02-02 00:00:49.070 South   Friday
... ... ...
2018-02-27 23:58:20.070 South   Tuesday
2018-02-27 23:58:23.040 South   Tuesday
2018-02-27 23:59:29.090 South   Tuesday
2018-02-27 23:59:32.050 South   Tuesday
2018-02-27 23:59:33.070 South   Tuesday
251528 rows × 2 columns

I want to be able to count the total number of rows for a selected date. For example, I want to count every row for the date 02-02-2018. Ultimately, I want to be able to count the total for every hour in the day (0am > 23:59pm.)

Here is an example of my desired output:

 DirectionName   Flag Text              Count
Date        
2018-02-02 01:00:00.000 South   Friday   234
2018-02-02 02:00:00.000 South   Friday   554
2018-02-02 03:00:00.000 South   Friday   785
2018-02-02 04:00:00.000 South   Friday   124
2018-02-02 05:00:00.000 South   Friday   345
... ... ...
  • How would I be able to group the dates per hour like shown above?
  • How would I then be able to count the dates within that hourly range?
  • Would I be able to use this method for multiple dates? (four different dates)

I have tried looking at other posts / documentation, but I am getting confused because I have put the Dates into the index? Which I thought made more sense.

Help and clarification would be greatly appreciated

pandas-groupby pandas dataframe datetime python
2 Answers

You could create another column which has (a) day-date (b) date until the hour

Something like this:

df['day-date'] = pd.to_datetime(df.Date, format='%Y-%m-%d')
df['hour-date'] = pd.to_datetime(df.Date, format='%Y-%m-%d %H')

Then do a group-by on each of these columns:

day_sum_df = df.groupby(['day-date']).sum()
hour_sum_df = df.groupby(['hour-date']).sum()
user_102084810

use groupby to group by date then use count.

# if your date column is in date plus time then convert it to date then group by date then count of Date column
df.groupby([df['Date'].dt.date])['Date'].count()

if your date is already in date format you can simply do

df.groupby('Date')['Date'].count()
user_123503260
Related