In [97]:
import numpy as np
In [98]:
import pandas as pd
In [99]:
#import begins
In [100]:
df = pd.read_csv('la.csv', error_bad_lines=False, skiprows=2)
In [101]:
#import ends
In [102]:
#munging begins
In [103]:
df_cleaned = df.drop(['Unnamed: 6'], axis=1)
In [104]:
df_cleaned = df_cleaned[pd.notnull(df_cleaned['Month'])]
In [105]:
df_cleaned['Absences/Lates (Hours)'] = 0.0
In [106]:
df_cleaned['Absences/Lates (Hours)'] = df_cleaned['Duration'].str.split(':').apply(lambda x: (int(x[0]) * 60 + int(x[1])) / 60)
In [107]:
#munging ends
In [108]:
#formatting begins
In [ ]:
df_cleaned.pivot_table(index=['Team', 'Agent'], values='Absences/Lates (Hours)', margins=True, aggfunc=sum)