In [ ]:
# http://pbpython.com/excel-file-combine.html
In [119]:
import datetime
import pandas as pd
In [30]:
df = pd.read_excel('/Users/copelco/Desktop/PIALog_16-0806.xlsx', sheetname=[0,1,2])
In [69]:
df[1].columns = df[0].columns
df[2].columns = df[0].columns
stops = df[0].append(df[1], ignore_index=True)
stops = stops.append(df[2], ignore_index=True)
In [71]:
len(stops.index)
Out[71]:
In [97]:
stops.AGENCY.value_counts()
Out[97]:
In [78]:
# covert datetime column
stops['datetime'] = pd.to_datetime(stops['STOPDATE'])
In [128]:
stops.dtypes
Out[128]:
In [129]:
years = stops['datetime'].map(lambda x: x.year)
stops.groupby(years)['datetime'].count()
Out[129]:
In [132]:
stops.groupby([years, stops.ETHNICITY])['ETHNICITY'].count()
Out[132]:
2012 looks messy ^^
In [133]:
stops.groupby([years, stops.SEIZED])['SEIZED'].count()
Out[133]:
In [136]:
stops.groupby([years, stops.STOPOUTCOME])['STOPOUTCOME'].count()
Out[136]:
In [134]:
BACOPD = stops.AGENCY == 'BACOPD'
In [137]:
stops[BACOPD].groupby(years)['datetime'].count()
Out[137]:
In [ ]: