30 pandas Commands for Manipulating DataFrames

The pandas library makes python-based data science an easy ride. It's a popular Python library for reading, merging, sorting, cleaning data, and more. Although pandas is easy to use and apply on datasets, it has many data manipulatory functions to learn.

You might use pandas, but there's a good chance you're under-utilizing it to solve data-related problems. Here's our list of valuable data manipulating pandas functions every data scientist should know.

Install pandas Into Your Virtual Environment

Before we proceed, make sure you install pandas into your virtual environment using pip:

        pip install pandas

After installing it, import pandas at the top of your script, and let's proceed.

1. pandas.DataFrame

You use pandas.DataFrame() to create a DataFrame in pandas. There are two ways to use this function.

You can form a DataFrame column-wise by passing a dictionary into the pandas.DataFrame() function. Here, each key is a column, while the values are the rows:

        import pandas
DataFrame = pandas.DataFrame({"A" : [1, 3, 4], "B": [5, 9, 12]})
print(DataFrame)

The other method is to form the DataFrame across rows. But here, you'll separate the values (row items) from the columns. The number of data in each list (row data) must also tally with the number of columns.

        import pandas
DataFrame = pandas.DataFrame([[1, 4, 5], [7, 19, 13]], columns= ["J", "K", "L"])
print(DataFrame)

2. Read From and Write to Excel or CSV in pandas

You can read or write to Excel or CSV files with pandas.

Reading Excel or CSV files

To read an Excel file:

        <strong>#Replace example.xlsx with the your Excel file path</strong> 
DataFrame = DataFrame.read_excel("example.xlsx")

Here's how to read a CSV file:

        <strong>#Replace example.csv with the your CSV file path</strong> 
DataFrame = DataFrame.read_csv("example.csv")

Writing to Excel or CSV

Writing to Excel or CSV is a well-known pandas operation. And it's handy for saving newly computed tables into separate datasheets.

To write to an Excel sheet:

        DataFrame.to_excel("full_path_of_the_destination_folder/filename.xlsx")

If you want to write to CSV:

        DataFrame.to_csv("full_path_of_the_destination_folder/filename.csv")

3. Get the Mean, Median, and Mode

You can also compute the central tendencies of each column in a DataFrame using pandas.

Here's how to get the mean value of each column:

        DataFrame.mean()

For the median or mode value, replace mean() with median() or mode().

4. DataFrame.transform

pandas' DataFrame.transform() modifies the values of a DataFrame. It accepts a function as an argument.

For instance, the code below multiplies each value in a DataFrame by three using Python's lambda function:

        DataFrame = DataFrame.transform(lambda y: y*3)
print(DataFrame)

5. DataFrame.isnull

This function returns a Boolean value and flags all rows containing null values as True:

        DataFrame.isnull()

The result of the above code can be hard to read for larger datasets. So you can use the isnull().sum() function instead. This returns a summary of all missing values for each column:

        DataFrame.isnull().sum()

6. Dataframe.info

The info() function is an essential pandas operation. It returns the summary of non-missing values for each column instead:

        DataFrame.info()

7. DataFrame.describe

The describe() function gives you the summary statistic of a DataFrame:

        DataFrame.describe()

8. DataFrame.replace

Using the DataFrame.replace() method in pandas, you can replace selected rows with other values.

For example, to swap invalid rows with Nan:

        <strong># Ensure that you pip install numpy for this to work</strong> 
import numpy

import pandas
<strong># Adding an inplace keyword and setting it to True makes the changes permanent:</strong> 

DataFrame.replace([invalid_1, invalid_2], numpy.nan, inplace=True)
print(DataFrame)

9. DataFrame.fillna

This function lets you fill empty rows with a particular value. You can fill all Nan rows in a dataset with the mean value, for instance:

        DataFrame.fillna(df.mean(), inplace = True)
print(DataFrame)

You can also be column-specific:

        DataFrame['column_name'].fillna(df[column_name].mean(), inplace = True)
print(DataFrame)

10. DataFrame.dropna

The dropna() method removes all rows containing null values:

        DataFrame.dropna(inplace = True)
print(DataFrame)

11. DataFrame.insert

You can use pandas' insert() function to add a new column to a DataFrame. It accepts three keywords, the column name, a list of its data, and its location, which is a column index.

Here's how that works:

        DataFrame.insert(column = 'C', value = [3, 4, 6, 7], loc=0)
print(DataFrame)

The above code inserts the new column at the zero column index (it becomes the first column).

12. DataFrame.loc

You can use loc to find the elements in a particular index. To view all items in the third row, for instance:

        DataFrame.loc[2]

