python read_excel数据筛选替换 python对excel进行筛选并写入新表

使用Python中操作mysql的pymysql模块从数据库查询数据生成一个excel表，使用pandas从该表中筛选数据到新的两个表再各新建两个sheet。【筛选指定字符行，升降序、筛选分组、要表头，不要序号、不覆盖原有表，新建sheet】

执行SQL

import pymysql
# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='1234', db='数据库名', charset='utf8')
# 创建游标
cursor = conn.cursor()
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("select * from xxx")
# 获取剩余结果的第一行数据
#row_1 = cursor.fetchone()
#print(row_1)
# 获取剩余结果前n行数据
# row_2 = cursor.fetchmany(3)
# 获取剩余结果所有数据
row_3 = cursor.fetchall()
print(row_3)
# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()

r1筛选包含指定字符的列，r2筛选包含指定字符的行

r1=[x for x in df.columns if "指定字符" in x]
r2=df[df['列名'].str.contains('指定字符')]

下面是筛选分组名为an和na，且用户名含k和b两个字符的行，运行结果是预期的但是会警告 。

df = df[(df['分组'] == "na") | (df['分组'] == "an")][df['用户名'].str.contains('k') | df['用户名'].str.contains('b')]

 

升降序

ascending=false:降序

ascending=true:升序

df.sort_values(by=, ascending=)可以单列或多列排序by="A"，by=['A', 'B']

df1 = pd.DataFrame(df.sort_values(by=['当天数'], ascending=False))  # 降序
df1 = pd.DataFrame(df.sort_values(by=['当天数'], ascending=True))     # 升序

筛选

df[df['age']>30]    #选取所有age大于30的行
df[(df['age']>30) & (df['isMarried']=='no')]    #选取出所有age大于30，且isMarried为no的行
df[(df['age']==20) | (df['age']==32)]    #选取出所有age为20或32的行

df[[each>30 for each in df['age']]]    #选取所有age大于30的行
df[[True,True,True,False,False,False,False,False,False,False]]    #选取前三行

df['a':'b']    #选取前两行
df[:'a']    #选取第一行
df[0:1]    #选取第一行
df[0:2]    #选取前两行

df['name']    #选取name列所有数据
df[['name','age']]    #选取name和age两列数据
df[lambda df: df.columns[0]]    #选取第一列

只输出数据，不想要表头header或者索引序号index

df.to_excel("xxxxx.xlsx",index=False,header=None)    # header 指定列名，index 默认为True，写行名，不写行名就为None或False

excel已经存在，不覆盖，新建一个sheet

dret = pd.DataFrame.from_records(list(row_2))  # mysql查询的结果为元组，需要转换为列表
dret.to_excel("filename.xlsx", index=False, header=(
'用户名', '分组', '当天数'))    #表头
df = pd.read_excel("filename.xlsx")
df = df[(df['分组'] == "an") | (df['分组'] == "na")]
wb = openpyxl.load_workbook('one.xlsx')
writer = pd.ExcelWriter('an.xlsx', engine='openpyxl')
writer.book = wb    #没有这句的话excel表将完全被覆盖
df1 = pd.DataFrame(df.sort_values(by=['当天数'], ascending=False))    #按当天数升序
df1.to_excel(writer, sheet_name='sheet_ins', index=None)    #不加序号#不覆盖原来的工作表
writer.save()
writer.close()

全部如下：

#!/usr/bin/env pytho
# -*- coding:utf-8 -*-
import os

import openpyxl
import pymysql
import pandas as pd


# 创建连接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='1234', db='markets', charset='utf8')
# 创建游标
cursor = conn.cursor()


# 查询1

# 执行SQL，并返回收影响行数
effect_row1 = cursor.execute('''SELECT
    ......''')
list1 = []
for i in range(len(cursor.description)):
    list1.append(cursor.description[i][0])
