First Data (Note: the further parts relates to the updates)
Data is very limited, probably due to the complexity to simplify it, so I shall make some assumptions and write this as generic as possible, so you can customize it fast to your needs.
Assumptions:
- You want to group by hours-windows ("hour_code") the data (therefore parameterized what data is grouped by as
group_divide_set_by_column)
- For each hours-windows ("hour_code"), you want to cluster by location using K means algorithm
Doing so allows you to investigate the clusters of vehicles for each hour-window separately, and learn what clustered areas are more active and need attention.
Notes:
- Location column (although noted) is missing and required for the K-means algorithm (I used
HostName_key but it's just a dummy so code would run, it not necessarily meaningful).
Generally speaking, the K-means algorithm is for spaces with euclidean distance (Mathematically, this means partitioning the observations according to the Voronoi diagram generated by the means.)
Here are some sources for k-means Python examples that are useful to further customize it: 1 2 3 4.
Code:
- Let's define a function, which given a dataframe group-divides it by a given column,
group_divide_set_by_column.
This would allow us to group-divide by 'hour_code', and then cluster by location.
def create_clusters_by_group(df, group_divide_set_by_column='hour_code', clusters_number_list=[2, 3]):
# Divide et by hours
divide_df_by_hours(df)
lst_df_by_groups = {f'{group_divide_set_by_column}_{i}': d for i, (g, d) in enumerate(df.groupby(group_divide_set_by_column))}
# For each group dataframe
for group_df_name, group_df in lst_df_by_groups.items():
# Divide to desired amount of clusters
for clusters_number in clusters_number_list:
create_cluster(group_df, clusters_number)
# Setting column types
set_colum_types(group_df)
return lst_df_by_groups
- The #1 function would use another function to convert
hour to hour codes, in similar to how you phrased it:
Time period meaning, say 1 would be before 6 am, 2 - 6 am to 9 am, 3 - 9 to 11, 4 - 11 to 14, etc..
def divide_df_by_hours(df):
def get_hour_code(h, start_threshold=6, end_threshold=21, windows=3):
"""
Divide hours to groups:
Hours:
1-5 => 1
6-8 => 2
9-11 => 3
12-14 => 4
15-17 => 5
18-20 => 6
21+ => 7
"""
if h < start_threshold:
return 1
elif h >= end_threshold:
return (end_threshold // windows)
return h // windows
df['hour_code'] = df['starthour'].apply(lambda h : get_hour_code(h))
- Moreover the #1 function would use the
set_colum_types function that would convert columns to their matching types:
def set_colum_types(df):
types_dict = {
'Startdtm': 'datetime64[ns, Australia/Melbourne]',
'HostName_key': 'category',
'Totalvehicles': 'int32',
'Enddtm': 'datetime64[ns, Australia/Melbourne]',
'starthour': 'int32',
'timedelta': 'float',
'vehiclespersec': 'float',
}
for col, col_type in types_dict.items():
df[col] = df[col].astype(col_type)
- A dedicated
timeit decorator is used to measure the time for each clustering, so boilerplate code is reduced
Whole Code:
import functools
import pandas as pd
from timeit import default_timer as timer
import sklearn
from sklearn.cluster import KMeans
def timeit(func):
@functools.wraps(func)
def newfunc(*args, **kwargs):
startTime = timer()
func(*args, **kwargs)
elapsedTime = timer() - startTime
print('function [{}] finished in {} ms'.format(
func.__name__, int(elapsedTime * 1000)))
return newfunc
def set_colum_types(df):
types_dict = {
'Startdtm': 'datetime64[ns, Australia/Melbourne]',
'HostName_key': 'category',
'Totalvehicles': 'int32',
'Enddtm': 'datetime64[ns, Australia/Melbourne]',
'starthour': 'int32',
'timedelta': 'float',
'vehiclespersec': 'float',
}
for col, col_type in types_dict.items():
df[col] = df[col].astype(col_type)
@timeit
def create_cluster(df, clusters_number):
# Create K-Means model
model = KMeans(n_clusters=clusters_number, max_iter=600, random_state=9)
# Fetch location
# NOTE: Should be a *real* location, used another column as dummy
location_df = df[['HostName_key']]
kmeans = model.fit(location_df)
# Divide to clusters
df[f'kmeans_{clusters_number}'] = kmeans.labels_
def divide_df_by_hours(df):
def get_hour_code(h, start_threshold=6, end_threshold=21, windows=3):
"""
Divide hours to groups:
Hours:
1-5 => 1
6-8 => 2
9-11 => 3
12-14 => 4
15-17 => 5
18-20 => 6
21+ => 7
"""
if h < start_threshold:
return 1
elif h >= end_threshold:
return (end_threshold // windows)
return h // windows
df['hour_code'] = df['starthour'].apply(lambda h : get_hour_code(h))
def create_clusters_by_group(df, group_divide_set_by_column='hour_code', clusters_number_list=[2, 3]):
# Divide et by hours
divide_df_by_hours(df)
lst_df_by_groups = {f'{group_divide_set_by_column}_{i}': d for i, (g, d) in enumerate(df.groupby(group_divide_set_by_column))}
# For each group dataframe
for group_df_name, group_df in lst_df_by_groups.items():
# Divide to desired amount of clusters
for clusters_number in clusters_number_list:
create_cluster(group_df, clusters_number)
# Setting column types
set_colum_types(group_df)
return lst_df_by_groups
# Load data
df = pd.read_csv('data.csv')
# Print data
print(df)
# Create clusters
lst_df_by_groups = create_clusters_by_group(df)
# For each hostname-key dataframe
for group_df_name, group_df in lst_df_by_groups.items():
print(f'Group {group_df_name} dataframe:')
print(group_df)
Example output:
Startdtm HostName_key ... timedelta vehiclespersec
0 2020-01-15 08:22:39 0 ... 29400.0 0.326633
1 2020-01-13 08:22:07 2 ... 28981.0 0.354474
2 2020-01-23 07:16:55 3 ... 26149.0 0.197904
3 2020-01-15 07:00:06 4 ... 2783.0 0.301114
4 2020-01-15 08:16:01 1 ... 15915.0 0.366949
5 2020-01-16 08:22:39 2 ... 29400.0 0.326633
6 2020-01-14 08:22:07 2 ... 28981.0 0.354479
7 2020-01-25 07:16:55 4 ... 26149.0 0.197904
8 2020-01-17 07:00:06 1 ... 2783.0 0.301114
9 2020-01-18 08:16:01 1 ... 15915.0 0.366949
[10 rows x 7 columns]
function [create_cluster] finished in 10 ms
function [create_cluster] finished in 11 ms
function [create_cluster] finished in 10 ms
function [create_cluster] finished in 11 ms
function [create_cluster] finished in 10 ms
function [create_cluster] finished in 11 ms
Group hour_code_0 dataframe:
Startdtm HostName_key ... kmeans_2 kmeans_3
0 2020-01-15 08:22:39+11:00 0 ... 1 1
1 2020-01-13 08:22:07+11:00 2 ... 0 0
2 2020-01-23 07:16:55+11:00 3 ... 0 2
[3 rows x 10 columns]
Group hour_code_1 dataframe:
Startdtm HostName_key ... kmeans_2 kmeans_3
3 2020-01-15 07:00:06+11:00 4 ... 1 1
4 2020-01-15 08:16:01+11:00 1 ... 0 0
5 2020-01-16 08:22:39+11:00 2 ... 0 2
[3 rows x 10 columns]
Group hour_code_2 dataframe:
Startdtm HostName_key ... kmeans_2 kmeans_3
6 2020-01-14 08:22:07+11:00 2 ... 1 2
7 2020-01-25 07:16:55+11:00 4 ... 0 0
8 2020-01-17 07:00:06+11:00 1 ... 1 1
9 2020-01-18 08:16:01+11:00 1 ... 1 1
[4 rows x 10 columns]
Update : Second Data
So, this time will make things a little different, as the updated objective is to understand how many vehicles are at each place and their speed.
Again, things are written with great care for generically for the ease of adaptation.
- First, we divide the data set to groups based on, their location which is inferred by Hostname (parameterized for customization as
dividing_colum).
def divide_df_by_column(df, dividing_colum='Hostname'):
df_by_groups = {f'{dividing_colum}_{g}': d for i, (g, d) in enumerate(df.groupby(dividing_colum))}
return df_by_groups
- Now, we arrange the data for each hostname (
dividing_colum) group:
def arrange_groups_df(lst_df_by_groups):
df_by_intervaled_group = dict()
# For each group dataframe
for group_df_name, group_df in lst_df_by_groups.items():
df_by_intervaled_group[group_df_name] = arrange_data(group_df)
return df_by_intervaled_group
2.1. We group by intervals of 15 minutes, and after each hostname area data is divided into time intervals, we aggregate the amount of vehicles to column volume and investigate the average speed to column average_speed.
def group_by_interval(df):
df[DATE_COLUMN_NAME] = pd.to_datetime(df[DATE_COLUMN_NAME])
intervaled_df = df.groupby([pd.Grouper(key=DATE_COLUMN_NAME, freq=INTERVAL_WINDOW)]).agg({'Vehicle_speed' : 'mean', 'Hostname' : 'count'}).rename(columns={'Vehicle_speed' : 'average_speed', 'Hostname' : 'volume'})
return intervaled_df
def arrange_data(df):
df = group_by_interval(df)
return df
The end result for stage #2 is that each hostname data is divided into time windows of 15 minutes, and we know how many vehicles have passed each time and what is their average speed.
By this, we achieve the objective:
An additional, and greedy question, would be - how would i interpolate speed into this measurement? i.e., large amounts of volumes, but low speeds, would be good to also cater for.
Again, all costumizable using [TIME_INTERVAL_COLUMN_NAME, DATE_COLUMN_NAME, INTERVAL_WINDOW].
The whole code:
import functools
import numpy
import pandas as pd
TIME_INTERVAL_COLUMN_NAME = 'time_interval'
DATE_COLUMN_NAME = 'DateTimeStamp'
INTERVAL_WINDOW = '15Min'
def round_time(df):
# Setting date_column_name to be of dateime
df[DATE_COLUMN_NAME] = pd.to_datetime(df[DATE_COLUMN_NAME])
# Grouping by interval
df[TIME_INTERVAL_COLUMN_NAME] = df[DATE_COLUMN_NAME].dt.round(INTERVAL_WINDOW)
def group_by_interval(df):
df[DATE_COLUMN_NAME] = pd.to_datetime(df[DATE_COLUMN_NAME])
intervaled_df = df.groupby([pd.Grouper(key=DATE_COLUMN_NAME, freq=INTERVAL_WINDOW)]).agg({'Vehicle_speed' : 'mean', 'Hostname' : 'count'}).rename(columns={'Vehicle_speed' : 'average_speed', 'Hostname' : 'volume'})
return intervaled_df
def arrange_data(df):
df = group_by_interval(df)
return df
def divide_df_by_column(df, dividing_colum='Hostname'):
df_by_groups = {f'{dividing_colum}_{g}': d for i, (g, d) in enumerate(df.groupby(dividing_colum))}
return df_by_groups
def arrange_groups_df(lst_df_by_groups):
df_by_intervaled_group = dict()
# For each group dataframe
for group_df_name, group_df in lst_df_by_groups.items():
df_by_intervaled_group[group_df_name] = arrange_data(group_df)
return df_by_intervaled_group
# Load data
df = pd.read_csv('data2.csv')
# Print data
print(df)
# Divide by column
df_by_groups = divide_df_by_column(df)
# Arrange data for each group
df_by_intervaled_group = arrange_groups_df(df_by_groups)
# For each hostname-key dataframe
for group_df_name, intervaled_group_df in df_by_intervaled_group.items():
print(f'Group {group_df_name} dataframe:')
print(intervaled_group_df)
Example Output:
We can now get valuable results from measuring the volumes (amount of vehicles) and average speed, for each individual hostname area.
DateTimeStamp VS_ID VS_Summary_Id Hostname Vehicle_speed Lane Length
0 11/01/2019 8:22 1 1 place_uno 65 2 71
1 11/01/2019 8:23 2 1 place_uno 59 1 375
2 11/01/2019 8:25 3 1 place_uno 59 1 389
3 11/01/2019 8:26 4 1 place_duo 59 1 832
4 11/01/2019 8:40 5 1 place_duo 52 1 409
Group Hostname_place_duo dataframe:
average_speed volume
DateTimeStamp
2019-11-01 08:15:00 59 1
2019-11-01 08:30:00 52 1
Group Hostname_place_uno dataframe:
average_speed volume
DateTimeStamp
2019-11-01 08:15:00 61 3
Appendix
Created also a round_time function, which allows to round to time intervals, without grouping:
def round_time(df):
# Setting date_column_name to be of dateime
df[DATE_COLUMN_NAME] = pd.to_datetime(df[DATE_COLUMN_NAME])
# Grouping by interval
df[TIME_INTERVAL_COLUMN_NAME] = df[DATE_COLUMN_NAME].dt.round(INTERVAL_WINDOW)
Third Update
So this time we want to reduce the number of rows in the result.
- We change the way we group the data, not only based on interval but also for the day in week, the result would allow us investigate in how traffic behaves for each day of the week and it's 15-minutes intervals.
The
group_by_interval function is now changed to group on the concise inteval thus, will be called group_by_concised_interval.
We shall call the combination of [day-in-week, hour-minute] as "consice interval", again this is configurable with CONCISE_INTERVAL_FORMAT.
def group_by_concised_interval(df):
df[DATE_COLUMN_NAME] = pd.to_datetime(df[DATE_COLUMN_NAME])
# Rounding time
round_time(df)
# Adding concised interval
add_consice_interval_columns(df)
intervaled_df = df.groupby([TIME_INTERVAL_CONCISE_COLUMN_NAME]).agg({'Vehicle_speed' : 'mean', 'Hostname' : 'count'}).rename(columns={'Vehicle_speed' : 'average_speed', 'Hostname' : 'volume'})
return intervaled_df
1.1. The group_by_concised_interval first rounds time to the given 15-minutes interval (configurable via INTERVAL_WINDOW) using the round_time method.
1.2. After creating the time intervals for each date, we apply the add_consice_interval_columns function that given the rounded to inteval time stamp, extracts the concise form.
def add_consice_interval_columns(df):
# Adding columns for time interval in day-in-week and hour-minute resolution
df[TIME_INTERVAL_CONCISE_COLUMN_NAME] = df[TIME_INTERVAL_COLUMN_NAME].apply(lambda x: x.strftime(CONCISE_INTERVAL_FORMAT))
The whole code is:
import functools
import numpy
import pandas as pd
TIME_INTERVAL_COLUMN_NAME = 'time_interval'
TIME_INTERVAL_CONCISE_COLUMN_NAME = 'time_interval_concise'
DATE_COLUMN_NAME = 'DateTimeStamp'
INTERVAL_WINDOW = '15Min'
CONCISE_INTERVAL_FORMAT = '%A %H:%M'
def round_time(df):
# Setting date_column_name to be of dateime
df[DATE_COLUMN_NAME] = pd.to_datetime(df[DATE_COLUMN_NAME])
# Grouping by interval
df[TIME_INTERVAL_COLUMN_NAME] = df[DATE_COLUMN_NAME].dt.round(INTERVAL_WINDOW)
def add_consice_interval_columns(df):
# Adding columns for time interval in day-in-week and hour-minute resolution
df[TIME_INTERVAL_CONCISE_COLUMN_NAME] = df[TIME_INTERVAL_COLUMN_NAME].apply(lambda x: x.strftime(CONCISE_INTERVAL_FORMAT))
def group_by_concised_interval(df):
df[DATE_COLUMN_NAME] = pd.to_datetime(df[DATE_COLUMN_NAME])
# Rounding time
round_time(df)
# Adding concised interval
add_consice_interval_columns(df)
intervaled_df = df.groupby([TIME_INTERVAL_CONCISE_COLUMN_NAME]).agg({'Vehicle_speed' : 'mean', 'Hostname' : 'count'}).rename(columns={'Vehicle_speed' : 'average_speed', 'Hostname' : 'volume'})
return intervaled_df
def arrange_data(df):
df = group_by_concised_interval(df)
return df
def divide_df_by_column(df, dividing_colum='Hostname'):
df_by_groups = {f'{dividing_colum}_{g}': d for i, (g, d) in enumerate(df.groupby(dividing_colum))}
return df_by_groups
def arrange_groups_df(lst_df_by_groups):
df_by_intervaled_group = dict()
# For each group dataframe
for group_df_name, group_df in lst_df_by_groups.items():
df_by_intervaled_group[group_df_name] = arrange_data(group_df)
return df_by_intervaled_group
# Load data
df = pd.read_csv('data2.csv')
# Print data
print(df)
# Divide by column
df_by_groups = divide_df_by_column(df)
# Arrange data for each group
df_by_intervaled_group = arrange_groups_df(df_by_groups)
# For each hostname-key dataframe
for group_df_name, intervaled_group_df in df_by_intervaled_group.items():
print(f'Group {group_df_name} dataframe:')
print(intervaled_group_df)
Output:
Group Hostname_place_duo dataframe:
average_speed volume
time_interval_concise
Friday 08:30 59 1
Friday 08:45 52 1
Group Hostname_place_uno dataframe:
average_speed volume
time_interval_concise
Friday 08:15 65 1
Friday 08:30 59 2
So now we can easily figure out how traffic behaves in each day of the week at all available time intervals.
