Pandas Common Functions

The following lists some commonly used Pandas functions and usage examples:

Read Data

FunctionDescription
pd.read_csv(filename)Read CSV files;
pd.read_excel(filename)Read Excel files;
pd.read_sql(query, connection_object)Read data from SQL database;
pd.read_json(json_string)Read data from JSON string;
pd.read_html(url)Read data from HTML pages.

Example

import pandas as pd

# Read data from CSV file
df = pd.read_csv('data.csv')

# Read data from Excel file
df = pd.read_excel('data.xlsx')

# Read data from SQL database
import sqlite3
conn = sqlite3.connect('database.db')
df = pd.read_sql('SELECT * FROM table_name', conn)

# Read data from JSON string
json_string = '{"name": "John", "age": 30, "city": "New York"}'
df = pd.read_json(json_string)

# Read data from HTML page
url = 'https://www.example.com'
dfs = pd.read_html(url)
df = dfs[0] # Select the first dataframe

View Data

FunctionDescription
df.head(n)Display the first n rows of data;
df.tail(n)Display the last n rows of data;
df.info()Display data information, including column names, data types, missing values, etc.;
df.describe()Display basic statistical information of data, including mean, variance, maximum, minimum, etc.;
df.shapeDisplay the number of rows and columns of the data.

Example

# Display the first five rows of data
df.head()

# Display the last five rows of data
df.tail()

# Display data information
df.info()

# Display basic statistical information
df.describe()

# Display the number of rows and columns of the data
df.shape

Example

import pandas as pd

data = [
    {"name": "Google", "likes": 25, "url": "https://www.google.com"},
    {"name": "Example", "likes": 30, "url": "https://www.example.com"},
    {"name": "Taobao", "likes": 35, "url": "https://www.taobao.com"}
]

df = pd.DataFrame(data)
# Display the first two rows of data
print(df.head(2))
# Display the last row of data
print(df.tail(1))

The output of the above examples is:

     name  likes                     url
0  Google     25  https://www.google.com
1  Example     30  https://www.example.com
     name  likes                     url
2  Taobao     35  https://www.taobao.com

Data Cleaning

FunctionDescription
df.dropna()Delete rows or columns containing missing values;
df.fillna(value)Replace missing values with specified values;
df.replace(old_value, new_value)Replace specified values with new values;
df.duplicated()Check whether there are duplicate data;
df.drop_duplicates()Remove duplicate data.

Example

# Delete rows or columns containing missing values
df.dropna()

# Replace missing values with specified values
df.fillna(0)

# Replace specified values with new values
df.replace('old_value', 'new_value')

# Check whether there are duplicate data
df.duplicated()

# Remove duplicate data
df.drop_duplicates()

Data Selection and Slicing

FunctionDescription
df[column_name]Select specified columns;
df.loc[row_index, column_name]Select data by label;
df.iloc[row_index, column_index]Select data by position;
df.ix[row_index, column_name]Select data by label or position;
df.filter(items=[column_name1, column_name2])Select specified columns;
df.filter(regex='regex')Select columns whose column names match a regular expression;
df.sample(n)Randomly select n rows of data.

Example

# Select specified columns
df['column_name']

# Select data by label
df.loc[row_index, column_name]

# Select data by position
df.iloc[row_index, column_index]

# Select data by label or position
df.ix[row_index, column_name]

# Select specified columns
df.filter(items=['column_name1', 'column_name2'])

# Select columns whose column names match a regular expression
df.filter(regex='regex')

# Randomly select n rows of data
df.sample(n=5)

Data Sorting

FunctionDescription
df.sort_values(column_name)Sort by the values of a specified column;
df.sort_values([column_name1, column_name2], ascending=[True, False])Sort by the values of multiple columns;
df.sort_index()Sort by index.

Example

# Sort by the values of a specified column
df.sort_values('column_name')

# Sort by the values of multiple columns
df.sort_values(['column_name1', 'column_name2'], ascending=[True, False])

# Sort by index
df.sort_index()

Data Grouping and Aggregation

FunctionDescription
df.groupby(column_name)Group by a specified column;
df.aggregate(function_name)Perform aggregation operations on the grouped data;
df.pivot_table(values, index, columns, aggfunc)Generate a pivot table.

Example

# Group by a specified column
df.groupby('column_name')

# Perform aggregation operations on the grouped data
df.aggregate('function_name')

# Generate a pivot table
df.pivot_table(values='value', index='index_column', columns='column_name', aggfunc='function_name')

Data Merging

FunctionDescription
pd.concat([df1, df2])Merge multiple dataframes by rows or columns;
pd.merge(df1, df2, on=column_name)Merge two dataframes by specified columns.

Example

# Merge multiple dataframes by rows or columns
df = pd.concat([df1, df2])

# Merge two dataframes by specified columns
df = pd.merge(df1, df2, on='column_name')

Data Selection and Filtering

FunctionDescription
df.loc[row_indexer, column_indexer]Select rows and columns by label.
df.iloc[row_indexer, column_indexer]Select rows and columns by position.
df[df['column_name'] > value]Select rows in a column that satisfy a condition.
df.query('column_name > value')Use a string expression to select rows in a column that satisfy a condition.

Data Statistics and Description

FunctionDescription
df.describe()Calculate basic statistical information, such as mean, standard deviation, minimum, maximum, etc.
df.mean()Calculate the mean of each column.
df.median()Calculate the median of each column.
df.mode()Calculate the mode of each column.
df.count()Calculate the number of non-missing values in each column.

Example

Suppose we have the following JSON data, saved todata.jsonfile:

data.json file

[
  {
    "name": "Alice",
    "age": 25,
    "gender": "female",
    "score": 80
  },
  {
    "name": "Bob",
    "age": null,
    "gender": "male",
    "score": 90
  },
  {
    "name": "Charlie",
    "age": 30,
    "gender": "male",
    "score": null
  },
  {
    "name": "David",
    "age": 35,
    "gender": "male",
    "score": 70
  }
]

We can use Pandas to read JSON data, and perform operations such as data cleaning and processing, data selection and filtering, data statistics and description, as follows:

Example

import pandas as pd

# Read JSON data
df = pd.read_json('data.json')

# Remove missing values
df = df.dropna()

# Fill missing values with specified values
df = df.fillna({'age': 0, 'score': 0})

# Rename column names
df = df.rename(columns={'name': 'Name', 'age': 'age', 'gender': 'Gender', 'score': 'Score'})

# Sort by score
df = df.sort_values(by='Score', ascending=False)

# Group by gender and calculate average age and score
grouped = df.groupby('Gender').agg({'age': 'mean', 'Score': 'mean'})

# Select rows where score is greater than or equal to 90, and keep only the name and score columns
df = df.loc[df['Score'] >= 90, ['Name', 'Score']]

# Calculate basic statistical information for each column
stats = df.describe()

# Calculate the mean of each column
mean = df.mean()

# Calculate the median of each column
median = df.median()

# Calculate the mode of each column
mode = df.mode()

# Calculate the number of non-missing values in each column
count = df.count()

The output result is as follows:

# df
   姓名  年龄 性别  成绩
1  Bob   0  male  90

# grouped
             年龄  成绩
性别                
female  25.000000  80
male    27.500000  80

# stats
         成绩
count   1.0
mean   90.0
std     NaN
min    90.0
25%    90.0
50%    90.0
75%    90.0
max    90.0

# mean
成绩    90.0
dtype: float64

# median
成绩    90.0
dtype: float64

# mode
    姓名    成绩
0  Bob  90.0

# count
姓名    1
成绩    1
dtype: int64
Other Extensions