<jupyter_start><jupyter_text>Classifying Hourly Timestamps into Day or Night<jupyter_code>import pandas as pd
import numpy as np
import warnings
from df2gspread import df2gspread as d2g
warnings.simplefilter('ignore') 
## Import Raw TTN Data from Google SpreadSheet
url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRlXVQ6c3fKWvtQlFRSRUs5TI3soU7EghlypcptOM8paKXcUH8HjYv90VoJBncuEKYIZGLq477xE58C/pub?gid=0&single=true&output=csv'
df_hourly = pd.read_csv(url,parse_dates = ['time'],infer_datetime_format = True,usecols = [0,3])
df_hourly.head()
## Cleaning and re-organizing the DataFrame
df_hourly.rename(columns={'time': 'TimeStamps'}, inplace=True)
df_hourly.rename(columns={'CarCount': 'VehicleCountperHour'}, inplace=True)

## Strip the Microseconds from the time column
df_hourly['TimeStamps'] = df_hourly['TimeStamps'].values.astype('datetime64[s]')

## Reorder the dataframe
df_hourly = df_hourly.reindex(['TimeStamps','VehicleCountperHour'], axis=1)
df_hourly.tail()<jupyter_output><empty_output><jupyter_text>Importing SunTime Chart for adding Day or Night Classification Here we add Day or Night classification to Hourly Vehicle Count Data using sunrise and sunset times dataframe of Saarbrucken. These times were acquired from [timeanddate.com](https://www.timeanddate.com/sun/germany/saarbrucken) and the dataframe was manually created.<jupyter_code># Reading SunTime Chart of Saarbrücken
url = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRJCZQkgUHTCcx-wvJOog87qq4EFiQ1W6T4akxLSpiqCb3KjYDf_43coltDGG0YcjjsDTxjeXE-O_NH/pub?gid=0&single=true&output=csv'
df_suntimes = pd.read_csv(url)
# Modified Sun Timings DataFrame
df_suntimes_mod = pd.DataFrame()
df_suntimes_mod['SunriseTimeStamp']= pd.to_datetime(df_suntimes['Date'] + ' ' + df_suntimes['Sunrise'])
df_suntimes_mod['SunsetTimeStamp']= pd.to_datetime(df_suntimes['Date'] + ' ' + df_suntimes['Sunset'])
# Querying values in the dataframe to selected dates
start_time = '2018-02-21 07:25:00'
df_suntimes_mod = df_suntimes_mod.loc[df_suntimes_mod.SunriseTimeStamp >= start_time,:]
end_time = '2018-02-25 18:15:00'
df_suntimes_mod = df_suntimes_mod.loc[df_suntimes_mod.SunriseTimeStamp <= end_time,:]
df_suntimes_mod = df_suntimes_mod.reset_index()
df_suntimes_mod = df_suntimes_mod.drop(['index'], 1)
df_suntimes_mod
## Creating a new Dataframe from original dataframe to classify Hourly TimeStamps as 'Day' or 'Night':
df_dn = df_hourly

## Set Everything to Day First
df_dn['DayorNight'] = 'Day'

## Manually fixing first day's night timestamps to 'Night'
night_index = (df_dn.loc[df_dn.TimeStamps <= (df_suntimes_mod['SunriseTimeStamp'][0]),:]).index

# Select Night Time Traffic Only from Sunset today to next day Sunrise
n_days = len(df_suntimes_mod['SunriseTimeStamp'])
for i,j in zip(range(n_days),range(1,n_days)):        
    start_time = df_suntimes_mod['SunsetTimeStamp'][i]
    end_time = df_suntimes_mod['SunriseTimeStamp'][j]
    data = df_dn[(df_dn['TimeStamps'] > start_time) & (df_dn['TimeStamps'] < end_time)]
    night_index = night_index.append(data.index)
    
## Set all the Night TimeStamps to 'Night
df_dn['DayorNight'].iloc[night_index] = 'Night'
df_dn.head(15)
# Writing the file as csv
df_dn.to_csv('data/DayorNight.csv', date_format="%d/%m/%Y %H:%M:%S",index=False)
## Write pandas dataframe to a Google Sheet Using df2spread:

# Insert ID of Google Spreadsheet
spreadsheet = '<KEY>'

# Insert Sheet Name
wks_name = 'CleanedData'

d2g.upload(df_dn,spreadsheet,wks_name,col_names=True,clean=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11996596710153148, 'fraction_numerical': 0.020703346568349403, 'mean_word_length': 7.4176610978520285, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 12, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '1718', 'license': 'MIT', 'n_tokens_mistral': 1403, 'n_tokens_neox': 1339, 'n_words': 256, 'path': 'DataWrangling/ClassifyingTimeStamps.ipynb', 'repo_name': 'diliprk/SmartCityVisualization', 'size': '3565'}