Feature Engineering - Expand category column
Tags: machinelearning, mage, mageacademy, pizza
Published: 2022-03-21 22:55:12

## TLDR
In this Mage Academy lesson, we’ll go over how to expand a category column and see how this can help us understand the data better.

## Glossary
* Definition
* Why is it necessary
* How to gain information
* Conceptual example 
* How to code

## Definition
Expanding the category column means splitting a categorical column into multiple numerical columns (i.e., the values of these columns are in the form of numbers), and the number of numerical columns is equal to the number of categories in the categorical column.

## Why is it necessary
Data is available in 2 forms (numbers or strings). Machine learning models can understand only numbers, so before building any machine learning model, we’ve to convert strings into numbers without losing any information. Expanding category columns is one such action that helps to represent categorical columns in the form of numbers without losing information. 

Now, let’s see in detail what the **expand category column** is and how to perform this action.

## How to gain information
As mentioned, expanding the category column splits the categorical column into multiple new numerical columns (i.e., the values in these columns are in the form of numbers). These new columns have the **frequency of each category** as column values, and these frequency values help us **prioritize** the data.

For example, there’s sample data with columns "ID" and "Dish". Using the expand category column action, we can prioritize the dishes based on the number of times they've been ordered.

And with this information, we can predict how many dishes we can sell next month or we can estimate the quantity of ingredients required for the next month.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fb2gazv9lgrdqaqdudtw.png)

_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ha13z0g0bm944k7z6ean.png)Frequency of each category_</center>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zssgz640vueyx4qevruu.png)

"Noodles" were ordered 4 times, so we can conclude that "Noodles" are the most popular dish. "Ice cream" was ordered twice, making it the second best-selling dish, and "Biryani" was ordered only once, making it the least favorite among the customers who visited the restaurant. As "Noodles" are our best-selling dish, we'll need to buy more ingredients to make this recipe more frequently. 

**Note**: After expanding the category column "Dish", we can remove this column as this column’s information is already in the new columns "Noodles," "Biryani," and "Ice cream."

## Conceptual example

Let’s understand the importance of expanding category columns in detail with the help of a more complicated example.


_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xi9sxzjknrfppvs46di1.jpeg)Image by digital designer from Pixabay_</center>

You own 4 pizza restaurants. Now, you'd like to know how frequently each variety of pizza is sold at each restaurant, as this helps you estimate the quantity of groceries you'll need for the next month at each restaurant. 

_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5lx0jw4n6qacc0217p8y.png)Image by Alexas_Fotos from Pixabay_</center> 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fcs8rxt4q7h5uqalx64f.jpeg)

Restaurant names

All 4 restaurants sell 2 varieties of pizza.

_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6ns4s0660hv321h9mbcs.jpeg)Menu_</center>

First, let’s take a look at the [order](https://app.box.com/s/478t4377lbuyuij2oz0q526p66bic73n) data of all restaurants.

_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/krposzvg8ounnc4lw6gu.jpeg)Image by Photo Mix from Pixabay_</center>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vnubg6g482cb8k5qyouf.png)


**Step-1**: Let’s group the orders by restaurant names.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hvn7zi62spnywip3omcq.png)

**Step-2**: Group the data by types of pizzas sold at each restaurant.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qgnfenhp3803op2n780b.png)

We see that there are 4 restaurants and each restaurant has 2 varieties or categories of pizzas sold. Now, for each variety or category, let’s count the number of pizzas sold at each restaurant.

**Step-3**: Display the count of pizzas sold at each restaurant. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ygfls44lwifce5djhsna.png)

We can also display the same result in a more readable format as shown below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5lbfvj9zne76dt39ziqn.png)

**Step-4**: Add the results back to the original data. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jkylteoav8wzgbqs6ezg.png)

**How to code**
Until now, we’ve seen how to manually calculate the count of pizzas sold per variety at each restaurant. Now, let’s see how we can automate the process through code.

Find the count of each variety of pizza sold at each restaurant.

**Step-1**:
First, let’s group orders by company name and then by types of pizzas sold. To do this, we’ll iterate through the rows of order data and save the “Company” as keys of the dictionary. The final result should be as shown below.

Output format: {‘CompanyA’: [List of pizzas sold], ‘CompanyB’:[List of pizzas sold],....}

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2tksyzocjf9cxcs9jrkz.jpeg)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f4y6jmedqbexyv0wyy7q.png)

**Step-2**:
**Calculate the number of pizzas sold in each category at each restaurant.**

The number of pizzas sold in each category at each restaurant is calculated by counting the number of times a pizza category appears in the list corresponding to each restaurant. The final result should be as shown below.

Output format: {‘CompanyA’: {Type1: count, Type2: count}, ‘CompanyB’: {Type1: count, Type2: count},....}

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g6nog5kb6x01yzn3toiq.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vzyaft6h46jpekda0yd4.jpeg)

First, we’ve to group the data to calculate the frequency of each category and then we’ve to join the result back into the original dataset.

Step-1: We can either use **crosstab()**, [groupby()](https://www.mage.ai/blog/ma-min-max-aggregate) or **pivot_table()** functions to get the count of the pizzas sold in each variety or category.

Using **crosstab()** 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8n9096ujaix6q8ev4jd0.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e97el49k67iygvwq68k6.jpeg)

Using **groupby()**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zef7nrhxef1fd76h0lor.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aaxhsqw9szygivwmzj08.png)

Using **pivot_table()** 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ta6oh3byvmfwbkef7n0y.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rb0427sadkrrfjfbr3qd.jpeg)


Step-2: Finally, create new columns in the original data frame for each category with values equal to the number of pizzas sold in each category with respect to each restaurant and add the result back to the original dataset using join() function.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ji5w47e1cl5fhdkb3841.jpeg)

 
_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ss42pvjqd2wmern8lq6j.png)Original data set after expanding the “Type” column_</center>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qam09nbipo4ywwn37mfz.png)

We've got 2 categories in the column "Type", so 2 new columns "Cheese Pizza" and "Speciality Pizza" are created after **expand the category column** action. The values in the new columns represent the frequency or count of pizzas sold at each restaurant. From this, we can say "Domino’s Pizza," "Godfather’s Pizza," and "IMO’s Pizza" are famous for specialty pizzas. IMO’s Pizza sold the least, i.e., 0 cheese pizzas, while "Pizza Hut" sold the most, i.e., cheese pizzas. Based on this, we can say that 3 of the restaurants require more ingredients to make specialty pizzas than cheese pizza ingredients.

**Note**: After expanding the "Type" column, we can remove this column from the dataset as its information is already in the new columns "Cheese Pizza" and "Speciality Pizza."

For quick analysis and results, try our product, Mage. Our service features an "Edit data" area with “Expand category column” action. 

 
_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bbcksrehdz3jkvt4swm7.png)Data set before expanding the category column action_</center>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4xdohmfeb8mu1y13jsuo.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nkg0atfr2xrhrdpukea2.png)


_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yhnbxhwrjokd7s7mlj8n.png)Data set after expanding “type_” column_</center>


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0xfpex057lp5o1syw8fp.gif)

Want to learn more about machine learning (ML)? Visit [Mage Academy](https://www.notion.so/mageai/Mage-Academy-64e4291fa786420885dc33a257f19bc5)! ✨🔮