Is there a simpler way to get object from groupby and putting in dictionary?

user_17128225 0
Viewed 125

So my dataframe looks like this: dataframe snapshot I am trying to find a simpler way to get the objects from a Groupby and then putting it in a dictionary. I had to obtain the index and then do a for loop to get the exact string of each row in Product.

More details if needed: My goal was to find duplicate Order ID and then take the products from the column and add to dictionary with:

  • key = Product

  • value = no of times the Product is found to be ordered together

(I am not looking for ways to optimize finding duplicates, i know I could use df.duplicated )

Code:

for date, df in df1.groupby('Order Date'):
    
    if  df.Product.count() > 1:

        indice = df.Product.index
        for data in indice:
            product = df.loc[data].at['Product']
            #update dictionary counter
            product_dict[product] = product_dict.get(product) + 1
      
    else:
        continue

for ease you can use this df instead. I listed as dictionary:

{'Order ID': ['147268', '148041', '149343', '149964', '149350', '141732', '149620', '142451', '146039', '143498', '141316', '144804', '144804', '145270', '142789'],
 'Product': ['Wired Headphones', 'USB-C Charging Cable', 'Apple Airpods Headphones', 'AAA Batteries (4-pack)', 'USB-C Charging Cable', 'iPhone', 'Lightning Charging Cable', 'AAA Batteries (4-pack)', '34in Ultrawide Monitor', 'AA Batteries (4-pack)', 'AAA Batteries (4-pack)', 'Wired Headphones', 'iPhone', 'Google Phone', 'AAA Batteries (4-pack)']}

The expected output:

{'Wired Headphones': 8090, 'USB-C Charging Cable': 9425, 'Apple Airpods Headphones': 6374, 'AAA Batteries (4-pack)': 8266, 'iPhone': 3663, 'Lightning Charging Cable': 9074, '34in Ultrawide Monitor': 2500, 'AA Batteries (4-pack)': 8167, 'Google Phone': 3091, 'Macbook Pro Laptop': 1878, 'ThinkPad Laptop': 1605, '27in FHD Monitor': 3010, 'Bose SoundSport Headphones': 5459, 'Flatscreen TV': 1827, '27in 4K Gaming Monitor': 2457, 'LG Dryer': 257, '20in Monitor': 1635, 'LG Washing Machine': 268, 'Vareebadd Phone': 1120}

pandas-groupby pandas dataframe python
2 Answers
# number of products per order 
prods_per_order = df.groupby(['Order ID'])["Product"].transform("count")

res = ( 
    df.loc[prods_per_order > 1, "Product"]   # Select only the products that were ordered together with another(s) product(s)
      .value_counts()      # count how many times were per product 
      .to_dict()           # convert the result to a dict 
)

Input

df = pd.DataFrame({
    'Order ID': ['147268', '148041', '149343', '149964', '149350', 
                 '141732', '149620', '142451', '146039', '143498', 
                 '141316', '144804', '144804', '145270', '142789'],
     'Product': ['Wired Headphones', 'USB-C Charging Cable', 'Apple Airpods Headphones', 
                 'AAA Batteries (4-pack)', 'USB-C Charging Cable', 'iPhone', 
                 'Lightning Charging Cable', 'AAA Batteries (4-pack)', '34in Ultrawide Monitor', 
                 'AA Batteries (4-pack)', 'AAA Batteries (4-pack)', 'Wired Headphones', 
                 'iPhone', 'Google Phone', 'AAA Batteries (4-pack)']
})

df = df.sort_values(['Order ID', 'Product'])

>>> df 

   Order ID                   Product
10   141316    AAA Batteries (4-pack)
5    141732                    iPhone
7    142451    AAA Batteries (4-pack)
14   142789    AAA Batteries (4-pack)
9    143498     AA Batteries (4-pack)
11   144804          Wired Headphones  # <-- Note that only these two products
12   144804                    iPhone  # <--    were ordered together 
13   145270              Google Phone
8    146039    34in Ultrawide Monitor
0    147268          Wired Headphones
1    148041      USB-C Charging Cable
2    149343  Apple Airpods Headphones
4    149350      USB-C Charging Cable
6    149620  Lightning Charging Cable
3    149964    AAA Batteries (4-pack)

Output

>>> res

{'iPhone': 1, 'Wired Headphones': 1}
user_171206920

Maybe I misunderstand, but this seems to do what you are trying to achieve by using a Counter:

from collections import Counter

mask = (
    df.groupby(["Order Date", "Order ID"], sort=False)["Product"]
      .transform("count")
      .gt(1)
)
product_dict = Counter(df.loc[mask, "Product"])

Result for the slightly modified sample dataframe (added an Order Date column)

   Order Date Order ID                   Product
0  2021-11-11   147268          Wired Headphones
1  2021-11-11   148041      USB-C Charging Cable
2  2021-11-11   149343  Apple Airpods Headphones
3  2021-11-11   149964    AAA Batteries (4-pack)
4  2021-11-11   149350      USB-C Charging Cable
5  2021-11-12   141732                    iPhone
6  2021-11-12   149620  Lightning Charging Cable
7  2021-11-12   142451    AAA Batteries (4-pack)
8  2021-11-12   146039    34in Ultrawide Monitor
9  2021-11-12   143498     AA Batteries (4-pack)
10 2021-11-12   141316    AAA Batteries (4-pack)
11 2021-11-12   144804          Wired Headphones
12 2021-11-12   144804                    iPhone
13 2021-11-12   145270              Google Phone
14 2021-11-12   142789    AAA Batteries (4-pack)

is

Counter({'Wired Headphones': 1, 'iPhone': 1})

Maybe it's enough to groupby over Order ID, but since you are grouping over Order Date I suspect it isn't.

user_143112630
Related