print(list1)
row_1 = cursor.fetchall()
print(row_1)
# # 提交，不然无法保存新建或者修改的数据
# conn.commit()
# # 关闭游标
# cursor.close()
# # 关闭连接
# conn.close()


# 查询2

# 执行SQL，并返回收影响行数
effect_row2 = cursor.execute('''SELECT
    ......''')
list2 = []
for i in range(len(cursor.description)):
    list2.append(cursor.description[i][0])
print(list2)
row_2 = cursor.fetchall()
print(row_2)
# 提交，不然无法保存新建或者修改的数据
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()


#    pandas操作表

# 生成xlsx文件的函数 sheet1
dret = pd.DataFrame.from_records(list(row_1))  # mysql查询的结果为元组，需要转换为列表
dret.to_excel("filename.xlsx", index=False, header=(
'用户名', '分组', '当天数'))  # header 指定列名，index 默认为True，写行名
df = pd.read_excel("filename.xlsx")
df = df[(df['分组'] == "Chen") | (df['分组'] == "Li")]
writer1 = pd.ExcelWriter(os.path.join(os.getcwd(), 'li.xlsx'))
df.to_excel(writer1, sheet_name='sheet_tiktok', index=None)  # startcol=**， startrow=**)
# # dret.to_excel(writer, sheet_name='sheet_ins')  # startcol=**， startrow=**)
writer1.save()

# 生成xlsx文件的函数 sheet1
dret = pd.DataFrame.from_records(list(row_1))  # mysql查询的结果为元组，需要转换为列表
dret.to_excel("filename.xlsx", header=(
'用户名', '分组', '当天数'),
              index=None)  # header 指定列名，index 默认为True，写行名
df = pd.read_excel("filename.xlsx")
df = df[(df['分组'] == "an") | (df['分组'] == "na")]
writer2 = pd.ExcelWriter(os.path.join(os.getcwd(), 'an.xlsx'))
df.to_excel(writer2, sheet_name='sheet_tiktok', index=None)  # startcol=**， startrow=**)
writer2.save()

# sheet2
dret = pd.DataFrame.from_records(list(row_2))  # mysql查询的结果为元组，需要转换为列表
dret.to_excel("filename.xlsx", index=False, header=(
'用户名', '分组', '当天数'))  # header 指定列名，index 默认为True，写行名
df = pd.read_excel("filename.xlsx")
df = df[(df['分组'] == "Chen") | (df['分组'] == "Li")]
wb = openpyxl.load_workbook('li.xlsx')
writer = pd.ExcelWriter('li.xlsx', engine='openpyxl')
writer.book = wb
df1 = pd.DataFrame(df.sort_values(by=['当天数'], ascending=False))
df1.to_excel(writer, sheet_name='sheet_ins', index=None)
writer.save()
writer.close()

# sheet2
dret = pd.DataFrame.from_records(list(row_2))  # mysql查询的结果为元组，需要转换为列表
dret.to_excel("filename.xlsx", index=False, header=(
'用户名', '分组', '当天数'))  # header 指定列名，index 默认为True，写行名
df = pd.read_excel("filename.xlsx")
df = df[(df['分组'] == "an") | (df['分组'] == "na")][df['用户名'].str.contains('k') | df['用户名'].str.contains('b')]
wb = openpyxl.load_workbook('an.xlsx')
writer = pd.ExcelWriter('an.xlsx', engine='openpyxl')
writer.book = wb
df1 = pd.DataFrame(df.sort_values(by=['当天数'], ascending=False))  # 降序
df1.to_excel(writer, sheet_name='sheet_ins', index=None)
writer.save()
writer.close()

 修改excel文件名

import os, sys
# 列出目录
print ("目录为: %s"%os.listdir(os.getcwd()))
# 重命名
os.rename("li.xlsx","li0402.xlsx")
os.rename("an.xlsx","an0402.xlsx")
# 列出重命名后的目录
print("目录为: %s" %os.listdir(os.getcwd()))