Classwork 18

Pandas Fundamental VI: Group Operation 2

Direction

The dataset ,beer_markets.csv, (with its pathname https://bcdanl.github.io/data/beer_markets.csv) includes information about beer purchases across different markets, brands, and households.

import pandas as pd
import numpy as np

# Below is for an interactive display of DataFrame in Colab
from google.colab import data_table
data_table.enable_dataframe_formatter()

beer = pd.read_csv('https://bcdanl.github.io/data/beer_markets_all.csv')

• Each observation in beer is a household-level transaction record for a purchase of beer.

Variable Description

• hh: an identifier of the household;
• X_purchase_desc: details on the purchased item;
• quantity: the number of items purchased;
• brand: Bud Light, Busch Light, Coors Light, Miller Lite, or Natural Light;
• dollar_spent: total dollar value of purchase;
• beer_floz: total volume of beer, in fluid ounces;
• price_per_floz: price per fl.oz. (i.e., beer spent/beer floz);
• container: the type of container;
• promo: Whether the item was promoted (coupon or otherwise);
• region: US Census region
• state: US state
• market: Designated market area (set by Nielsen);
• demographic data, including gender, marital status, household income, class of work, race, education, age, the size of household, and whether or not the household has a microwave or a dishwasher.

Question 1

What are the descriptive statistics (mean, count, min, max) for dollar_spent and beer_floz grouped by brand?

Show answer

q1_dollar_spent = (
      beer
      .groupby('brand')['dollar_spent']
      .describe()
      )


q1_beer_floz = (
      beer
      .groupby('brand')['beer_floz']
      .describe()
      )

Question 2

• For each market, find the average price_per_floz, total dollar_spent, and total beer_floz.
• Which market has the highest average price_per_floz?

Show answer

q2 = (
      beer
      .groupby('market')
      .agg(
          price_floz_avg = ('price_floz', 'mean'),
          dollar_spent_tot = ('dollar_spent', 'sum'),
          beer_floz_tot = ('beer_floz', 'sum')
          )
      .sort_values('price_floz_avg', ascending = False)
      )

Question 3

• Compute the share of dollar_spent on each brand as a percentage of the total spending by each household.
  • In the resulting DataFrame, retain only the following three variables:
    1. hh (household ID)
    2. brand
    3. total dollars spent by the household
    4. The calculated percentage contribution of spending on that brand
  • Ensure the resulting DataFrame contains only one row per household–brand pair.

Show answer

q3 = (
      beer
      .groupby(['household', 'brand'])
      .agg(dollar_spent_per_brand = ('dollar_spent', 'sum'))
      )

q3['dollar_spent_tot'] = q3.groupby('household')['dollar_spent_per_brand'].transform('sum')

q3['dollar_spent_pct'] = q3['dollar_spent_per_brand'] / q3['dollar_spent_tot']

Question 4

• Identify the brand with the highest price_per_floz in each market using a custom function with apply().

Show answer

q4 = (
      beer
      .groupby('market')
      .apply(lambda df: df.nlargest(1, 'price_floz', keep = 'all'))
      )

Question 5

• Among households that have purchased BUD LIGHT at least once, what proportion only bought BUD LIGHT?

• Among households that have purchased BUSCH LIGHT at least once, what proportion only bought BUSCH LIGHT?

• Among households that have purchased COORS LIGHT at least once, what proportion only bought COORS LIGHT?

• Among households that have purchased MILLER LITE at least once, what proportion only bought MILLER LITE?

• Among households that have purchased NATURAL LIGHT at least once, what proportion only bought NATURAL LIGHT?

• Which of these beer brands has the highest share of loyal customers, who purchase no other brand?

• For example, let’s consider the following example DataFrame with the four households:

In this example, households 1, 2 and 3 purchased BUD at least once, and only the households 1 and 3 purchased BUD only. Therefore, among households that have purchased BUD LIGHT at least once, the proportion only bought BUD is 2/3 = 66.67%.

Show answer

q5_bud = (
    beer[beer['brand'] == 'BUD LIGHT'][['household']]
    .drop_duplicates()
    ) 

len(q5_bud) # household that purchased BUD at least once

q5_bud_loyal = (
    beer[beer['household'].isin(q5_bud['household'])][['household', 'brand']]
    .value_counts()
    .reset_index()['household']
    .value_counts()
    .reset_index()
    .query('count == 1')
    )

len(q5_bud_loyal)

100 * len(q5_bud_loyal) / len(q5_bud)

def loyal(brand_beer):
    q5_brand_beer = (
        beer[beer['brand'] == brand_beer][['household']]
        .drop_duplicates()
        ) 

    q5_brand_beer_loyal = (
        beer[beer['household'].isin(q5_brand_beer['household'])][['household', 'brand']]
        .value_counts()
        .reset_index()['household']
        .value_counts()
        .reset_index()
        .query('count == 1')
        )

    return 100 * len(q5_brand_beer_loyal) / len(q5_brand_beer)

# beer['brand'].unique()

lst = []
for item in beer['brand'].unique():
    print(item, loyal(item))    
    lst.append({
        'brand': item,
        'loyal_pct': loyal(item)
        })

pd.DataFrame(lst)

Discussion

Welcome to our Classwork 18 Discussion Board! 👋

This space is designed for you to engage with your classmates about the material covered in Classwork 18.

Whether you are looking to delve deeper into the content, share insights, or have questions about the content, this is the perfect place for you.

If you have any specific questions for Byeong-Hak (@bcdanl) regarding the Classwork 18 materials or need clarification on any points, don’t hesitate to ask here.

Let’s collaborate and learn from each other!