Pandas常用功能笔记

pandas包导入

import pandas as pd

单个sheet处理

# 读取excel数据
file = './aa.xlsx'
data_1 = pd.read_excel(file)
data_1.to_excel(file_save, index=False, header=False)

# 写入excel数据
file_save = './bb.xlsx'
sample_1 = [(1, 2), (3, 4), (5, 6)]
data_2 = pd.Data_Frame(sample_1)
data_2.to_excel(file_save, index=False, header=['odd', 'even'])

sample_2 = {'odd': [1, 3, 5], 'even': [2, 4, 6]}
data_3 = pd.DataFrame(sample_2)
data_3.to_excel(file_save, index=False, header=True)

多个sheet处理

# 读取sheet数据
file = './aa.xlsx'
data_1 = pd.read_excel(file, sheet='data_1')

# 写入sheet数据
f_save = pd.ExcelWriter('./bb.xlsx')
sample_1 = [(1, 2), (3, 4), (5, 6)]
sheet_1 = pd.Data_Frame(sample_1)
sheet_1.to_excel(file_save, index=False, header=['odd', 'even'], sheet_name='tuple_data')

sample_2 = {'odd': [1, 3, 5], 'even': [2, 4, 6]}
sheet_2 = pd.DataFrame(sample_2)
sheet_2.to_excel(file_save, index=False, header=True, sheet_name='dict_data')

数据选择

file = r'./aa.xlsx'
df = pd.read_excel(file)
odd_data = df['odd']
odd_list = df['odd'].values.tolist()

# 按名称选择
odd, even = df.loc([:, ['odd', 'even']])
odd_part, even_part = df.loc([0:2, ['odd', 'even']])  # 选取前两行

# 按列顺序选择
odd, even = df.iloc([:, [0, 1]])
odd_part, even_part = df.iloc([0:2, [0, 1]])

数据去重

file = r'./aa.xlsx'
df = pd.read_excel(file)
df.drop_duplicates(subset=['odd','even'],keep='first',inplace=True)  # 在原数据上操作

# 不在原数据上操作，重新生成副本
new_data = drop_duplicates(subset=['odd','even'],keep='first',inplace=False)

遍历excel中的所有sheet

file = r'./aa.xlsx'
df = pd.ExcelFile(file)

for name in df.sheet_names:
    sheet = pd.read_excel(file, sheet_name=name)
……