How to display a count of combinations from a data set in python

Pas 0
Viewed 47

I have a data set of customers and products and I would like to know which combination of products are more popular combinations chosen by customers and display that in a table (like a traditional Mileage chart or other neat way).

Example dataset:

enter image description here

Example output:

enter image description here

I am able to tell that the most popular combination of products for customers are P1 with P2 and the least popular is P1 with P3. My actual dataset is of course much larger in terms of customers and products. I'd also be keen to hear any ideas on better outputs visualisations too, especially as I can't figure out how to best display 3 way or 4 way popular combinations.

Thank you

python-3.x python
1 Answers

I have a full code example that may work for what you are doing... or at least give you some ideas on how to move forward.

This script uses OpenPyXl to scrape the info from the first sheet. It is turned into a dictionary where the key's are strings of the combinations. The combinations are then counted and it is then placed into a 2nd sheet (see image).

Results:

enter image description here

The Code:

from openpyxl import load_workbook
from collections import Counter

    #Load master workbook/Worksheet and the file to be processed 
data_wb = load_workbook(filename=r"C:\\Users\---Place your loc here---\SO_excel.xlsx") 
data_ws = data_wb['Sheet1']
results_ws = data_wb['Sheet2']

    #Finding Max rows in sheets
data_max_rows = data_ws.max_row 
results_max_rows = results_ws.max_row 

    #Collecting Values and placing in array 
customer_dict = {}

for row in data_ws.iter_rows(min_row = 2, max_col = 2, max_row = data_max_rows): #service_max_rows
        #Gathering row values and creatin var's for relevant ones 
    row_list = [cell.value for cell in row] 
    customer_cell = row_list[0]
    product_cell = row_list[3]
    
        #Creating Str value for dict 
    if customer_cell not in customer_dict:
        words = ""
        words += product_cell
        customer_dict.update({customer_cell:words})
    else:
        words += ("_" + product_cell)
        customer_dict.update({customer_cell:words})

    #Counting Occurances in dict for keys
count_dict = Counter(customer_dict.values())


    #Column Titles
results_ws.cell(1, 1).value = "Combonation"
results_ws.cell(1, 2).value = "Occurances"

    #Placing values into spreadsheet
count = 2 
for key, value in count_dict.items(): 
    results_ws.cell(count, 1).value = key
    results_ws.cell(count, 2).value = value
    count += 1

data_wb.save(filename = r"C:\\Users\---Place your loc here---\SO_excel.xlsx") 
data_wb.close()
BMarshyyy0
Related