In [22]:
import matplotlib.pyplot as plt
import pandas as pd
from pandas.tools.plotting import scatter_matrix
from io import StringIO
import numpy as np
import datetime
import os
import glob

In [23]:
path='./'

In [71]:
#Import ALL csv files into dataframe
allFiles = glob.glob(path + "/*.csv")
frame = pd.DataFrame()
list_ = []
for file_ in allFiles:
    df = pd.read_csv(file_,index_col=None, header=0,skiprows=range(1, 3))
    list_.append(df)
frame = pd.concat(list_);
len(list_) #len(list_) = number of coaches


Out[71]:
18

In [87]:
frame=frame.fillna(0); #Replace NaN with zeros.
print frame.head(3)
len(frame)


  Unnamed: 0  Week 1 Unnamed: 2 Week 2  Unnamed: 4  Week 3 Unnamed: 6  \
0     Monday       0          0    2.5         2.5     2.5        2.5   
1    Tuesday       0          0      0         0.0     1.0          1   
2  Wednesday       0          0    2.5         2.0     2.5        2.5   

        Week 4  Unnamed: 8  Week 5     ...             Week 12  Unnamed: 24  \
0          2.5         2.5     2.5     ...       Thanks giving            0   
1            0         0.0     0.0     ...                   0            0   
2  Career Fair         0.0     2.5     ...                   0            0   

   Week 13  Unnamed: 26 Week 14  Unnamed: 28  Week 15  Unnamed: 30 Week 16  \
0        0            0       0            0        0            0       0   
1        0            0       0            0        0            0       0   
2        0            0       0            0        0            0       0   

   Unnamed: 32  
0            0  
1            0  
2            0  

[3 rows x 33 columns]
Out[87]:
156

In [144]:
print allFiles #Order of coaches' files
start_col=3; #first week of work starts at column number "start_col"
             #week 1: col 3 and 4.  
             #week 2: col 5 and 6
             # ...
             #week n: start_col+2*n and (start_col+2*n)+1
start_row=0;
end_row=8;
increment=8; #Next coach
frame.iloc[start_row:end_row,start_col] #Entire week's per day "expected hours" 
frame.iloc[start_row:end_row, start_col+1] #Entire week's per day "actual hours"
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]


start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]


start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]


start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]

start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]


start_row=start_row+increment;
end_row = end_row + increment;
frame.iloc[start_row+increment:end_row+increment,start_col] #Prints the entire week's per day expected hours 
frame.iloc[start_row+increment:end_row+increment, start_col+1] #Prints the entire week's per day actual hours
print frame.iloc[start_row:end_row, start_col].iloc[-1] #Last entry in column of "Actual hours"
print frame.iloc[start_row:end_row, start_col+1].iloc[-1] #Last entry in column of "Actual hours
print 'current row...', start_row, 'coach...', allFiles[start_row/increment]


['./ParasRavindraGhumare.csv', './DaanishTyrewala.csv', './RobertMikulaMalstrom.csv', './JacobProchnow.csv', './RobbieTian.csv', './JohnStough.csv', './AnnaWheatley.csv', './AdamAugustyniak.csv', './JuliaSmit.csv', './ElizabethBergh.csv', './RileyStroven.csv', './SiddhantDange.csv', './DavidAdamovicz.csv', './JesseLassila.csv', './MarcelloGuadagno.csv', './EricaJacobson.csv', './MaxEllingson.csv', './BrittanyStagman.csv']
9.5
9.0
current row... 0 coach... ./ParasRavindraGhumare.csv
7
7.0
current row... 8 coach... ./DaanishTyrewala.csv
7.5
7.5
current row... 16 coach... ./RobertMikulaMalstrom.csv
8.0
8.0
current row... 24 coach... ./JacobProchnow.csv
4
4.0
current row... 32 coach... ./RobbieTian.csv
7.0
7.0
current row... 40 coach... ./JohnStough.csv
1
1.0
current row... 48 coach... ./AnnaWheatley.csv
1
1.0
current row... 56 coach... ./AdamAugustyniak.csv
1.0
1.0
current row... 64 coach... ./JuliaSmit.csv
0.0
0.0
current row... 72 coach... ./ElizabethBergh.csv
1
1.0
current row... 80 coach... ./RileyStroven.csv
Saturday hours are for Week 1
0.0
current row... 88 coach... ./SiddhantDange.csv
8.5
7.5
current row... 96 coach... ./DavidAdamovicz.csv
6.5
6.5
current row... 104 coach... ./JesseLassila.csv
1.0
1.0
current row... 112 coach... ./MarcelloGuadagno.csv
0
0.0
current row... 120 coach... ./EricaJacobson.csv
0
0.0
current row... 128 coach... ./MaxEllingson.csv
2.0
2.0
current row... 136 coach... ./BrittanyStagman.csv

n=3; start_col=3+2*n; for start_row in range(0,156,8): print 'current row...', start_row, 'coach...', allFiles[start_row/increment] print frame.iloc[start_row:start_row+8, start_col].iloc[-1] #Last entry in column of "Actual hours" print frame.iloc[start_row:start_row+8, start_col+1].iloc[-1] #Last entry in column of "Actual hours" print "-------------------"


In [ ]: