& (sales_per_retailer.index < pd.to_datetime('2021-01-01'))].sum()
print('Sales per retailer in MA in 2020: %.2fM' % (avg_2020_sales / 1_000_000))
#--------------------------------------------------------------------------
# Calculate retailers per population.
#--------------------------------------------------------------------------
from fredapi import Fred
# Initialize FRED API client.
fred_api_key = config.get('FRED_API_KEY')
fred = Fred(api_key=fred_api_key)
# Get MA population (conjecturing that population remains constant in 2021).
observation_start = production.index.min().isoformat()
population = fred.get_series('MAPOP', observation_start=observation_start)
population = end_of_period_timeseries(population, 'Y')
population = population.multiply(1000) # thousands of people
new_row = pd.DataFrame([population[-1]], index=[pd.to_datetime('2021-12-31')])
population = pd.concat([population, pd.DataFrame(new_row)], ignore_index=False)
# Calculate retailers per population.
weekly_population = population[0].resample('W-SUN').mean().pad()
retailers_per_capita = weekly_total_retailers / (weekly_population / 100_000)
retailers_per_capita.plot()
# Calculate retailers per capita in 2020.
avg_retailers_per_capita_2020 = retailers_per_capita.loc[
(retailers_per_capita.index >= pd.to_datetime('2020-01-01'))
& (retailers_per_capita.index < pd.to_datetime('2021-01-01'))].mean()
print('Retailres per capita in MA in 2020: %.2f' %
avg_retailers_per_capita_2020)
& (sales_per_retailer.index < pd.to_datetime('2021-01-01'))].sum()
print('Sales per retailer in MA in 2020: %.2fM' % (avg_2020_sales / 1_000_000))
#--------------------------------------------------------------------------
# Calculate retailers per population.
#--------------------------------------------------------------------------
from fredapi import Fred
# Initialize FRED API client.
fred_api_key = config.get('FRED_API_KEY')
fred = Fred(api_key=fred_api_key)
# Get MA population (conjecturing that population remains constant in 2021).
observation_start = production.index.min().isoformat()
population = fred.get_series('MAPOP', observation_start=observation_start)
population = end_of_period_timeseries(population, 'Y')
population = population.multiply(1000) # thousands of people
new_row = pd.DataFrame([population[-1]], index=[pd.to_datetime('2021-12-31')])
population = pd.concat([population, pd.DataFrame(new_row)], ignore_index=False)
# Calculate retailers per population.
weekly_population = population[0].resample('W-SUN').mean().pad()
retailers_per_capita = weekly_total_retailers / (weekly_population / 100_000)
retailers_per_capita.plot()
# Calculate retailers per capita in 2020.
avg_retailers_per_capita_2020 = retailers_per_capita.loc[
(retailers_per_capita.index >= pd.to_datetime('2020-01-01'))
& (retailers_per_capita.index < pd.to_datetime('2021-01-01'))].mean()
print('Retailres per capita in MA in 2020: %.2f' %
avg_retailers_per_capita_2020)
return pd.NaT
# Set the time index.
dates = sales_data.month.map(str) + ' ' + sales_data.year.map(str)
dates = dates.apply(month_year_to_date)
sales_data.index = dates
sales_data = sales_data.loc[sales_data.index.notnull()]
sales_data.sort_index(inplace=True)
# Convert string columns to numeric, handling dollar signs.
sales_data[sales_data.columns[1:]] = sales_data[sales_data.columns[1:]] \
.replace('[\$,]', '', regex=True).astype(float)
# Set the index as the end of the month.
sales_data = end_of_period_timeseries(sales_data)
# Save the sales data.
sales_data.to_excel('./data/sales_data_il.xlsx', sheet_name='Data')
#-----------------------------------------------------------------------------
# Calculate Illinois retailer statistics.
#-----------------------------------------------------------------------------
# Format the `license_issue_date`.
licensees['issue_date'] = pd.to_datetime(licensees['license_issue_date'])
# Create total retailers by month series.
total_retailers = []
for index, _ in sales_data.iterrows():
licensed_retailers = licensees.loc[licensees['issue_date'] <= index]
# Optional: Estimate a production function with the forecasted values
# and calculate the estimated competitive wage and interest rate,
# getting supplemental data from FRED (Federal Reserve Economic Data).
#--------------------------------------------------------------------------
# Initialize Fred client.
from fredapi import Fred
config = dotenv_values('../.env')
fred = Fred(api_key=config.get('FRED_API_KEY'))
# Find the observation time start.
observation_start = production.index.min().isoformat()
# Get average weekly hours worked in MA.
avg_weekly_hours = fred.get_series('SMU25000000500000002SA', observation_start=observation_start)
avg_weekly_hours = end_of_period_timeseries(avg_weekly_hours)
avg_weekly_hours = avg_weekly_hours.resample('W-Sun').ffill().iloc[:-1]
#--------------------------------------------------------------------------
# Optional: Estimate historic competitive wages and interest rates.
#--------------------------------------------------------------------------
# Define variables.
Y = weekly_sales
K = weekly_plants
L = weekly_employees * avg_weekly_hours
# Exclude missing observations.
missing_sales = Y.loc[Y == 0].index
Y = Y[~Y.index.isin(missing_sales)]
K = K[~K.index.isin(missing_sales)]
& (sales_per_retailer.index < pd.to_datetime('2021-01-01'))].sum()
print('Sales per retailer in MA in 2020: %.2fM' % (avg_2020_sales / 1_000_000))
#--------------------------------------------------------------------------
# Calculate retailers per population.
#--------------------------------------------------------------------------
from fredapi import Fred
# Initialize FRED API client.
fred_api_key = config.get('FRED_API_KEY')
fred = Fred(api_key=fred_api_key)
# Get MA population (conjecturing that population remains constant in 2021).
observation_start = production.index.min().isoformat()
population = fred.get_series('MAPOP', observation_start=observation_start)
population = end_of_period_timeseries(population, 'Y')
population = population.multiply(1000) # thousands of people
new_row = pd.DataFrame([population[-1]], index=[pd.to_datetime('2021-12-31')])
population = pd.concat([population, pd.DataFrame(new_row)], ignore_index=False)
# Calculate retailers per population.
weekly_population = population[0].resample('W-SUN').mean().pad()
retailers_per_capita = weekly_total_retailers / (weekly_population / 100_000)
retailers_per_capita.plot()
# Calculate retailers per capita in 2020.
avg_retailers_per_capita_2020 = retailers_per_capita.loc[
(retailers_per_capita.index >= pd.to_datetime('2020-01-01'))
& (retailers_per_capita.index < pd.to_datetime('2021-01-01'))].mean()
print('Retailres per capita in MA in 2020: %.2f' %
avg_retailers_per_capita_2020)
# Optional: Estimate a production function with the forecasted values
# and calculate the estimated competitive wage and interest rate,
# getting supplemental data from FRED (Federal Reserve Economic Data).
#--------------------------------------------------------------------------
# Initialize Fred client.
from fredapi import Fred
config = dotenv_values('../.env')
fred = Fred(api_key=config.get('FRED_API_KEY'))
# Find the observation time start.
observation_start = production.index.min().isoformat()
# Get average weekly hours worked in MA.
avg_weekly_hours = fred.get_series('SMU25000000500000002SA', observation_start=observation_start)
avg_weekly_hours = end_of_period_timeseries(avg_weekly_hours)
avg_weekly_hours = avg_weekly_hours.resample('W-Sun').ffill().iloc[:-1]
# Get Average Hourly Earnings of All Employees: Total Private in Massachusetts
# SMU25000000500000003SA
avg_earnings = fred.get_series('SMU25000000500000003SA', observation_start=observation_start)
avg_earnings = end_of_period_timeseries(avg_earnings)
avg_earnings = avg_earnings.resample('W-Sun').ffill().iloc[:-1]
# Get the state minimum wage rate for Massachusetts.
min_wage = fred.get_series('STTMINWGMA', observation_start=observation_start)
min_wage = end_of_period_timeseries(min_wage)
min_wage = min_wage.resample('W-Sun').ffill().iloc[:-1]
#--------------------------------------------------------------------------
# Optional: Estimate historic competitive wages and interest rates.
production.set_index('date', inplace=True)
monthly_avg_production = production.resample('M').mean()
quarterly_avg_production = production.resample('Q').mean()
monthly_total_production = production.resample('M').sum()
quarterly_total_production = production.resample('Q').sum()
# Get the monthly average price per ounce.
url = f'{base}/rqtv-uenj.json'
params = {'$limit': 10000, '$order': 'date DESC'}
response = requests.get(url, headers=headers, params=params)
prices = pd.DataFrame(response.json(), dtype=float)
prices = reverse_dataframe(prices)
# prices = end_of_period_timeseries(prices)
# prices.set_index('date')
prices.index = pd.to_datetime(prices.date)
prices = end_of_period_timeseries(prices)
#--------------------------------------------------------------------------
# Get supplemental data from FRED (Federal Reserve Economic Data).
#--------------------------------------------------------------------------
# Initialize Fred client.
config = dotenv_values('../.env')
fred = Fred(api_key=config.get('FRED_API_KEY'))
# Find the observation time start.
observation_start = prices.index.min()
# Get the Federal funds interest rate.
interest_rate = fred.get_series('FEDFUNDS', observation_start=observation_start)
interest_rate = end_of_period_timeseries(interest_rate)
filename = './data/2021-12-06-MJ-Sales-Activity-by-License-Number-Traceability-Contingency-Reporting-Retail.xlsx'
data = pd.read_excel(filename, skiprows=3)
# Remove null values.
data = data.loc[data['License Number'].notnull()]
# Create a date column.
data['date'] = pd.to_datetime(data['Reporting Period'])
# Get the Washington State population.
config = dotenv_values('../.env')
fred_api_key = config.get('FRED_API_KEY')
fred = Fred(api_key=fred_api_key)
observation_start = data['date'].min().isoformat()
population = fred.get_series('WAPOP', observation_start=observation_start)
population = end_of_period_timeseries(population, 'Y')
population = population.multiply(1000) # thousands of people
new_row = pd.DataFrame([population[-1]], index=[pd.to_datetime('2021-12-31')])
population = pd.concat([population, pd.DataFrame(new_row)], ignore_index=False)
monthly_population = population[0].resample('M').mean().pad()
#--------------------------------------------------------------------------
# Look at monthly sales data over time.
#--------------------------------------------------------------------------
# Plot monthly sales.
monthly_production = data.groupby('Reporting Period').sum()
ax = monthly_production['Total Sales'].plot()
yaxis_format = FuncFormatter(format_millions)
ax.yaxis.set_major_formatter(yaxis_format)
plt.show()