This notebook computes the number of visits at each sample site from an excel file exported from an online database (arcgis online) of site visits.
excel_filepath – path to excel file with site visit records
csv_output_filepath – path to export csv file
In [1]:
excel_filepath = ""
In [2]:
csv_output_filepath = ""
In [1]:
import pandas
In [2]:
file = pandas.read_excel(excel_filepath)
In [3]:
file = file.rename_axis({'NAME':'Name', 'TIME1':'Time', 'WEATHER1':'Weather', 'TEMPERATURE1':'Temperature'}, 1)
In [4]:
data = file[['ID', 'Name', 'Time', 'Weather', 'Temperature']].sort_values(by=['ID', 'Name', 'Time'])
In [5]:
data['ID'] = data['ID'].map('{:g}'.format)
In [6]:
data['Name'] = [n.strip() for n in data['Name']]
In [7]:
counts = data['ID'].value_counts().sort_index()
In [8]:
names = data[['ID', 'Name']].drop_duplicates().set_index('ID')
In [9]:
count = names.join(counts).rename_axis({'ID':'count'}, 1)
In [10]:
count
Out[10]:
In [11]:
data
Out[11]:
In [12]:
data.to_csv(csv_output_filepath)