python利用pandas读取Excel文件和筛选处理小案例

一、案例1

1.问题描述：想要筛选Excel中的数据，筛选条件是 实际收费 > 消费金额，并且保存筛选的数据为Excel格式！

                     数据格式如底部所示！

2.实现：利用pandas处理数据。

3.代码如下：
 

import numpy as np
import pandas as pd
# a = np.arange(24).reshape(4, 6)
# a[2][4] = 100
# a[1][4] = 100
# b = a[:, 4]
# c = a[:, 5]
# # a = a[a[:, 4] > a[:, 5]]
# a = a[b > c]
# print(a)
excel_ori = pd.read_excel(io = 'data.xlsx')
a = excel_ori.values
a = a[a[:, 7] > a[:, 8]]
data_df = pd.DataFrame(a)

data_df.columns = ['单据号','商品编码','商品售价','销售数量','消费金额','消费产生的时间','收银机号','实际收费','消费金额']
# data_df.index = ['a','b','c','d','e','f','g','h']

writer = pd.ExcelWriter('ret.xlsx')
data_df.to_excel(writer, 'page_1', index=False)
writer.save()

4.源数据和处理后的数据图片：

源数据

处理后的数据

二、案例2

1.问题描述：筛选Excel中的数据，筛选条件是 后本门上饰板 == NOK，并且筛选获取列号为VIN的数据。

                     数据格式如底部所示！

2.实现：利用pandas处理数据。

3.代码如下：

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time        :2024/9/10 10:47
# @Author      :weiz
# @ProjectName :weizTools
# @File        :excel_process.py
# @Description :excel数据处理
import numpy as np
import pandas as pd
import os


def excel_process(file_path, column_name, dest_column_name):
    file_list = os.listdir(file_path)
    for excel_name in file_list:
        print(excel_name)
        excel_data = pd.read_excel(io=os.path.join(file_path, excel_name), sheet_name=0)
        values = excel_data.values

        ind_dest_column_name = -1
        ind_column_name = -1
        for ind, column_name_tmp in enumerate(excel_data.head(1)):
            if column_name_tmp == column_name:
                ind_column_name = ind

            if column_name_tmp == dest_column_name:
                ind_dest_column_name = ind

        # print(ind_column_name, column_name)
        dest_excel_data = values[values[:, ind_column_name] == "NOK"]
        # print(dest_excel_data)

        # print(ind_dest_column_name, dest_column_name)
        print(dest_excel_data[:, ind_dest_column_name])



def main():
    excel_process(g_excel_file_path, g_column_name, g_dest_column_name)


g_column_name = "后背门上饰板"  # Excel表格第一行的某列名字
g_dest_column_name = "VIN"
g_excel_file_path = r"C:\Users\Administrator\Desktop\shangshiban"
if __name__ == "__main__":
    main()

4.源数据和处理后的数据图片：

'LNNABDBF6SD033836' 'LVVDB21B2RDB27550' 'LVVDB21B4RDB28148'
 'LVVDB21B7RDB28516' 'LVVDB21B4RDB29722' 'LNNABDBF0RDB28163'
 'LNNABDBFXRDB28560' 'LVVDB21B5SD034069'