13. DataFrame.pop

This function lets you remove a specified column from a pandas DataFrame.

It accepts an item keyword, returns the popped column, and separates it from the rest of the DataFrame:

        DataFrame.pop(item= 'column_name')
print(DataFrame)

14. DataFrame.max, min

Getting the maximum and minimum values using pandas is easy:

        DataFrame.min()

The above code returns the minimum value for each column. To get the maximum, replace min with max.

15. DataFrame.join

The join() function of pandas lets you merge DataFrames with different column names. You can use the left, right, inner, or outer join. To left-join a DataFrame with two others:

        #Left-join longer columns with shorter ones
newDataFrame = df1.join([df_shorter2, df_shorter3], how='left') 
print(newDataFrame)

To join DataFrames with similar column names, you can differentiate them by including a suffix to the left or right. Do this by including the lsuffix or rsuffix keyword:

        newDataFrame = df1.join([df2, rsuffix='_', how='outer') 
print(newDataFrame)

16. DataFrame.combine

The combine() function comes in handy for merging two DataFrames containing similar column names based on set criteria. It accepts a function keyword.

For instance, to merge two DataFrames with similar column names based on the maximum values only:

        newDataFrame = df.combine(df2, numpy.minimum)
print(newDataFrame)

Note: You can also define a custom selection function and insert numpy.minimum.

17. DataFrame.astype

The astype() function changes the data type of a particular column or DataFrame.

To change all values in a DataFrame to string, for instance:

        DataFrame.astype(str)

18. DataFrame.sum

The sum() function in pandas returns the sum of the values in each column:

        DataFrame.sum()

You can also find the cumulative sum of all items using cumsum():

        DataFrame.cumsum()

19. DataFrame.drop

pandas' drop() function deletes specific rows or columns in a DataFrame. You have to supply the column names or row index and an axis to use it.

To remove specific columns, for example:

        df.drop(columns=['colum1', 'column2'], axis=0)

To drop rows on indexes 1, 3, and 4, for instance:

        df.drop([1, 3, 4], axis=0)

20. DataFrame.corr

Want to find the correlation between integer or float columns? pandas can help you achieve that using the corr() function:

        DataFrame.corr()

The above code returns a new DataFrame containing the correlation sequence between all integer or float columns.

21. DataFrame.add

The add() function lets you add a specific number to each value in DataFrame. It works by iterating through a DataFrame and operating on each item.

Related:How to Use For Loops in Python

To add 20 to each of the values in a specific column containing integers or floats, for instance:

        DataFrame['interger_column'].add(20)

22. DataFrame.sub

Like the addition function, you can also subtract a number from each value in a DataFrame or specific column:

        DataFrame['interger_column'].sub(10)

23. DataFrame.mul

This is a multiplication version of the addition function of pandas:

        DataFrame['interger_column'].mul(20)

24. DataFrame.div

Similarly, you can divide each data point in a column or DataFrame by a specific number:

        DataFrame['interger_column'].div(20)

25. DataFrame.std

Using the std() function, pandas also lets you compute the standard deviation for each column in a DataFrame. It works by iterating through each column in a dataset and calculating the standard deviation for each:

        DataFrame.std()

26. DataFrame.sort_values

You can also sort values ascendingly or descendingly based on a particular column. To sort a DataFrame in descending order, for example:

        newDataFrame = DataFrame.sort_values(by = "colmun_name", descending = True)

27. DataFrame.melt

The melt() function in pandas flips the columns in a DataFrame to individual rows. It's like exposing the anatomy of a DataFrame. So it lets you view the value assigned to each column explicitly.

        newDataFrame = DataFrame.melt()

28. DataFrame.count

This function returns the total number of items in each column:

        DataFrame.count()

29. DataFrame.query

pandas' query() lets you call items using their index number. To get the items in the third row, for example:

        DataFrame.query('4') <strong># Call the query on the fourth index</strong>

30. DataFrame.where

The where() function is a pandas query that accepts a condition for getting specific values in a column. For instance, to get all ages less than 30 from an Age column:

        DataFrame.where(DataFrame['Age'] < 30)

The above code outputs a DataFrame containing all ages less than 30 but assigns Nan to rows that don't meet the condition.

Handle Data Like a Pro With pandas

pandas is a treasure trove of functions and methods for handling small to large-scale datasets with Python. The library also comes in handy for cleaning, validating, and preparing data for analysis or machine learning.

Taking the time to master it definitely makes your life easier as a data scientist, and it's well worth the effort. So feel free to pick up all the functions you can handle.