import pandas as pd
import numpy as np
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))

scraped_comp = pd.read_csv('../data/scraped/scraped_complaints_3_25.csv')

scraped_comp['abuse_number'] = scraped_comp['abuse_number'].apply(lambda x: x.upper())

manual = pd.read_excel('/Users/fzarkhin/OneDrive - Advance Central Services, Inc/fproj/github/database-story/scraper/manual verification.xlsx', sheetname='All manual')

manual = manual.groupby('name').sum().reset_index()

manual['name']= manual['name'].apply(lambda x: x.strip())
scraped_comp['fac_name']= scraped_comp['fac_name'].apply(lambda x: x.strip())

df = scraped_comp.groupby('fac_name').count().reset_index()[['fac_name','abuse_number']]

merge1 = manual.merge(df, how = 'left', left_on = 'name', right_on='fac_name')

"""
Explanation: A significant portion of the checking was done in the Excel file 'manual verification.'
In essence, I searched the state's site (https://apps.state.or.us/cf2/spd/facility_complaints/) using the same criteria as in the scraper, then copy-pasted the resulting list of totals by facility into a spreadsheet. I summed them up and compared them to what the scraper got me.
There were some differences between the two. 

The totals were off by 5. I manually checked. The totals didn't correspond to the actual numbers on the facility pages.
The number of complaints per facility type did not match. I don't know why this is, but I don't see it as a problem because the totals are accurate, and we don't need to know the right facility type from the scraped data.
Four facilities didn't join on 'name.' Checked each one. Each had the right number of complaints scraped.
End of explanation
"""


merge1[merge1['count']!=merge1['abuse_number']].sort_values('abuse_number')#.sum()

manual[manual['name']=='AVAMERE AT SANDY']

scraped_comp[scraped_comp['abuse_number']=='BH116622B']

scraped_comp[scraped_comp['fac_name'].str.contains('FLAGSTONE RETIREME')]

merge2 = manual.merge(df, how = 'right', left_on = 'name', right_on='fac_name')

merge2[merge2['count']!=merge2['abuse_number']].sort_values('count')#.sum()

"""
Explanation: Five facilities did not correspond. Manual checks shows inaccurate online data.
End of explanation
"""


