#!/usr/bin/env python
# coding: utf-8

# In[2]:


#import libraries required for analysis
import pandas as pd
import numpy as np
from ebmdatalab import bq


# In[3]:


#obtain overall data for formulary prescribing at Guildford and Waveney CCG

sql = """
select month, bnf_name, bnf_code, sum(quantity) as quantity, sum(actual_cost) as actual_cost
from `hscic.normalised_prescribing_standard` 
where
bnf_code like '1404000H0%'
and month >='2011-04-01'
group by month, bnf_name, bnf_code
order by month
"""
flu_df = bq.cached_read(sql, csv_path='flu_df.csv')
flu_df.head()


# In[7]:


grp_flu_df=flu_df.groupby('month').sum()
grp_flu_df['actual_cost'] = grp_flu_df['actual_cost'].map('£{:,.0f}'.format)


# In[8]:


grp_flu_df.head(200)


# In[ ]: