Data Cleaning - Sort Values
Tags: pokemon, mage, mageacademy, machinelearning
Published: 2022-03-09 21:44:39

## TLDR
Learn how to sort out our mess– I mean– row data in one or more columns by ascending and descending order.

## Glossary
* Best time to sort?

* Dataset

* Sort by…

* “Abra” - kadabra! ✨🔮

## Best time to sort?
By definition, sorting seems obvious– given text or numerical data, if you display text data in alphabetical order, you’re sorting by ascending values. Conversely,  if you display the biggest to smallest numbers, you’re sorting by descending values.

Rather than asking what sorting is, the worthier question is whether your data pre-processing is optimized by sorting specific columns. Thus, what kinds of insights are you searching for in your data? In which situations, with what type of data, does sorting columns benefit your analysis? 


_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0t5cs9yk6fg4bq7lv0ml.jpg)Fanart of Psyduck_</center>

Below are scenarios described by our co-founders where it was immensely useful to leverage sorting:

* Viewing a user’s first and last page visit: when given user traffic data on a website (like our Mage blog) we can **sort by timestamp** per user. The first page tells us which page brought them to the site. The last page would say what made them drop off, and hopefully it’s not one of my articles 😛 

* Discern what kind of content users like to consume in the morning: As a newsfeed app, if we **sort by timestamp** per user, we can gather a list of articles they ❤️ to store in a column. With this data, we can train a model that decides what to show the user in the morning and keep them engaged with the app. 

As shown in these examples, sort is rarely used on its own, and it’s most powerful when used with other operations, like to **group by** user. 

## Dataset
For this lesson, we’ll be using ThiagoAZen’s [Pokemon dataset](https://app.box.com/s/b9cfpcoctzt3qed4ex4jjtpkbcv5q8c3) to visualize the functionality of sort. 

However, since only three features are relevant to us, we will drop all but the columns Name, Type1, and Attack. For additional data cleaning, we’ll also [remove the duplicates](https://www.mage.ai/blog/ma-remove-duplicates). 
```
1 data = df[["Name", "Type1", "Attack"]]
2 pokedex= data.drop_duplicates(subset=["Name"], 
3 keep="first")
4 pokedex
```

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

That’s a lot of Pokemon to work with! Thankfully, we have some Pokemon who also know [sorting algorithms](https://www.programiz.com/dsa/sorting-algorithm). Maybe he can come with us and help! 


_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o4zh1e73a9199e93qalh.jpg)Squirtle doing a bubble sort_</center> 

## Sort by
As the name suggests, sorting by ascending values means that values in the column at the beginning are smaller than the values at the end. For alphabetical text entries, this means “A” is the first (ASCII value: 65) and “Z” is the last value (ASCII: 90). For ASCII value look-up, you can refer to [this](http://www.kerryr.net/pioneers/ascii2.htm).

Thus, if we wanted the Pokedex in alphabetical order, we sort the “Name” column using:
```
1 pokedex.sort_values(by=["Name"])
```

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

Now that it’s sorted, in the “Name” column, “Abra” has a smaller value than “Absol,” so “Abra” is placed before. 

_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/359s351ntp4ab75809kk.png)Pokemon images from Bulbapedia_</center>

For string comparisons between two strings, what determines which word is positioned first is by the ASCII values of the first non-matching character. When comparing “Abra” with “Absol,” the ASCII values of the first two characters are the same, so we are not yet able to determine which Pokemon appears first in the list.

Thus the only comparison that matters is between the third character of each word– “R” and “S.” Since the ASCII value of “R,” 114, is smaller than S’ 115, “Abra” gets to be positioned before “Absol.” 

Sorting by descending values means that the largest values appear at the beginning of the set. 

If we sort by descending “Attack”, this means the strongest Pokemon are at the top of the list, so we should be sure to catch ‘em 😉
```
1 pokedex.sort_values(by=["Attack"], ascending = False)
```

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

Since it’s descending, the Pokemon with the higher attack is placed first. This is the opposite behavior from the above section. Therefore, since the Pokemon Zacian has an attack of 170, which is larger than Rampardos’ 165, Zacian is sorted into a higher position. 

_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kmpdgio9gd6xe80x2ftv.png)Images from Bulbapedia_</center>

Arguably, sort is even more useful when we sort by multiple columns. If we first sort by descending value on “Type1,” all the Pokemon are sorted into groups where all the Water-type Pokemon are kept together:


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

Thus, when we sort again by “Attack,” the strongest Pokemon appear at the top of a group of types. 
```
1 pokedex.sort_values(by=["Type1", 'Attack'], ascending = False)
```

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

This is useful as we can now find the largest and smallest values, not only across the entire dataset, but within groups as well. Now, you have the knowledge to look out for and capture the strongest Pokemon of each type! 

## “Abra” - kadabra!  ✨🔮
Data cleaning is the most tedious yet important aspect of data analysis. Let Mage handle all the fuss of sorting so you can re-allocate that energy to derive value from your data (or rest because you’ve been working hard).

_<center>![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hidrtev1947l9exzur5j.gif)Source: Blue Bandana Jake_</center>

There’s no code involved, but we need you to specify which column needs sorting, and which order. Go to Edit Data > Sort and enter the column and the order you’ll be sorting by. 

Since I wanted to match the example above, I sorted by the two columns, “type1” and “attack,” in descending order.


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

Abrakadabra! Your data is all sorted with just a couple clicks and no code! 

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