<jupyter_start><jupyter_text>Set hours and end_time<jupyter_code># How many hours you want to request in total
h = 1
# When is the end hour you want to request
end_time = time.strptime('May 31 2020 10:00AM', '%b %d %Y %I:%M%p')
vehicle_hourly_aggregates = pd.DataFrame()
Time = int(time.mktime(end_time))*1000
# loop through assets to fetch events for each asset
for index, row in vehicle_sensor_metadata_df.iterrows():
    endTime = Time
    # Because when hours exceed 12, the server will be overloaded
    
    hours = 0
    
    while hours < h:
        # empty list to collect events
        vehicle_sensor_events_list = []
        hours = hours + 1
        
        startTime = endTime - 3600000 # startTime is 1 hour before endTime
        
        myCIQ.fetchEvents("assets", row.assetUid, "TFEVT", startTime, endTime, pageSize=100000)
        assetEvents = myCIQ.getEvents()
        
        
        
        if assetEvents == []:
            continue

        for a in assetEvents:
            a["directionUnit"] = a["properties"]["directionUnit"]
            a["speedUnit"] = a["properties"]["speedUnit"]
            a["eventUid"] = a["properties"]["eventUid"]
            a["counter_direction_speed"] = a["measures"]["counter_direction_speed"]
            a["counter_direction_vehicleCount"] = a["measures"]["counter_direction_vehicleCount"]
            a["vehicleCount"] = a["measures"]["vehicleCount"]
            a["counter_direction"] = a["measures"]["counter_direction"]
            a["speed"] = a["measures"]["speed"]
            a["direction"] = a["measures"]["direction"]
            vehicle_sensor_events_list.append(a)
        
        # with one hour of data make a dataframe, drop nulls, and group/aggregate vehicle counts 
        vehicle_sensor_events_df = pd.DataFrame(vehicle_sensor_events_list)
        vehicle_sensor_events_df.dropna(inplace = True)
        
        # group by location ID to get a sum of vehicleCounts 
        grouped_SD_veh_sensor_events_df = vehicle_sensor_events_df.groupby('assetUid').agg({'vehicleCount': ['sum'], 'counter_direction_vehicleCount': ['sum']})
        grouped_SD_veh_sensor_events_df['vehicleCount_sum'] = grouped_SD_veh_sensor_events_df['vehicleCount'] + grouped_SD_veh_sensor_events_df['counter_direction_vehicleCount']
        grouped_SD_veh_sensor_events_df = grouped_SD_veh_sensor_events_df.drop(['vehicleCount', 'counter_direction_vehicleCount'], axis=1)
        grouped_SD_veh_sensor_events_df['startTime'] = startTime
        grouped_SD_veh_sensor_events_df['assetUid'] = grouped_SD_veh_sensor_events_df.index
        grouped_SD_veh_sensor_events_df["latitude"] = row.latitude
        grouped_SD_veh_sensor_events_df["longitude"] = row.longitude
        
        vehicle_hourly_aggregates = pd.concat([vehicle_hourly_aggregates, grouped_SD_veh_sensor_events_df], ignore_index=True)
        
        endTime = startTime
    print("one iter finished!")
time_list = []
for i, row in vehicle_hourly_aggregates.iterrows():
    time_list.append(time.asctime(time.localtime(float(row['startTime']/1000))))
    
vehicle_hourly_aggregates['time'] = time_list
vehicle_hourly_aggregates = vehicle_hourly_aggregates.drop('startTime', axis=1)
vehicle_hourly_aggregates['vehicleCount_sum'] = vehicle_hourly_aggregates['vehicleCount_sum'].astype(int)
vehicle_hourly_aggregates.head(30)
vehicle_hourly_aggregates.to_csv("vehicle_count_sample.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1070389761489238, 'fraction_numerical': 0.011925538103548575, 'mean_word_length': 3.9553314121037464, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '66537', 'license': 'MIT', 'n_tokens_mistral': 1114, 'n_tokens_neox': 1049, 'n_words': 193, 'path': 'CityIQ_VehCount_Hourly_Agg.ipynb', 'repo_name': 'kronellenfitsch/Portland_CARTO_notebook', 'size': '3438'}