The variables item1-item50 on each row contain names of cereals that are priced at the same price point.
The dataset has about 22,000 rows.
I had trouble exporting a realistic sample of the data..so I provide a simplified synthetic version of my data with just 5 rows and 5 items
In the simplified example data below, each row is a price point in a store (eg. $2.99, $3.99 in row 1.2) and item1-item 5 are cereal brands that are priced at that price point.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str7 chain float(store_num price) str2 item1 str1(item2 item3 item4 item5 var9) "Chain A" 1 2.99 "a" "b" "c" "d" "x" "" "Chain A" 2 3.99 "b" "c" "d" "e" "a" "" "Chain a" 2 7.99 "m" "n" "p" "p" " " "" "Chain A" 3 3.99 "a" "e" "f" "g" " " "" "Chain A" 4 4.99 "k" "f" "g" "a" "b" "" "Chain A" 5 2.99 "c" "d" "x" "y" "z" "" end
* tabulate the distinct sets of items that co-occur i.e. the sets of items that are priced at the same price point
In the example above,
store#1 and store2 have a, b, c, d priced at the same price point (though the order in which they are listed differ)
store# 3 and 4 have a, f, g priced at the same price point
My broader goal is to try to understand the logic that leads store managers to think of co-occurring items as a "bundle" and place the set at the same price point.
* What I have tried
- groups (var-var5) provides occurrences of permutations of itemv1-itemv5 rather than combinations. so this does not work.
- I sorted itemv1-itemvn within row (with reshapes and sort) and them concatenated the strings in itemv1-itemvn
- Using the concatenated variable, I tried to use strpos in a loop looking for the occurrence of each cereal item (there are a total of about 150 distinct cereal items in my date)
I discovered I dont have the programming chops to accomplish this.
I am really stuck at this point...
Maybe the most tractable way forward it to focus only on the most common cereals and create combinations that may occur.
For example, for 3 popular cereals - cereal1, cereal2 and cereal3, I could have a loop to create dummy 0/1 dummy variables in each row for 4 combinations (of greater than 1 item) - i) c1 c2, ii) c2 c3, iii) c1 c3, iv)c1 c2 c3.
If I focus on four most popular cereals, the loop would create 11 dummy variables to code the combinations as being 1 or 0.
The summary of these dummy variables would let me know the frequency of occurrence of these sets.
I am wondering if there is a better way...and if this problem has already been solved with some cleaner logic.
In case this helps, I am providing a subset of my data with individual cereal names (item1-item7) concatenated in str_brand.
I just realized this doesnt have strings sorted .
n_br_pp indicates the number of items in the price_point
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str7 chain long store_num float price_0_price byte n_br_pp str1948 str_brand "Chain A" 3 1.59 4 "QKR OVRNGT OTS PCH,QKR OVRNGT OTS RSW,QKR OVERNIGHT OATS BN BB,QKR OVRNGT OTS CCN" "Chain A" 3 3.59 5 "GMI VERY BRY CHEERIOS,GMI CHOCOLATE CHEERIOS,GMI HONEY NUT CHEERIOS MDLY CR,GMI FROSTED CHEERIOS,GMI APPLE CINN CHEERIOS" "Chain A" 3 3.14 5 "NATURE'S PATH FLAX PLUS RB CRN,KEL RICE KRISPIES ORIG,NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH CRUNCHY VNLL SNR,NATURE'S PATH FLAX PLUS PKN RC" "Chain A" 3 3.29 5 "POST GREAT GRAINS CRUNCHY PCN,QKR CRUNCHY CORN BRAN,POS H-C GRAPE NUTS ORIG,POS GREAT GRAINS CRBY ALM CRN,POS HBOATS STRAWBERRY" "Chain A" 3 .99 5 "GMI LUCKY CHARMS REG,GMI HONEY NUT CHEERIOS REG,KEL FROOT LOOPS REG,GMI CHEERIOS,KEL FROSTED FLAKES ORIG" "Chain A" 3 3.79 7 "KASHI ORGANIC PROMISE BF PS,GMI NV BK OT BTS CN,KASHI DRK COCOA KARMA,GMI RAISIN NUT BRAN,GMI WHEATIES,KASHI AUTUMN WHEAT PROJECT SPK,KASHI CINNAMON HARVEST" "Chain A" 3 2.69 7 "GMI CN TOAST CRUNCH REG,GMI MULTIGRAIN CHEERIOS REG,GMI APPLE CN TOAST CRUNCH,GMI HONEY NUT CHEERIOS REG,GMI TINY TST BB,GMI TINY TST STB,GMI FRENCH TOAST CRUNCH" "Chain A" 3 4.99 5 "MAMMA CHIA GRN,NATURE'S PATH GRN,BAKERY ON MAIN GRN,PURELY ELIZABETH GRN,MOM FRST MINI SPOONERS BAG" "Chain A" 3 4.19 8 "VAN'S CINNAMON HEAVEN,GMI KIX,VAN'S HONEY NUT CRUNCH,BARBARA'S PUFFINS,VAN'S BLISSFULLY BERRY,ANNIE'S HOMEGROWN BERRY BUNNIS,BARBARA'S PUFFINS CRUNCHY CORN,VAN'S GRN" "Chain A" 4 1.59 4 "QKR OVRNGT OTS CCN,QKR OVERNIGHT OATS BN BB,QKR OVRNGT OTS RSW,QKR OVRNGT OTS PCH" "Chain A" 4 3.59 5 "GMI APPLE CINN CHEERIOS,GMI VERY BRY CHEERIOS,GMI CHOCOLATE CHEERIOS,GMI FROSTED CHEERIOS,GMI HONEY NUT CHEERIOS MDLY CR" "Chain A" 4 3.14 5 "NATURE'S PATH FLAX PLUS PKN RC,NATURE'S PATH FLAX PLUS RB CRN,NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH CRUNCHY VNLL SNR,KEL RICE KRISPIES ORIG" "Chain A" 4 .99 5 "GMI LUCKY CHARMS REG,KEL FROSTED FLAKES ORIG,GMI HONEY NUT CHEERIOS REG,KEL FROOT LOOPS REG,GMI CHEERIOS" "Chain A" 4 3.29 6 "GMI 'TOTAL' WG ORIG,POS H-C GRAPE NUTS ORIG,POST GREAT GRAINS CRUNCHY PCN,QKR CRUNCHY CORN BRAN,POS HBOATS STRAWBERRY,POS GREAT GRAINS CRBY ALM CRN" "Chain A" 4 3.79 7 "GMI NV BK OT BTS CN,KASHI DRK COCOA KARMA,KASHI ORGANIC PROMISE BF PS,KASHI CINNAMON HARVEST,GMI WHEATIES,KASHI AUTUMN WHEAT PROJECT SPK,GMI RAISIN NUT BRAN" "Chain A" 4 2.69 7 "GMI TINY TST STB,GMI HONEY NUT CHEERIOS REG,GMI TINY TST BB,GMI APPLE CN TOAST CRUNCH,GMI FRENCH TOAST CRUNCH,GMI MULTIGRAIN CHEERIOS REG,GMI CN TOAST CRUNCH REG" "Chain A" 4 4.19 7 "VAN'S CINNAMON HEAVEN,VAN'S HONEY NUT CRUNCH,BARBARA'S PUFFINS,BARBARA'S PUFFINS CRUNCHY CORN,ANNIE'S HOMEGROWN BERRY BUNNIS,VAN'S BLISSFULLY BERRY,VAN'S GRN" "Chain A" 4 4.99 5 "MAMMA CHIA GRN,BAKERY ON MAIN GRN,NATURE'S PATH GRN,PURELY ELIZABETH GRN,MOM FRST MINI SPOONERS BAG" "Chain A" 5 1.59 4 "QKR OVRNGT OTS CCN,QKR OVRNGT OTS RSW,QKR OVRNGT OTS PCH,QKR OVERNIGHT OATS BN BB" "Chain A" 5 3.29 5 "POST GREAT GRAINS CRUNCHY PCN,POS HBOATS STRAWBERRY,POS H-C GRAPE NUTS ORIG,POS GREAT GRAINS CRBY ALM CRN,QKR CRUNCHY CORN BRAN" "Chain A" 5 3.59 5 "GMI FROSTED CHEERIOS,GMI HONEY NUT CHEERIOS MDLY CR,GMI CHOCOLATE CHEERIOS,GMI VERY BRY CHEERIOS,GMI APPLE CINN CHEERIOS" "Chain A" 5 3.14 5 "NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH FLAX PLUS RB CRN,NATURE'S PATH CRUNCHY VNLL SNR,NATURE'S PATH FLAX PLUS PKN RC,KEL RICE KRISPIES ORIG" "Chain A" 5 .99 5 "KEL FROSTED FLAKES ORIG,GMI HONEY NUT CHEERIOS REG,GMI CHEERIOS,GMI LUCKY CHARMS REG,KEL FROOT LOOPS REG" "Chain A" 5 3.79 7 "KASHI ORGANIC PROMISE BF PS,KASHI DRK COCOA KARMA,GMI RAISIN NUT BRAN,KASHI AUTUMN WHEAT PROJECT SPK,GMI WHEATIES,KASHI CINNAMON HARVEST,GMI NV BK OT BTS CN" "Chain A" 5 2.69 7 "GMI HONEY NUT CHEERIOS REG,GMI MULTIGRAIN CHEERIOS REG,GMI APPLE CN TOAST CRUNCH,GMI FRENCH TOAST CRUNCH,GMI CN TOAST CRUNCH REG,GMI TINY TST STB,GMI TINY TST BB" "Chain A" 5 4.19 7 "VAN'S CINNAMON HEAVEN,VAN'S GRN,BARBARA'S PUFFINS CRUNCHY CORN,VAN'S BLISSFULLY BERRY,VAN'S HONEY NUT CRUNCH,ANNIE'S HOMEGROWN BERRY BUNNIS,BARBARA'S PUFFINS" "Chain A" 5 4.99 5 "BAKERY ON MAIN GRN,MOM FRST MINI SPOONERS BAG,PURELY ELIZABETH GRN,MAMMA CHIA GRN,NATURE'S PATH GRN" "Chain A" 12 3.79 3 "GMI RAISIN NUT BRAN,GMI NV BK OT BTS CN,GMI WHEATIES" "Chain A" 12 4.19 3 "BARBARA'S PUFFINS CRUNCHY CORN,ANNIE'S HOMEGROWN BERRY BUNNIS,BARBARA'S PUFFINS" "Chain A" 12 3.69 3 "GMI CHEERIOS PROTEIN CNA,MOM TOOTIE FRUITIES BAG,MOM BERRY COLOSSAL CRN BAG" "Chain A" 12 1.59 4 "QKR OVRNGT OTS RSW,QKR OVERNIGHT OATS BN BB,QKR OVRNGT OTS PCH,QKR OVRNGT OTS CCN" "Chain A" 12 3.14 5 "NATURE'S PATH FLAX PLUS PKN RC,NATURE'S PATH FLAX PLUS RB CRN,NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH CRUNCHY VNLL SNR,KEL RICE KRISPIES ORIG" "Chain A" 12 3.29 5 "POST GREAT GRAINS CRUNCHY PCN,POS GREAT GRAINS CRBY ALM CRN,QKR CRUNCHY CORN BRAN,POS H-C GRAPE NUTS ORIG,POS HBOATS STRAWBERRY" "Chain A" 12 3.59 5 "GMI VERY BRY CHEERIOS,GMI HONEY NUT CHEERIOS MDLY CR,GMI APPLE CINN CHEERIOS,GMI CHOCOLATE CHEERIOS,GMI FROSTED CHEERIOS" "Chain A" 12 .99 5 "GMI HONEY NUT CHEERIOS REG,KEL FROOT LOOPS REG,GMI CHEERIOS,KEL FROSTED FLAKES ORIG,GMI LUCKY CHARMS REG" "Chain A" 12 2.69 7 "GMI APPLE CN TOAST CRUNCH,GMI HONEY NUT CHEERIOS REG,GMI FRENCH TOAST CRUNCH,GMI TINY TST STB,GMI MULTIGRAIN CHEERIOS REG,GMI TINY TST BB,GMI CN TOAST CRUNCH REG" "Chain A" 12 4.99 5 "BAKERY ON MAIN GRN,MOM FRST MINI SPOONERS BAG,MAMMA CHIA GRN,BAKERY ON MAIN GRMT NTRLS GRN,PURELY ELIZABETH GRN" "Chain A" 13 1.59 4 "QKR OVRNGT OTS PCH,QKR OVRNGT OTS CCN,QKR OVRNGT OTS RSW,QKR OVERNIGHT OATS BN BB" "Chain A" 13 3.19 4 "KEL FUN PAK,NATURE'S PATH GRN,NATURE'S PATH FLAX PLUS GRN,NATURE'S PATH CHIA PLUS" "Chain A" 13 3.14 5 "NATURE'S PATH FLAX PLUS PKN RC,NATURE'S PATH FLAX PLUS RB CRN,NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH CRUNCHY VNLL SNR,KEL RICE KRISPIES ORIG" "Chain A" 13 .99 5 "GMI LUCKY CHARMS REG,KEL FROSTED FLAKES ORIG,KEL FROOT LOOPS REG,GMI CHEERIOS,GMI HONEY NUT CHEERIOS REG" "Chain A" 13 3.69 6 "GMI BASIC 4,GMI RAISIN NUT BRAN,MOM BERRY COLOSSAL CRN BAG,MOM TOOTIE FRUITIES BAG,KEL SMART START,GMI CHEERIOS PROTEIN CNA" "Chain A" 13 2.89 7 "CASCADIAN FARM OATS&HNY GRN,CASCADIAN FARM GRAHAM CRUNCH,CASCADIAN FARM CNMN RSN GRN,CASCADIAN FARM BERRY VANILA PS,CASCADIAN FARM HONEY OAT CRNCH,CASCADIAN FARM FRUIT & NUT GRN,CASCADIAN FARM CINNAMON CRUNCH" "Chain A" 13 3.39 4 "KEL FRST MWHT BB MFN,MOM COCOA DYNO-BITES BAG,NATURE'S PATH LOVE CRUNCH GRN,MOM FRUITY DYNO-BITES BAG" "Chain A" 13 4.19 7 "VAN'S BLISSFULLY BERRY,ANNIE'S HOMEGROWN BERRY BUNNIS,BARBARA'S PUFFINS,VAN'S CINNAMON HEAVEN,BARBARA'S PUFFINS CRUNCHY CORN,VAN'S HONEY NUT CRUNCH,VAN'S GRN" "Chain A" 13 4.99 5 "MAMMA CHIA GRN,MOM FRST MINI SPOONERS BAG,PURELY ELIZABETH GRN,BAKERY ON MAIN GRN,BAKERY ON MAIN GRMT NTRLS GRN" "Chain A" 19 3.69 4 "GMI CHEERIOS PROTEIN CNA,MOM BERRY COLOSSAL CRN BAG,KEL SMART START,MOM TOOTIE FRUITIES BAG" "Chain A" 19 4.19 4 "VAN'S GRN,VAN'S BLISSFULLY BERRY,ANNIE'S HOMEGROWN BERRY BUNNIS,VAN'S HONEY NUT CRUNCH" "Chain A" 19 1.59 4 "QKR OVRNGT OTS CCN,QKR OVRNGT OTS RSW,QKR OVRNGT OTS PCH,QKR OVERNIGHT OATS BN BB" "Chain A" 19 3.79 4 "GMI WHEATIES,BARBARA'S PUFFINS CRUNCHY CORN,GMI NV BK OT BTS CN,GMI RAISIN NUT BRAN" "Chain A" 19 3.59 5 "GMI APPLE CINN CHEERIOS,GMI VERY BRY CHEERIOS,GMI HONEY NUT CHEERIOS MDLY CR,GMI CHOCOLATE CHEERIOS,GMI FROSTED CHEERIOS" "Chain A" 19 3.14 5 "NATURE'S PATH CRUNCHY VNLL SNR,NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH FLAX PLUS RB CRN,NATURE'S PATH FLAX PLUS PKN RC,KEL RICE KRISPIES ORIG" "Chain A" 19 .99 5 "KEL FROOT LOOPS REG,KEL FROSTED FLAKES ORIG,GMI CHEERIOS,GMI HONEY NUT CHEERIOS REG,GMI LUCKY CHARMS REG" "Chain A" 19 4.99 4 "MAMMA CHIA GRN,MOM FRST MINI SPOONERS BAG,BAKERY ON MAIN GRN,PURELY ELIZABETH GRN" "Chain A" 19 2.69 7 "GMI CN TOAST CRUNCH REG,GMI FRENCH TOAST CRUNCH,GMI TINY TST STB,GMI HONEY NUT CHEERIOS REG,GMI APPLE CN TOAST CRUNCH,GMI MULTIGRAIN CHEERIOS REG,GMI TINY TST BB" "Chain A" 24 1.59 4 "QKR OVRNGT OTS CCN,QKR OVRNGT OTS RSW,QKR OVERNIGHT OATS BN BB,QKR OVRNGT OTS PCH" "Chain A" 24 3.14 5 "NATURE'S PATH FLAX PLUS PKN RC,NATURE'S PATH HERITAGE MLTGN,KEL RICE KRISPIES ORIG,NATURE'S PATH FLAX PLUS RB CRN,NATURE'S PATH CRUNCHY VNLL SNR" "Chain A" 24 3.29 5 "POS HBOATS STRAWBERRY,POS H-C GRAPE NUTS ORIG,POS GREAT GRAINS CRBY ALM CRN,QKR CRUNCHY CORN BRAN,POST GREAT GRAINS CRUNCHY PCN" "Chain A" 24 3.59 6 "GMI VERY BRY CHEERIOS,GMI FROSTED CHEERIOS,GMI RAISIN NUT BRAN,GMI HONEY NUT CHEERIOS MDLY CR,GMI CHOCOLATE CHEERIOS,GMI APPLE CINN CHEERIOS" "Chain A" 24 .99 5 "GMI LUCKY CHARMS REG,KEL FROOT LOOPS REG,GMI CHEERIOS,GMI HONEY NUT CHEERIOS REG,KEL FROSTED FLAKES ORIG" "Chain A" 24 3.69 6 "KASHI DRK COCOA KARMA,MOM TOOTIE FRUITIES BAG,MOM BERRY COLOSSAL CRN BAG,KASHI CINNAMON HARVEST,KASHI AUTUMN WHEAT PROJECT SPK,KASHI ORGANIC PROMISE BF PS" "Chain A" 24 2.69 7 "GMI CN TOAST CRUNCH REG,GMI APPLE CN TOAST CRUNCH,GMI HONEY NUT CHEERIOS REG,GMI FRENCH TOAST CRUNCH,GMI TINY TST BB,GMI MULTIGRAIN CHEERIOS REG,GMI TINY TST STB" "Chain A" 24 4.99 4 "MOM FRST MINI SPOONERS BAG,MAMMA CHIA GRN,PURELY ELIZABETH GRN,BAKERY ON MAIN GRN" "Chain A" 24 4.19 7 "VAN'S GRN,VAN'S HONEY NUT CRUNCH,VAN'S BLISSFULLY BERRY,ANNIE'S HOMEGROWN BERRY BUNNIS,VAN'S CINNAMON HEAVEN,BARBARA'S PUFFINS CRUNCHY CORN,BARBARA'S PUFFINS" "Chain A" 26 3.69 3 "MOM TOOTIE FRUITIES BAG,GMI CHEERIOS PROTEIN CNA,MOM BERRY COLOSSAL CRN BAG" "Chain A" 26 3.79 3 "GMI RAISIN NUT BRAN,GMI WHEATIES,GMI NV BK OT BTS CN" "Chain A" 26 4.19 3 "ANNIE'S HOMEGROWN BERRY BUNNIS,BARBARA'S PUFFINS,BARBARA'S PUFFINS CRUNCHY CORN" "Chain A" 26 1.59 4 "QKR OVRNGT OTS CCN,QKR OVERNIGHT OATS BN BB,QKR OVRNGT OTS PCH,QKR OVRNGT OTS RSW" "Chain A" 26 3.14 5 "NATURE'S PATH FLAX PLUS PKN RC,NATURE'S PATH FLAX PLUS RB CRN,KEL RICE KRISPIES ORIG,NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH CRUNCHY VNLL SNR" "Chain A" 26 .99 4 "GMI CHEERIOS,GMI LUCKY CHARMS REG,GMI HONEY NUT CHEERIOS REG,KEL FROOT LOOPS REG" "Chain A" 26 3.29 5 "POS GREAT GRAINS CRBY ALM CRN,QKR CRUNCHY CORN BRAN,POS HBOATS STRAWBERRY,POST GREAT GRAINS CRUNCHY PCN,POS H-C GRAPE NUTS ORIG" "Chain A" 26 3.59 5 "GMI VERY BRY CHEERIOS,GMI FROSTED CHEERIOS,GMI HONEY NUT CHEERIOS MDLY CR,GMI APPLE CINN CHEERIOS,GMI CHOCOLATE CHEERIOS" "Chain A" 26 2.69 7 "GMI TINY TST BB,GMI MULTIGRAIN CHEERIOS REG,GMI HONEY NUT CHEERIOS REG,GMI TINY TST STB,GMI APPLE CN TOAST CRUNCH,GMI FRENCH TOAST CRUNCH,GMI CN TOAST CRUNCH REG" "Chain A" 26 4.99 6 "PURELY ELIZABETH GRN,MOM FRST MINI SPOONERS BAG,SEVEN SUNDAYS,BAKERY ON MAIN GRN,MAMMA CHIA GRN,BAKERY ON MAIN GRMT NTRLS GRN" "Chain A" 43 3.79 3 "GMI NV BK OT BTS CN,GMI RAISIN NUT BRAN,GMI WHEATIES" "Chain A" 43 1.59 4 "QKR OVRNGT OTS RSW,QKR OVRNGT OTS CCN,QKR OVRNGT OTS PCH,QKR OVERNIGHT OATS BN BB" "Chain A" 43 3.69 4 "KEL SMART START,GMI CHEERIOS PROTEIN CNA,MOM BERRY COLOSSAL CRN BAG,MOM TOOTIE FRUITIES BAG" "Chain A" 43 3.14 5 "NATURE'S PATH FLAX PLUS PKN RC,NATURE'S PATH CRUNCHY VNLL SNR,NATURE'S PATH HERITAGE MLTGN,NATURE'S PATH FLAX PLUS RB CRN,KEL RICE KRISPIES ORIG" "Chain A" 43 3.59 5 "GMI FROSTED CHEERIOS,GMI VERY BRY CHEERIOS,GMI HONEY NUT CHEERIOS MDLY CR,GMI APPLE CINN CHEERIOS,GMI CHOCOLATE CHEERIOS" "Chain A" 43 .99 5 "GMI LUCKY CHARMS REG,GMI HONEY NUT CHEERIOS REG,GMI CHEERIOS,KEL FROSTED FLAKES ORIG,KEL FROOT LOOPS REG" "Chain A" 43 2.69 7 "GMI MULTIGRAIN CHEERIOS REG,GMI TINY TST STB,GMI TINY TST BB,GMI CN TOAST CRUNCH REG,GMI FRENCH TOAST CRUNCH,GMI APPLE CN TOAST CRUNCH,GMI HONEY NUT CHEERIOS REG" "Chain A" 43 4.99 4 "MOM FRST MINI SPOONERS BAG,PURELY ELIZABETH GRN,MAMMA CHIA GRN,BAKERY ON MAIN GRN" "Chain A" 43 4.19 7 "BARBARA'S PUFFINS CRUNCHY CORN,BARBARA'S PUFFINS,ANNIE'S HOMEGROWN BERRY BUNNIS,VAN'S CINNAMON HEAVEN,VAN'S HONEY NUT CRUNCH,VAN'S GRN,VAN'S BLISSFULLY BERRY" "Chain A" 44 3.67 3 "KASHI CINNAMON HARVEST,KASHI ORGANIC PROMISE BF PS,KASHI AUTUMN WHEAT PROJECT SPK" "Chain A" 44 .99 4 "KEL FROSTED FLAKES ORIG,GMI LUCKY CHARMS REG,GMI HONEY NUT CHEERIOS REG,KEL FROOT LOOPS REG" end
0 Response to tabulate distinct subsets of n items (where number of elements in each subset is {2, 3, 4,..n})
Post a Comment