After playing around with Pandas Python Data Analysis Library for about a month, I’ve compiled a pretty large list of useful snippets that I find myself reusing over and over again. These tips can save you some time sifting through the comprehensive Pandas docs.
For this article, we are starting with a DataFrame filled with Pizza orders. If you’re brand new to Pandas, here’s a few translations and key terms.
- DataFrame - Indexed rows and columns of data, like a spreadsheet or database table.
- Series = Single column of data.
- Axis - 0 == Rows, 1 == Columns
- Shape - (number_of_rows, number_of_columns) in a DataFrame
1. Importing a CSV File
There are a ton of options for the read_csv function that can simplify preprocessing of data. Nobody want to waste time cleaning data, so see if you can knock it out when import the initial file.
Need to parse dates? Just pass in the corresponding column name(s).
Only need a few specific columns?
2. Exploring Data in a DataFrame
The first thing you probably want to do is see what the data looks like. Here a few ways to check out Pandas data.
Here’s the head of the pizza DataFrame…
|2||PZZA0001||09/27/16||Extra Large||Bell Pepper||19.99||0.0||No|
3. Adding a New Column to a DataFrame
The quick and easy way is to just define a new column on the dataframe. This will give us column with the number 23 on every row. Usually, you will be setting the new column with an array or Series that matches the number of rows in the data.
Need to build a new column based on values from other columns?
Need the column in a certain order? The first argument is the position of the column. This will put the column at the begining of the DataFrame.
4. Select a Specific “Cell” Value
By cell I mean a single row/column intersection, like those in an Excel spreadsheet. You would expect this to be simple, but the syntax is not very obvious. There are three methods in Pandas that almost do the same thing, .loc, iloc, .ix – adding to the confusion for newcomers.
Typically, I use
.ix because it allows a mix of integers and strings. Enter the index of the row first, then the column.
You can also select the column first with dot notation, then the row index, which looks a little cleaner.
Either method will return the value of the cell.
>>> 'Bell Pepper'
5.Filtering DataFrames with Conditional Logic
Let’s the we need to analyze orders that have pineapple in the topping column.
Or that meet a certain price threshold
How about both at the same time? Just add the conditions to tuples and connect them with a bitwise operator.
Now we have all the pizzas with a Pineapple topping priced over 11.99.
6. Sorting a DataFrame by a Certain Column
Pretty self-explanatory, but very useful.
7. Apply a Function to Every Row in a Column
Anonymous lambda functions in Python are useful for these tasks. Let’s say we need to calculate taxes for every row in the DataFrame with a custom function. The pandas apply method allows us to pass a function that will run on every value in a column. In this example, we extract a new taxes feature by running a custom function on the price data.
8. Add a New Column with Conditional Logic
The where function in numpy is useful when extracting features with conditional logic. Let’s imagine the pizza parlor is only profitable on sales above $15.00. We create a new column based on this insight like so:
9. Finding the Mean or Standard Deviation of Multiple Columns or Rows
If you have a DataFrame with the same type of data in every column, possibly a time series with financial data, you may need to find he mean horizontally.
or to find the standard deviation vertically
10. Converting a DataFrame to a Numpy Array
Converting the the values in a DataFrame to an array is simple
If you want to preserve the table presentation
11. Combining DataFrames with Concatenation
You can concatenate rows or columns together, the only requirement is that the shape is the same on corresponding axis. To concat rows vertically:
Or to concat columns horizontally:
12. Combining DataFrames based on an Index Key
Merging in Pandas works just like SQL. If you you have two DataFrames that share a key, perhaps a pizza ‘order_id’, you can perform inner, outer, left, right joins just like you would in SQL.
13. Converting Dates to their own Day, Week, Month, Year Columns
First, make sure the data is in datetime format. Then use
dt method to extract the data you need.
14. Finding NaNs in a DataFrame
Count the total number of NaNs present:
List the NaN count for each column:
15. Filling NaNs or Missing Data
Most machine learning algorithms do not like NaN values, so you’ll probably need to convert them. If the topping column is missing some values, we can fill them a default value.
or we can drop any row missing data across the entire DataFrame:
16. Extracting Features by Grouping Columns
Grouping columns is a great way to extract features from data. This is especially useful when you have data that can be counted or quantified in some way. For example, you might have group pizzas by topping, then calculate the mean for price in each group.
or maybe you want to see the count of a certain value
topping Anchovies 3 Bell Pepper 1 Cheese 2 Olives 1 Pepperoni 3 Pineapple 2 Veggie 1 Name: discount, dtype: int64
Let’s say we want to create 3 separate bins for different price ranges. This is especially useful for simplifying noisy data.
18. Creating a new Column by Looping
Let’s say we want to categorize toppings by ‘vegetable’ or ‘meat’. Dealing with nominal values like these can be handled with a for loop. (Note: you can also use the apply function described earlier to perform this task. )
19. Loading Massive Datasets in Smaller Chunks
Sometimes you might have a massive file that will max out your RAM and crash your system. In that case, you might need to analyze the file in smaller chunks.