# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# +
# %reset

import matplotlib.pyplot as plt
import pandas as pd
import os
from os import listdir
from os.path import isfile, join
import numpy as np
import csv
import datetime
import time
from datetime import datetime
from datetime import timedelta
import dill

###set working directory
os.chdir('insert directory here')

dill.load_session(os.getcwd() + '/clean_webinar_data.db')
data=webinar_data_all

#specify timestamps with 1 minute frequency for each conference day
#Day 1
start1=pd.Timestamp(2020, 5, 18, 10, 0, 0)
end1=pd.Timestamp(2020, 5, 18, 13, 15, 0)
timestamps1 = pd.date_range(start=start1,end=end1,freq='min')

#Day 2
start2=pd.Timestamp(2020, 5, 19, 10, 0, 0)
end2=pd.Timestamp(2020, 5, 19, 12, 15, 0)
timestamps2 = pd.date_range(start=start2,end=end2,freq='min')

#Day 3
start3=pd.Timestamp(2020, 5, 20, 10, 0, 0)
end3=pd.Timestamp(2020, 5, 20, 15, 0, 0)
timestamps3 = pd.date_range(start=start3,end=end3,freq='min')

#Day 4
start4=pd.Timestamp(2020, 5, 21, 10, 0, 0)
end4=pd.Timestamp(2020, 5, 21, 15, 0, 0)
timestamps4 = pd.date_range(start=start4,end=end4,freq='min')

stamps_total=timestamps1.append(timestamps2)
stamps_total=stamps_total.append(timestamps3)
stamps_total=stamps_total.append(timestamps4)

#create list of unique participants
participants = data['Participant'].tolist()
participants=list(set(participants))

#create dataframe of with columns for unique participants
tseries = pd.DataFrame(columns = participants)
#set index to timestamps for conference days
tseries=tseries.reindex(stamps_total)
#set all table values to 0
for col in tseries.columns:
    tseries[col].values[:] = 0

#cycle through each unique participant
for person in tseries.columns:
#    print(person)
    #get data for unique participant
    person_data = data.loc[data['Participant']==person]
    #cycle through each timestamp of conference
    for stamp in tseries.index:
        #cycle through each logged entry or exit stamp for unique participant
        for row in person_data.index:
            #check if conference timestamp of interest is within the range of this login
            if stamp > person_data.loc[row,'Join Time'] and stamp < person_data.loc[row,'Leave Time']:
                    #list the room that this unique particpant was in at this timestamp
                    tseries.loc[stamp,person] = person_data.loc[row,'Room']

#create columns for rooms to sum the number of participants in attendance
tseries['Room1']=0
tseries['Room2']=0
tseries['Room3']=0
tseries['Room4']=0
tseries['Room5']=0    
#cycle through conference timestamps and sum the number of participants in each room at the timestamp
for stamp in tseries.index:
    tseries.loc[stamp,'Room1'] = sum(1 for i in list(tseries.loc[stamp,:]) if i == 1)
    tseries.loc[stamp,'Room2'] = sum(1 for i in list(tseries.loc[stamp,:]) if i == 2)
    tseries.loc[stamp,'Room3'] = sum(1 for i in list(tseries.loc[stamp,:]) if i == 3)
    tseries.loc[stamp,'Room4'] = sum(1 for i in list(tseries.loc[stamp,:]) if i == 4)
    tseries.loc[stamp,'Room5'] = sum(1 for i in list(tseries.loc[stamp,:]) if i == 5)

#sum the attendance in all five webinar rooms
tseries['Total'] = tseries['Room1']+tseries['Room2']+tseries['Room3']+tseries['Room4']+tseries['Room5']

#reformat dataframe for importing to Tableau
tab_data=pd.DataFrame()
room1=pd.DataFrame(tseries['Room1'])
room1=room1.rename(columns={"Room1":"Quantity"})
room1['Room']='Room1'
tab_data=tab_data.append(room1)
room2=pd.DataFrame(tseries['Room2'])
room2=room2.rename(columns={"Room2":"Quantity"})
room2['Room']='Room2'
tab_data=tab_data.append(room2)
room3=pd.DataFrame(tseries['Room3'])
room3=room3.rename(columns={"Room3":"Quantity"})
room3['Room']='Room3'
tab_data=tab_data.append(room3)
room4=pd.DataFrame(tseries['Room4'])
room4=room4.rename(columns={"Room4":"Quantity"})
room4['Room']='Room4'
tab_data=tab_data.append(room4)
room5=pd.DataFrame(tseries['Room5'])
room5=room5.rename(columns={"Room5":"Quantity"})
room5['Room']='Room5'
tab_data=tab_data.append(room5)

#Create tableau_data export folder if it doesn't exist
if not os.path.exists(os.getcwd() + '/tableau_data'):
    os.makedirs(os.getcwd() + '/tableau_data')

#Export time series of room attendance and reformatted data for tableau
tab_data.to_csv(os.getcwd() + '/tableau_data/tableau_data.csv',index_label='Timestamp')
tseries.to_csv(os.getcwd() + '/tableau_data/tseries_data.csv',index_label='Timestamp')

#Export environment as database file
dill.dump_session(os.getcwd() + '/tableau_data/attendees_per_session.db')
# -



StarcoderdataJupyterScriptsDedupFiltered