0

I am trying to built a database based on a table with 5 categorical columns (Business, Product Type, Vendor, Region and Country) and as a measure sales.

The idea is to aggregate the sales amount by all permutation of the 5 categorical columns (Total of 120 different aggregations).

Table example:

Cat A Cat B Cat C Sales
Orange Spain Mercadona 100
Orange UK Tesco 100
Strawberry Ireland Mercadona 100
Orange Germany Mercadona 100
Orange Spain Fnac 100
Strawberry Germany Saturn 100
Orange Turkey CorteIngles 100
Blueberry France Fnac 100

Feel free to use the table as bases I can then extrapolate to the database.

Overall my objective with the permutation is to then "quickly" identify where the biggest YoY variances come from and hopefully "automate" some comments/insights on the data based on a correct sequence of A,B,C or D to deep dive.

I have no language preference feel free to use R or Python. I do expect the size of the database greatly increase.

thanks for your time! kind regards, Kylian

Kylian
  • 59
  • 7
  • are you looking to aggregate the sales per unique combination of values across the categorical columns? I found this as a starting point: https://stackoverflow.com/questions/8862105/count-unique-combinations-of-values – uke Apr 05 '23 at 09:50
  • Yes. I need the aggregation of sales for each permutation. Example on record would be Orange (no other category) and 500. The link provided i've check and works for eliminating duplicates the data i have each row is a unique record. – Kylian Apr 05 '23 at 10:07
  • 1
    I've found the same question already solved : https://stackoverflow.com/questions/64213656/how-to-get-various-combinations-of-categories-in-a-categorical-variable-and-at-t – Kylian Apr 05 '23 at 10:22
  • Does this answer your question? [How to get various combinations of categories in a categorical variable and at the same time aggregate it?](https://stackoverflow.com/questions/64213656/how-to-get-various-combinations-of-categories-in-a-categorical-variable-and-at-t) – Joseph Wood Apr 05 '23 at 18:04

0 Answers0