使用pandas处理excel数据

读取Excel

首先通过pandas提供了read_excel函数来支持读取excel表里的数据

pandas.read_excel(
io,  #string类型文件的路径或url.
sheet_name=0,  #指定的excel中的具体某个或某些表的表名或表索引.
header=0,  #以哪些行作为表头，也叫做列名.
names=None, #自己定义一个表头(列名).
index_col=None,  #将哪些列设为索引.
usecols=None,  #指定读取excel中哪些列的数据,默认为None，表示读取全部.
squeeze=False,  #默认为False,如果解析的数据只包含一列，则返回一个Series。
dtype=None,  #接收dict,设置数据类型，具体到每列.
engine=None,  #如果io不是缓冲区或路径，则必须将其设置为标识io。可接受的值是None、“xlrd”、“openpyxl”或“odf”.
converters=None,  #类型为字典(dict).默认为None.进行值转换。{列名:str}
true_values=None,  #默认:None,接收一个list，将在list中的值转换成True，只有在整列值都能转换成bool值时才能成功。
false_values=None,  #默认:None,接收一个list，将在list中的值转换成False，只有在整列值都能转换成bool值时才能成功。
skiprows=None,  #跳过excel中的某些行来读取数据.
nrows=None,  #指定要读取excel表中哪些行的数据.
na_values=None,  #设置指定值填充为NaN.
keep_default_na=True,
verbose=False, #布尔类型, 默认为 False.显示列表中除去数字列，NA值的数量.
parse_dates=False,  #指定解析成日期格式的列.
date_parser=None,  #funtion.指定解析日期格式的函数.
thousands=None, #将字符串列解析为数字的数千个分隔符。
comment=None, #将一个或多个字符传递给此参数以指示输入文件中的注释。
skipfooter=0,  #省略指定行数的数据，从尾部数的行开始。
convert_float=True, #布尔, 默认为 True.将积分浮点数转换为int（即1.0 - > 1）。
mangle_dupe_cols=True,  #布尔类型，默认为True.重复列将被指定为“X”、“X.1”、“X.N”.
**kwds)

将获取到的DataFrame进行数据处理

import matplotlib as mpl
import pandas as pd
import xlrd
import openpyxl
from io import BytesIO
import base64
import matplotlib.pyplot as plt
import numpy as np

data = pd.read_excel("E:\\pycharm\\get_commitNum_from_gitlab\\项目统计表格\\2021年1-3月代码提交汇总.xls",skipfooter=3)

print(data.index)                # 查看索引 RangeIndex(start=0, stop=26, step=1)
print(data.values)               # 查看数值
print(data.shape)                # 查看行数、列数  (26, 6)
print(data.head( 5 ))            # 查看前5行
print(data.tail( 3 ))            # 查看后3行
print(data['总计代码'].unique())   # 查看唯一值  nunique()返回的是唯一值的个数
print(data.columns)              # 查看data的列名
data.sort_index()                # 按索引排序
data.sort_values()               # 按数值排序sort_values(by,ascending=True, inplace=False,na_position=‘last’)
data.isnull()                    # 查找data中出现的空值
data.sort_index()                # 索引排序
data.sort_values()               # 值排序

pd.merge(data,data,on=[""],how='left')# 合并，left_index将左侧的行索引用作其连接键 right_index将右侧的行索引用作其连接键
# on是用于连接的列名，how是连接方式：‘inner’（默认）；还有，‘outer’、‘left’、‘right’
pd.concat([data,data])         # 合并
pd.pivot_table( data )           # 用df做data透视表（类似于Excel的数透）

print(data.reset_index(drop=True)) # 重置索引 drop为不想保留原来的index
data.reindex()                   # 重置索引，如下示例
data=data.reindex(columns=['商品名称', '规格', '对应车型类别', '备注', '新增的一列'], fill_value='新增的一列要填的值')

a=data['x']                      # 取列名为'x'的列，格式为series
b=data[['x']]                    # 取列名为'x'的列，格式为Dataframe
c=data[['w','z']]                # 取多列时需要用Dataframe的格式
data.loc['A']                    # 取行名为'A'的行
data.loc[:,['工程名称','总计代码']] # 表示选取所有的行以及columns为x,z的列
data['name'].values              # 取列名为'name'的列的值（取出来的是array而不是series）取单行后是一个Series，Series有index而无columns，可以用name来获取单列的索引
print(data.loc[(data['总计代码'] < -0),:])#查找总计代码为负的行
data.loc[data['总计代码'] == 0, "总计代码"] = "John" #赋值 总计代码等于0的值改为John
data.loc["行标签","列标签"]
data.loc['a':'b']                 #选取ab两行数据
data.loc[:,'one']                 #选取one列的数据

data.notnull()                   # 非空值
data.dropna()                    # 删除空值
data.dropna()                    # 删除有空值的行
data.dropna(axis=1)              # 删除有空值的列
data.dropna(how='all')           # 删除所有值为Nan的行
data.dropna(thresh=2)            # 至少保留两个非缺失值
data.strip()                     # 去除列表中的所有空格与换行符号
data.fillna(0)                   # 将空值填充0
data.replace(1, -1)              # 将1替换成-1

data.fillna(100)                           # 填充缺失值为100
data.fillna({'语文':100,'数学':100,})        # 不同列填充不同值
data.fillna(method='ffill')                 # 将空值填充为上一个值
data.fillna(method='bfill')                 # 将空值填充下一个值
data.fillna(method='bfill',limit=1)         # 将空值填充下一个值，限制填充数量为1

df.to_excel('winner.xlsx',index = False)    #保存为Excel表

将处理好的数据用plt展示并保存为HTML文件

import matplotlib as mpl
import pandas as pd
import xlrd
import openpyxl
from io import BytesIO
import base64
import matplotlib.pyplot as plt
import numpy as np

df = pd.read_excel("E:\\pycharm\\get_commitNum_from_gitlab\\项目统计表格\\2021年1-3月EP12代码提交汇总.xls",skipfooter=3)
# df=df.sort_values(by='提交次数') 
# df.loc[3, '总计代码'] = 0
print(df)
print(df["总计代码"])
fig = plt.figure(figsize=(12,4))
plt.plot(df["工程名称"],df["总计代码"],label='lines of code',linewidth=2,color='r',marker='o',
markerfacecolor='blue',markersize=10)
fig.autofmt_xdate() #自动根据标签长度进行旋转
plt.xlabel("Submission times")
plt.ylabel('lines of code')
plt.title("Submission times and lines of code")
plt.legend()
plt.grid()
# plt.show() #这里生成HTML时需要注释 不然会生成空白的HTML文件

# 转base64
figfile = BytesIO()
plt.savefig(figfile, format='png')
print(figfile)
figfile.seek(0)
figdata_png = base64.b64encode(figfile.getvalue()) # 将图片转为base64
figdata_str = str(figdata_png, "utf-8") # 提取base64的字符串，不然是b'xxx'

# 保存为.html
html = '<img src=\"data:image/png;base64,{}\"/>'.format(figdata_str)
filename = '1-3月代码行数与项目折线图a.html'
with open(filename, 'w') as f:
    f.write(html)