capstone_project_starbucks_7.py

query = """
        SELECT
            complete.offer_type,
            complete.gender,
            complete.complete_without_view,
            complete_view.complete_with_view,
            (complete.complete_without_view + complete_view.complete_with_view) total_complete
            
        FROM
            (SELECT
                offer_type,
                gender,
                COUNT(*) complete_without_view
            FROM
                complete_data
            WHERE
                viewed = 0
                AND completed = 1
            GROUP BY
                offer_type, gender) complete
        
        JOIN
            (SELECT
                offer_type,
                gender,
                COUNT(*) complete_with_view
            FROM
                complete_data
            WHERE
                viewed = 1
                AND completed = 1
            GROUP BY
                offer_type, gender) complete_view ON complete.offer_type = complete_view.offer_type
                                                     AND complete.gender = complete_view.gender
        """

user_demographic_summary = pdsql.sqldf(query)

# Count the ratio
user_demographic_summary['complete_without_view_ratio'] = round((user_demographic_summary['complete_without_view'] / user_demographic_summary['total_complete']) * 100, 2)
user_demographic_summary['complete_with_view_ratio'] = round((user_demographic_summary['complete_with_view'] / user_demographic_summary['total_complete']) * 100, 2)

# Map the value of gender to its full name
user_demographic_summary['gender'] = user_demographic_summary['gender'].map({'F': 'Female', 'M': 'Male', 'O': 'Others'})


# Start the visualization process
fig, (ax, ax2) = plt.subplots(ncols=2, sharey=True)

# Set the first figure
ax.title.set_text('BOGO Offer')
ax.set_xlabel('Completeness Percentage')
user_demographic_summary[user_demographic_summary['offer_type'] == 'bogo'] \
                        [['gender', 'complete_without_view_ratio', 'complete_with_view_ratio']] \
                        .set_index('gender') \
                        .plot(kind='barh',
                              legend=False,
                              stacked=True,
                              colormap=ListedColormap(sns.color_palette("ch:2.5,-.2,dark=.6")),
                              figsize=(13,5),
                              ax=ax)

# Set the second figure
ax2.title.set_text('Discount Offer')
ax2.set_xlabel('Completeness Percentage')
user_demographic_summary[user_demographic_summary['offer_type'] == 'discount'] \
                        [['gender', 'complete_without_view_ratio', 'complete_with_view_ratio']] \
                        .set_index('gender') \
                        .plot(kind='barh',
                              stacked=True,
                              colormap=ListedColormap(sns.color_palette("ch:2.5,-.2,dark=.6")),
                              figsize=(13,5),
                              ax=ax2)

plt.legend(loc="upper left", bbox_to_anchor=(1,1.02))
plt.show()