手把手教你使用Pandas从Excel文件中提取满足条件的数据并生成新的文件（附源码）

import pandas as pd

excel_filename = '数据.xlsx'
df = pd.read_excel(excel_filename)
# print(df)

# 方法一：分别取日期与小时，按照日期和小时删除重复项
df['day'] = df['SampleTime'].dt.day   # 提取日期列
df['hour'] = df['SampleTime'].dt.hour     # 提取小时列
df = df.drop_duplicates(subset=['day', 'hour'])  # 删除重复项

# 把筛选结果保存为excel文件
df.to_excel('数据筛选结果2.xlsx')

import pandas as pd

excel_filename = '数据.xlsx'
df = pd.read_excel(excel_filename)

# 方法二：把日期中的分秒替换为0
SampleTime_new = df['SampleTime'].map(lambda x: x.replace(minute=0, second=0))
data = df[SampleTime_new.duplicated() == False]
print(df)
# 把筛选结果保存为excel文件
df.to_excel('数据筛选结果2.xlsx')

import pandas as pd

excel_filename = '数据.xlsx'
df = pd.read_excel(excel_filename)

# 方法三：对日期时间按照小时进行分辨
SampleTime_new = df['SampleTime'].dt.floor(freq='H')
df = df[SampleTime_new.duplicated() == False]
print(df)
# 把筛选结果保存为excel文件
df.to_excel('数据筛选结果2.xlsx')

import pandas as pd

excel_filename = '数据.xlsx'
df = pd.read_excel(excel_filename)

# 方法四：对日期时间按照小时进行分辨
SampleTime_new = df['SampleTime'].dt.to_period(freq='H')
df = df[SampleTime_new.duplicated() == False]
print(df)
# 把筛选结果保存为excel文件
df.to_excel('数据筛选结果2.xlsx')

import pandas as pd

excel_filename = '数据.xlsx'
df = pd.read_excel(excel_filename)

# 方法五：对日期时间进行重新格式，并按照新的日期时间删除重复项（会引入新列）
df['new'] = df['SampleTime'].dt.strftime('%Y-%m-%d %H')
df = df.drop_duplicates(subset=['new'])
print(df)
# 把筛选结果保存为excel文件
df.to_excel('数据筛选结果2.xlsx')

from openpyxl import load_workbook, Workbook
from datetime import datetime

# 打开数据工作簿
workbook = load_workbook('数据.xlsx')
# 打开工作表
sheet = workbook.active
time_column = sheet['C']
row_lst = []
date_lst = []
hour_lst = []
for cell in time_column:
    if cell.value != "SampleTime" and cell.value != None:
        # print(cell.value.date())
        if cell.value.date() not in date_lst:
            date_lst.append(cell.value.date())
        # row_lst.append(cell.row)
print(date_lst)

# if all(cell.value != "SampleTime", cell.value != None, cell.value.date() == date, cell.value.hour not in hour_lst):

for date in date_lst:
    # print(date)
    for cell in time_column:
        # if all((cell.value != "SampleTime", cell.value != None, cell.value.date() == date, cell.value.hour not in hour_lst)):
        #     row_lst.append(cell.row)
        if cell.value != "SampleTime" and cell.value != None:
            if cell.value.date() == date:
                if cell.value.hour not in hour_lst:
                    hour_lst.append(cell.value.hour)
                    row_lst.append(cell.row)
    hour_lst = []
print(hour_lst)
# 将满足要求的数据写入到新表
new_workbook = Workbook()
new_sheet = new_workbook.active

# 创建和原数据 一样的表头（第一行）
header = sheet[1]
header_lst = []
for cell in header:
    header_lst.append(cell.value)
new_sheet.append(header_lst)

# 从旧表中根据行号提取符合条件的行，并遍历单元格获取值，以列表形式写入新表
for row in row_lst:
    data_lst = []
    for cell in sheet[row]:
        data_lst.append(cell.value)
    new_sheet.append(data_lst)

# 最后切记保存
new_workbook.save('新表.xlsx')
print("满足条件的新表保存完成！")