Skip to content

Instantly share code, notes, and snippets.

@dzakyputra

dzakyputra/capstone_project_starbucks_6.py

Created June 17, 2020 00:23
Show Gist options
  • Save dzakyputra/15c2c86c05f256c45fcfd03642c032be to your computer and use it in GitHub Desktop.
Save dzakyputra/15c2c86c05f256c45fcfd03642c032be to your computer and use it in GitHub Desktop.
Download ZIP
Raw
capstone_project_starbucks_6.py
query = """
SELECT
complete.offer_id,
complete.offer_type,
total_completed,
total_completed_without_view,
ROUND(((1.0*total_completed_without_view) / (1.0*total_completed))*100, 2) as total_completed_without_view_ratio,
100 - ROUND(((1.0*total_completed_without_view) / (1.0*total_completed))*100, 2) as total_completed_with_view_ratio,
`loss ($)`
FROM
(SELECT
offer_id,
offer_type,
COUNT(*) AS total_completed
FROM
complete_data
WHERE
completed = 1
GROUP BY
offer_id) complete
JOIN
(SELECT
offer_id,
offer_type,
COUNT(*) AS total_completed_without_view,
SUM(reward) AS `loss ($)`
FROM
complete_data
WHERE
viewed = 0
AND completed = 1
GROUP BY
offer_id) complete_not_view ON complete.offer_id = complete_not_view.offer_id
ORDER BY
total_completed_without_view_ratio DESC
"""
completed_without_view = pdsql.sqldf(query)
# Start the visualization process
viz = completed_without_view[['offer_id', 'loss ($)', 'offer_type']].set_index('offer_id') \
.sort_values('loss ($)')
colors = tuple(np.where(viz['offer_type'] == 'discount', '#C6E5CC', '#6fb08e'))
viz['loss ($)'].plot(kind='barh',
color=colors,
figsize=(10,5))
discount = mpatches.Patch(color='#C6E5CC', label='Discount')
bogo = mpatches.Patch(color='#6fb08e', label='BOGO')
plt.legend(handles=[bogo, discount])
plt.title('Total Loss for Each Offer Id')
plt.xlabel('Loss ($)')
plt.show()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment