Pandas Cheat Sheet: top 35 commands and operations
Tags: python, pandas, datascience, cheatsheet
Published: 2020-06-30 19:15:45

Pandas is one of the most popular tools for data analysis in Python. This open-source library is the backbone of many data projects and is used for data cleaning and data manipulation. With Pandas, you gain greater control over complex data sets. It's an essential tool in the data analysis tool belt. If you're not using Pandas, you're not making the most of your data.

In my other article, [Pandas for Python Tutorial](https://www.educative.io/blog/python-pandas-tutorial), I went through all the basics of Pandas to get you started. In this post, I've compiled the 35 most essential operations and commands that any Pandas user needs to know. This article is intended for Pandas learners looking for quick search answers. No more complex documentation! **Let's get right to the answers.**

<br>

> <h4> <b>Master Python for Data Analysis. </b> </h4>
> Learn the tools of the trade: Pandas, NumPy, Matplotlib, and Seaborn 
>
> <b>[Predictive Data Analysis with Python](https://www.educative.io/courses/predictive-data-analysis-with-python)

* <small>[Pandas import convention](#import)
* <small>[Create and name a Series](#name)
* <small>[Create a DataFrame](#df)
* <small>[Specify values in DataFrame columns](#specify)
* <small>[Read and Write to CSV file](#csv)
* <small>[Read and write to Excel file](#excel)
* <small>[Read and write to SQL Query](#sql)
* <small>[Get the first element of a Series](#first)
* <small>[Get the first 5 elements of a Series](#first-5)
* <small>[Get the last 5 elements in a Series](#last-5)
* <small>[Select a single value position](#single-pos)
* <small>[Select a single value by label](#single-label)
* <small>[Access a DataFrame with a boolean index](#boolean)
* <small>[Drop values from rows](#drop-row)
* <small>[Drop values from columns](#drop-column)
* <small>[Create a new column in a DataFrame](#new-column)
* <small>[Rename columns in a DataFrame](#rename)
* <small>[Sort Series by labels along an axis](#sort-label)
* <small>[Sort values along an axis (ascending order)](#sort-axis)
* <small>[Adding ranks to particular entries](#rank)
* <small>[Retrieve rows and columns description](#retrieve)
* <small>[Describe columns of DataFrame](#describe)
* <small>[Retrieve index description](#index-description)
* <small>[Get information on DataFrame](#get-info)
* <small>[Retried number of non-`NA` values](#na)
* <small>[Get sum of values](#sum)
* <small>[Subtract/Add 2 from all values](#add)
* <small>[Multiply/Divide all values by 2](#divide)
* <small>[Find min/max values of a DataFrame](#min-df)
* <small>[Get min/max index values](#min-index)
* <small>[Get median or mean of values](#mean)
* <small>[Describe a summary of data statistics](#stats)
* <small>[Apply a function to a dataset](#function) 
* <small>[Merge two DataFrames](#merge)
* <small>[Combine DataFrames across columns or rows: concatenation](#concat)
* <small>[Wrap up and resources](#wrap-u)
</small>

<br>

<a name="import"></a>

### Pandas import convention 

```python
import pandas as pd
```

Pandas is now accessible with the acronym `pd`. You can also install Pandas using the built-in Python tool pip and run the following command.

```python
$ pip install pandas
```

<br>

___ 
<br>

<a name="name"></a>
 
### Create and name a Series
Create one-dimensional array to hold any data type. Invoke the `pd.Series()` method and then pass a list of values. Pandas will default count index from 0. 

```python
series1 = pd.Series([1,2,3,4]), index=['a', 'b', 'c', 'd'])
```

Set the Series name 

```python
srs.name = "Insert name"
```

Set index name. 

```python
srs.index.name = "Index name"
```

<br>

___

<br>

<a name="df"></a>

### Create a DataFrame

Create a two-dimensional data structure with columns. Create and print a `df`.  

```python
df = pd.DataFrame(
         {"a" : [1 ,2, 3],
          "b" : [7, 8, 9],
          "c" : [10, 11, 12]},        index = [1, 2, 3])
```

<br>

___

<br>

<a name="specify"></a>

### Specify values in DataFrame columns 

Specify how you want to organize your DataFrame by columns. 

```python
df = pd.DataFrame( 
     [[1, 2, 3], 
     [4, 6, 8],
     [10, 11, 12]],
     index=[1, 2, 3], 
     columns=['a', 'b', 'c'])
```
<br>

___


<br>

<a name="csv"></a>

### Read and Write to `CSV` file

Open the CSV file, copy the data, paste it in our Notepad, and save it in the same directory that houses your Python scripts. Use `read_csv` function build into Pandas and index it the way we want.

```python
import pandas as pd
data = pd.read_csv('file.csv')

data = pd.read_csv("data.csv", index_col=0)
```

<br>

___

<br>

<a name="excel"></a>

### Read and write to Excel file

Call the `read_excel` function to access an Excel file. Pass the name of the Excel file as an argument.

```python
pd.read_excel('file.xlsx')

df.to_excel('dir/myDataFrame.xlsx',  sheet_name='Sheet2')
```

<br>

___

<br>

<a name="sql"></a>

### Read and write to SQL Query

```python
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
pd.read_sql("SELECT * FROM my_table;", engine)
pd.read_sql_table('my_table', engine)
pd.read_sql_query("SELECT * FROM my_table;", engine)
```

`(read_sql()` is a convenience wrapper around `read_sql_table()` and `read_sql_query())`

```python
df.to_sql('myDf', engine)
```

<br>

___


<br>

<a name="first"></a>

### Get the first element of a Series

Since Pandas indexes at 0, call the first element with `ser[0]`.

```python
import pandas as pd  
    
df = pd.read_csv  
  
df['Name'].head(10) 
  
# get the first element 
ser[0]
```

<br>

___

<br>

<a name="first-5"></a>

### Get the first 5 elements of a Series

Use `ser[:n]` to get the first $n$ elements of a Series. 

```python
import pandas as pd  
    
df = pd.read_csv  
  
df['Name'].head(10) 
  
ser[:5]
```

<br>

___

<br>

<a name="last-5"></a>

### Get the last 5 elements in a Series

Use `ser[-n:]` to get the last $n$ elements of a Series. 

```python
import pandas as pd  
    
df = pd.read_csv 
  
df['Name'].head(10) 
  
ser[-5:] 
```

<br>

___

<br> 

<a name="single-pos"></a>

### Select a single value position

```python
df.iloc[[0],[0]] 'Name'
df.iat([0],[0]) 'Name'
```

<br>

___

<br>

<a name="single-label"></a>

### Select a single value by label

```python
df.loc[[0], ['Label']] 'Name'
df.at([0], ['Label']) 'Name'
```

<br>

___

<br>

<a name="boolean"></a>

### Access a DataFrame with a boolean index

In boolean indexing, we filter data with a boolean vector.

```python
import pandas as pd 
   
# dictionary of lists 
dict = {'name':["name1", "name2", "name3", "name4"], 
        'degree': ["degree1", "degree2", "degree3", "degree4"], 
        'score':[1, 2, 3, 4]} 
   
df = pd.DataFrame(dict, index = [True, False, True, False]) 
   
print(df) 
```

<br>

___

<br>

<a name="drop-row"></a>

### Drop values from rows

```python
s.drop(['a', 'c'])
```

<br>

___

<br>

<a name="drop-column"></a>

### Drop values from columns 

```python
df.drop('Value', axis=1)
```

<br>

___

<br>

<a name="new-column"></a>

### Create a new column in a DataFrame

```python
df['New Column'] = 0
```


<br>

> ### Keep the learning going. 
> Learn Pandas and Data Analysis without scrubbing through videos or documentation. Educative's text-based courses are easy to skim and feature live coding environments, making learning quick and efficient. 
> 
> <b>[Predictive Data Analysis with Python
](https://www.educative.io/courses/predictive-data-analysis-with-python) </b>


<br>

<a name="rename"></a>

### Rename columns in a DataFrame

```python
df.columns = ['Column 1', 'Column 2', 'Column 3']
```

<br>

___

<br>

<a name="sort-label"></a>

### Sort Series by labels along an axis

Sort Series by index labels and returns a new Series sorted by the label if inplace argument is `False`, otherwise it updates the original series and returns `None`.

```python
Series.sort_index(self, axis=0, level=None, ascending=True, inplace=False, kind='quicksort', na_position='last', sort_remaining=True)
```

<br>

___

<a name="sort-axis"></a>

### Sort values along an axis (ascending order)

```python
df.sort_values(by='Values')

# descending order
df.sort_values(ascending = False)
```

<br>

___

<br>

<a name="rank"></a>

### Adding ranks to particular entries 

Specify how you want to rank a column and add ranks. 

```python
df.rank()
```

<br>

___

<br>

<a name="retrieve"></a>


### Retrieve rows and columns description 

```python
df.shape
```

<br>

___

<br>

<a name="describe"></a>

### Describe columns of DataFrame

```python
df.columns
```

<br>

___

<br>

<a name="index-description"></a>

### Retrieve index description 

```python
df.index
```

<br>

___

<br>

<a name="get-info"></a>

### Get information on DataFrame

```python
df.info()
```

<br>

___

<br>

<a name="na"></a>

### Retrieve number of non-`NA` values

```python
df.count()
```

<br>

___

<br>


<a name="sum"></a>

### Get sum of values 

```python
df.sum()

# cumulative sum

df.cumsum()
```

<br>

___

<br>

<a name="add"></a>

### Subtract/Add 2 from all values

```python
s.sub(2)

s.add(2)
```

<br>

___

<a name="divide"></a>

### Multiply/Divide all values by 2

```python
s.mul(2)

s.div(2)
```


<br>

___

<br>

<a name="min-df"></a>

### Find min/max values of a DataFrame

```python
df.min()

df.max()
```

<br>

___

<br>

<a name="min-index"></a>

### Get min/max index values 

```python
df.idxmin()

df.idxmax()
```

<br>

____

<br>

<a name="mean"></a>

### Get median or mean of values 

```python
df.mean()

df.median()
```

<br>

___

<br>

<a name="stats"></a>

### Describe a summary of data statistics 

```python
df.describe()
```

<br>

___

<br>

<a name="function"></a>

### Apply a function to a dataset

```python
f = # write function here
df.apply(f)

# apply a function by an element 

f = # write function here
df.applymap(f)
```


<br>

___

<br>

<a name="merge"></a>

### Merge two DataFrames

```python
pd.merge(df1, df2, on='subject_id')
```

<br>

___

<br>

<a name="concat"></a>

### Combine DataFrames across columns or rows: concatenation

```python
print(pd.concat([df1, df2]))
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/5mh92zwdtbliy4cgwghu.png)

<a name="wrap-up"></a>

## Wrapping up and resources 

Now that you're armed with the common operations and commands in Python, you can put them into practice. After all, working with real datasets is the best way to master Python and become a data analyst! There's still a lot more to learn that we didn't cover today such as, 
* Statistics
* NumPy
* Matplotlib
* Advanced data wrangling 
* Visualizations for data 
* Data scraping
* Seaborn
* Scikit
* and more 

To get started with these essential tools of the trade, check out Educative's course [**Predictive Data Analysis for Python**](https://www.educative.io/courses/predictive-data-analysis-with-python). You'll get hands-on practice with industry-standard examples and become fluent in the data analysis. 

<br>

### Keep reading 
* [Data Analysis Made Simple: Python Pandas Tutorial
](https://www.educative.io/blog/python-pandas-tutorial)
* [Master Algorithms with Python for Coding Interviews
](https://www.educative.io/blog/python-algorithms-coding-interview)
* [Stop Using Excel for Data Analytics: Upgrade to Python
](https://www.educative.io/blog/excel-python-data-analytics)