提升效率的十种 Pandas 数据操作方法

import pandas as pd

# 假设我们有一个大型CSV文件
filename = 'large_dataset.csv'

# 只读取特定列
df = pd.read_csv(filename, usecols=['Column1', 'Column2'])

# 指定数据类型以节省内存
dtypes = {'Column1': str, 'Column2': float}
df = pd.read_csv(filename, dtype=dtypes)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

# 假设 df 是一个包含用户信息的数据框
# 我们想要筛选出年龄大于30岁且性别为女性的用户

filtered_df = df.query('age > 30 and gender == "F"')
1.
2.
3.
4.

# 计算所有数值列的平方值
df['square'] = df.select_dtypes(include='number').apply(lambda x: x ** 2, axis=0)

# 或者更简洁地
df['square'] = df[['col1', 'col2']].pow(2)
1.
2.
3.
4.
5.

# 合并两个数据框
df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'value': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['B', 'D', 'E'], 'value': [4, 5, 6]})

# 使用 merge 按 key 列连接
merged = pd.merge(df1, df2, on='key')

# 使用 concat 沿轴堆叠
stacked = pd.concat([df1, df2], ignore_index=True)
1.
2.
3.
4.
5.
6.
7.
8.
9.

# 假设我们有一个销售数据集
sales_data = pd.DataFrame({
    'Product': ['A', 'A', 'B', 'B', 'C', 'C'],
    'Region': ['North', 'South', 'North', 'South', 'North', 'South'],
    'Quantity': [100, 150, 200, 250, 300, 350]
})

# 按产品分组并计算总销量
total_sales = sales_data.groupby('Product')['Quantity'].sum()

# 按地区和产品分组并计算平均销量
average_sales = sales_data.groupby(['Region', 'Product'])['Quantity'].mean()
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

# 使用 pivot_table 生成透视表
pivot = pd.pivot_table(sales_data,
                       values='Quantity',
                       index=['Region'],
                       columns=['Product'],
                       aggfunc=np.sum,
                       fill_value=0)

print(pivot)
1.
2.
3.
4.
5.
6.
7.
8.
9.

Product  A  B  C
Region               
North   100 200 300
South   150 250 350
1.
2.
3.
4.

# 使用 crosstab 生成交叉表
cross_tab = pd.crosstab(sales_data['Region'], sales_data['Product'])

print(cross_tab)
1.
2.
3.
4.

Product  A  B  C
Region               
North     1  1  1
South     1  1  1
1.
2.
3.
4.

# 假设我们有一个包含城市名称的数据框
cities = pd.DataFrame({
    'City': ['New York', 'Los Angeles', 'Chicago', 'Houston', 'Phoenix']
})

# 筛选出包含特定城市的行
selected_cities = cities[cities['City'].isin(['New York', 'Chicago'])]

print(selected_cities)
1.
2.
3.
4.
5.
6.
7.
8.
9.

       City
0  New York
2   Chicago
1.
2.
3.

# 自定义一个函数来处理数据
def process_row(row):
    if row['Quantity'] > 200:
        return 'High'
    elif row['Quantity'] > 100:
        return 'Medium'
    else:
        return 'Low'

# 应用自定义函数
sales_data['Sales_Level'] = sales_data.apply(process_row, axis=1)

print(sales_data)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

  Product Region  Quantity Sales_Level
0       A  North      100         Low
1       A  South      150      Medium
2       B  North      200      Medium
3       B  South      250        High
4       C  North      300        High
5       C  South      350        High
1.
2.
3.
4.
5.
6.
7.

# 假设我们有一个包含状态代码的数据框
status_codes = pd.DataFrame({
    'Code': ['OK', 'ERROR', 'WARNING', 'UNKNOWN']
})

# 定义一个映射字典
status_map = {
    'OK': 0,
    'ERROR': 1,
    'WARNING': 2,
    'UNKNOWN': -1
}

# 使用 map 替换值
status_codes['Numeric_Code'] = status_codes['Code'].map(status_map)

print(status_codes)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

     Code  Numeric_Code
0     OK              0
1  ERROR              1
2  WARNING            2
3 UNKNOWN           -1
1.
2.
3.
4.
5.

import pandas as pd
import numpy as np

# 创建示例数据
sales_data = pd.DataFrame({
    'Product': ['A', 'A', 'B', 'B', 'C', 'C'],
    'Region': ['North', 'South', 'North', 'South', 'North', 'South'],
    'Quantity': [100, 150, 200, 250, 300, 350]
})
1.
2.
3.
4.
5.
6.
7.
8.
9.

# 按地区计算总销量
total_sales_by_region = sales_data.groupby('Region')['Quantity'].sum()

print(total_sales_by_region)
1.
2.
3.
4.

Region
North    600
South    750
Name: Quantity, dtype: int64
1.
2.
3.
4.

# 按地区和产品计算平均销量
average_sales_by_region_product = sales_data.groupby(['Region', 'Product'])['Quantity'].mean()

print(average_sales_by_region_product)
1.
2.
3.
4.

Region  Product
North   A          100.0
        B          200.0
South   A          150.0
        C          350.0
Name: Quantity, dtype: float64
1.
2.
3.
4.
5.
6.

# 找出每个地区的最畅销产品
best_selling_products = sales_data.groupby(['Region', 'Product'])['Quantity'].sum().reset_index()
best_selling_products = best_selling_products.sort_values(by=['Region', 'Quantity'], ascending=[True, False])

print(best_selling_products)
1.
2.
3.
4.
5.

   Region Product  Quantity
0   North       C        300
1   North       B        200
2   North       A        100
3  South       C        350
4  South       B        250
5  South       A        150
1.
2.
3.
4.
5.
6.
7.