• 1. xlrd + xlwt
  • 1.1 xlrd
  • 1.2 xlwt
• 2. xlutils
  • 2.1 xlutils
  • 2.2 用Excel和python生成自动化报表
• 3. pandas创建文件
• 4.pandas读取文件
  • 4.1正常情况
  • 4.2特殊情况1
  • 4.3特殊情况2
• 5.pandas 行列单元格
• 6. 数字区域读取，填充数字
• 7. 函数填充，计算列
• 8.排序，多重排序
• 9.数据筛选，过滤
• 10.柱状图
• 11.分组柱图，深度优化
• 12.叠加柱状图，水平柱状图
• 13.饼图
• 14 折线图和区域叠加图
• 15 散点图，直方图
• 16.密度图，数据相关性
• 17.多表联合（从vlookup到join）
• 18 数据校验，轴的概念
• 19 把一列数据分割成两列
• 20求和，求平均，统计导引
• 21 定位，消除重复行
• 22 旋转数据表
• 23 读取CSV，tsv，txt文件中的数据
• 24 透视表，分组，聚合
• 25线性回归，数据预测
• 26条件格式（上）
• 27 条件格式（下）
• 28行操作集合
• 29 列操作集合
• 30 读取数据库
• 31 编写复杂方程

1. xlrd + xlwt

1.1 xlrd

import xlrd
xlsx = slrd.open_workbook("文件路径")
table = xlsx.sheet_by_index(0)//使用索引打开，第0个表
#table = xlsx.sheet_by_name("表名")//根据表名打开
print(table.cell_value(0, 0))
print(table.cell(1, 2).value)
print(table.row(1)[2].value)

1.2 xlwt

import xlwt
new_workbook = xlwt.Workbook()#新建一张工作蒲
worksheet = new_workbook.add_sheet("new_test")#添加了一张工作表，并命名
worksheet.write(0, 0, '写入的内容')
new_workbook.save("文件路径")#保存的是工作蒲

2. xlutils

2.1 xlutils

from xlutils.copy import copy
import xlrd
import xlwt
tem_excel = xlrd.open_wordbook("文件路径.xls", formatting_info = True)
tem_sheet = tem_excel.sheet_by_index(0)

#复制工作蒲，打开第一个工作表
new_excel = copy(tep_excel)
new_sheet = new_excel.get_sheet(0)

#写入数据，写入的数据没有格式
new_sheet.write(2, 1, 12)
new_sheet.write(3, 1, 18)
new_sheet.write(4, 1, 19)
new_sheet.write(5, 1, 15)
new_excel.save("文件路径.xls")






#设置格式
style = xlwt.XFStyle()
#字体（字号，加粗，倾斜）
font = xlwt.Font()
font.name = '微软雅黑'
font.bold = True
font.height = 360

style.font = font
#边框（粗实线，细线，点划线）
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottpm = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders

#对齐方式（水平居中，垂直居中，左对齐，右对齐）
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment

new_sheet.write(2, 1, 12, style)
new_sheet.write(3, 1, 18, style)
new_sheet.write(4, 1, 19, style)
new_sheet.write(5, 1, 15, style)
new_excel.save("文件路径.xls")

2.2 用Excel和python生成自动化报表

import xlrd
import xlwt
import xlutils.copy import copy
xlsx = xlrd.open_workbook("文件路径.xlsx")
table = xlsx.sheet_by_index(0)
all_data = []
for n in range(1, table.nrows):
    company = table.cell(n, 1).value
    price = table.cell(n, 3).value
    weight = table.cell(n, 4).value
    
    data = {"company":company, "weight":weight, "price":price}
    all_data.append(data)
a_weight = []
a_total_price = []
b_weight = []
b_total_price = []
c_weight = []
c_total_price = []
d_weight = []
d_total_price = []

for i in all_data:
    if i['company'] == '张三培良':
        a_weight.append(i['weight'])
        a_total_price.append)i['weight'] * i['price'])
    if i['company'] == '':
        #下面代码类似上
tem_excel = xlrd.open_workbook("文件路径", formatting_info = True)
tem_sheet = tem_excel.sheet_by_index(0)

new_excel = copy(tem_excel)
new_sheet = new_excel.get_sheet(0)
#设置格式
style = xlwt.XFStyle()
#字体（字号，加粗，倾斜）
font = xlwt.Font()
font.name = '微软雅黑'
font.bold = True
font.height = 360

style.font = font
#边框（粗实线，细线，点划线）
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottpm = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders

#对齐方式（水平居中，垂直居中，左对齐，右对齐）
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment

new_sheet.write(2, 1, len(a_weight), style)
new_sheet.write(2, 2, round(sum(a_weight), 2), style)
new_sheet.write(2, 3, round(sum(a_total_price), 2), style)
#下面bcd同上
new_excel.save("文件路径.xlsx")

3. pandas创建文件

import pandas as pd
df = pd.DataFrame({"id":[1, 2, 3], "Name":["tim", "victor", "nick"]})
df.set_index("id")
df.to_excel("D:/temp/output.xlsx")
print("done")

4.pandas读取文件

4.1正常情况

import pandas as pd
people = pd.read_excel("d:/temp/people.xlsx")
print(people.shape)
print(people.columns)
print(people.head())#默认五行
print(people.head(3))
print(people.tail(3))

4.2特殊情况1

#首行没有数据
import pandas as pd
people = pd.read_excel("文件路径", header = 1)
print(people.columns)
#如果不存在标题
people = pd.read_excel("文件路径", header = None)
print(people.columns)
people.columns = ["id", "type", "title", "firstname", "middlename", "lastname"]
people = people.set_index("id", inplace = True)
people.to_excel("文件路径")
print("done")

4.3特殊情况2

import pandas as pd
df = pd.read_excel("文件路径", index_col = "id")
print(df.head())
df.to_excel("新的文件路径")

5.pandas 行列单元格

import pandas as pd
d = {"x":100, "y":200, "z":300}
s1 = pd.Series(d)
#print(s1.data)

l1 = [100, 200, 300]
l2 = ["x", "y", "z"]
s2 = pd.Series(l1, index = l2)
#s2 = pd.Series([100, 200, 300], index = 12)
print(s2)
print(s2.index)
----------------------------
x    100
y    200
z    300
dtype: int64

import pandas as pd
s1 = pd.Series([1, 2, 3], index=[1, 2, 3], name = "a")
s2 = pd.Series([10, 20, 30], index=[1, 2, 3], name = "b")
s3 = pd.Series([100, 200, 300], index=[1, 2, 3], name = "c")
df = pd.DataFrame({s1.name:s1, s2.name:s2, s3.name:s3})
print(df)
---------------------
把每个序列当做一列
   a   b    c
1  1  10  100
2  2  20  200
3  3  30  300

import pandas as pd
s1 = pd.Series([1, 2, 3], index=[1, 2, 3], name = "a")
s2 = pd.Series([10, 20, 30], index=[1, 2, 3], name = "b")
s3 = pd.Series([100, 200, 300], index=[1, 2, 3], name = "c")
df = pd.DataFrame([s1, s2, s3])
print(df)
----------------
把每个序列当做一行，名字当做行号
     1    2    3
a    1    2    3
b   10   20   30
c  100  200  300


import pandas as pd
s1 = pd.Series([1, 2, 3], index=[1, 2, 3], name = "a")
s2 = pd.Series([10, 20, 30], index=[1, 2, 3], name = "b")
s3 = pd.Series([100, 200, 300], index=[2, 3, 4], name = "c")
df = pd.DataFrame({s1.name:s1, s2.name:s2, s3.name:s3})
print(df)
---------------
     a     b      c
1  1.0  10.0    NaN
2  2.0  20.0  100.0
3  3.0  30.0  200.0
4  NaN   NaN  300.0

6. 数字区域读取，填充数字

iimport pandas as pd
from datetime import date, timedelta
def add_month(d, md):
    yd = md // 12
    m = d.month + md % 12
    if(m != 12):
        yd += m // 12
        m = m % 12
    return date(d.year + yd, m, d.day)
books = pd.read_excel("文件路径", skiprows = 3, usecols= "C:F", index_col=None, dtype = {"id":str, "Instore":str})
print(books["id"])
print(books)
print(type(books["id"]))
books["id"].at[0] = 100
print(books["id"])
start = date(2018, 1, 1)
for i in books.index:
    #改series的值
    books["id"].at[i] = i + 1
    books["Instore"].at[i] = "yes" if i % 2 == 0 else "No"
    #books["date"].at[i] = start + timedelta(days = i)  天数增加
    #books["data"].at[i] = date(start.year + i, start.month, start.day)  年数增加
    books["data"].at[i] = add_month(start, i)
    
    
    #还可以这样操作，直接改dateframe的值
    books.at[i, "id"] = i + 1
    books.at[i, "Inscore"] = "yes" if i % 2 == 0 else "No"
    books.at[i, "date"] = add_month(start, i)
    
print(books["id"])#浮点型
print(books)
books.set_index("id", inplace = True)
books.to_excel("文件路径")

7. 函数填充，计算列

import pandas as pd
books = pd.read_excel("文件路径", index_col = 'id')
------------方式一：
books["price"] = books["Listprice"] * books["Discount"]
print(books)
-------------方式二：（可以用于计算某段范围）
for i in books.index:
    books["Price"].at[i] = books["ListPrice"].at[i] * books["Discount"].at[i]
print(books)
for i in range(5, 16):
    books["Price"].at[i] = books["ListPrice"].at[i] * books["Discount"].at[i]
    
books["Listprice"] = books["Listprice"] + 2

def add_2(x):
    return x + 2
books["listprice"] = books["Listprice"].apply(add_2)

books["listprice"] = books["listprice"].apply(lambda x: x + 2)

8.排序，多重排序

import pandas as pd
products = pd.read_excel("文件路径")
print(products)
products.sort_values(by = "price", inplace = True, ascending = False)//先按照price排序
products.sort_values(by = "worthy", inplace = True, ascending = False)//第二次排序会对第一次排序进行覆盖
------------正确的做法
products.sort_values(by = ["worthy", "price"], inplace = True, ascending = [True, False])

9.数据筛选，过滤

import pandas as pd

def age_18_to_30(a):
    return 18 <= a <= 30


def level_a(s):
    return 85 <= s <= 100


students = pd.read_excel("文件路径", index_col = "id")
students = students.loc[students["age"].apply(age_18_to_30)].loc[students["score"].apply(level_a)]
print(students)

10.柱状图

import pandas as pd
import matplotlib.pyplot as plt
students = pd.read_excel("文件路径")
students.sort_values(by = "number", inplace = True, ascending = True)
print(students)
================方式一：
#students.plot.bar(x = "field", y = "number", color = "orange", title = "internation students by field")
================方式二：
plt.bar(students.field, students.number, color = "orange")
plt.xticks(student.field, rotation = "90")
plt.xlabel("field")
plt.ylabel("number")
plt.title("internation student by field")
plt.tight_layout()
plt.show()

11.分组柱图，深度优化

import pandas as pd
import matplotlib.pyplot as plt

students = pd.read_excel("文件路径")
students.sort_values(by = "2017", inplace = True, ascending = False)
print(students)
students.plot_bar(x = "field", y = [2016, 2017], color = ["orange", "red"])
plt.title["international students by field", fontsize = 16, fontweight = "bold"]
plt.xlabel["field", fontweight = "bold"]
plt.ylabel["number", fontweight = "bold"]
ax = plt.gca[]
ax.set_xticklabels[students["field"], rotation = 45, ha = "right"]
f = plt.gcf[]
f.subplots_adjust[left = 0.2, bottom = 0.42]
plt.tight_layout()
plt.show()
注意一下：那些[]全是()

12.叠加柱状图，水平柱状图

import pandas as pd
import matplotlib.pyplot as plt
users = pd.read_excel("文件路径")
users["total"] = users["Oct"] + users["Nov"] + users["Dec"]
users.sort_values(by = "Total", inplace = True, ascending = False)
print(users)

users.plot.bar(x = "Name", y = ["Oct", "Nov", "Dec"], stacked = True, title = "user behavior")#竖直
user.plot.barh(x = "name", y = ["oct", "nov", "dec"], stacked = True, title = "user behavior")#水平

plt.tight_layout()
plt.show()

13.饼图

import pandas as pd
import matplotlib.pyplot as plt
方法一：
students = pd.read_excel("文件路径", index_col = "from")
print(students)

students["2017"].sort_values(ascending = True).plot.pie(fontsize = 8, startangle = -270)
plt.title("source of internation students", fomtsize = 16, fontweight = "blod")
plt.ylabel("2017", fontsize = 12, fontweight = "bold")
plt.show()


方法二：
import pandas as pd
import matplotlib.pyplot as plt

students = pd.read_excel("文件路径")
print(students)

students["2017"].plot.pie(fontsize = 8, counterclock = False, startangle = -270)
plt.title("source of internation students", fontsize = 16, fontweight = "bold")
plt.ylabel("2017", fontsize = 12, fontweight = "bold")
plt.show()

14 折线图和区域叠加图

import pandas as pd
import matplotlib.pyplot as plt

weeks = pd.read_excel("文件路径")
print(weeks)
print(weeks.columns)

weeks.plot.area(y = ["列名", "列名", "列名"])#叠加区域图

weeks.plot.bar(y = ["列名", "列名", "列名"], stacked = True)#叠加柱状图
plt.title("sales weekly thread", fontsize = 16, fontweight = "bold")
plt.ylabel("total", fontsize = 12, fontweight = "bold")
plt.xticks(weeks.index, fontsize = 8)
plt.show()

15 散点图，直方图

import pandas as pd
immport matplotlib.pyplot as plt

pd.options.display.max_columns = 777
homes = pd.read_excel("文件路径")
print(homes.head())

homes.plot.scatter(x = "sqlt_living", y = "price")
plt.show()

16.密度图，数据相关性

import pandas as pd
import matplotlib.pyplot as plt

pd.optiones.display.max_columns = 777
homes = pd.read_excel("文件路径")
print(homes.head())


homes.sqft_living.plot.hist(bins = 100)
plt.xticks(range(0, max(homes.price), 500), fontsize = 8, rotation = 90)
plt.show()

#密度图
homes.sqft_living.plot.kde()
plt.xticks(range(0, max(homes.sqlt_living), 500), fontsize = 8, rotationo = 90)

import pandas as pd
import matplotlib.pyplot as plt
pd.options.display.max_columns = 777
homes = pd.read_excel("文件路径")
print(homes.corr())

17.多表联合（从vlookup到join）

import pandas as pd
students = pd.read_excel("文件路径", sheet_name = "Students", index = "id")
scores = pd.read_excel("文件路径", sheet+name = "Scores", index = "id")
print(students)
print(scores)

table = students.merge(scores,how = "left", left_on = students.index, right_on = scores.index).final(0)
table.score = table.Score.astype(int)
print(table)

table = students.join(scores, how = "left").fillna(0)
table.Score = table.Score.astype(int)
print(table)

18 数据校验，轴的概念

import pandas as pd
def test(row):
    try:
        assert 0 <= row.Score <= 100
    except:
        print("has a invalid score")
        
def test1(row):
    if not 0 <= row.Score <= 100:
        print("has a invalid score")
students = pd.read_excel("文件路径")
students.apply(score_validation, axis = 1)
print(students)

19 把一列数据分割成两列

import pandas as pd

employees = pd.read_excel("文件路径")

df = employees["Full Name"].str.split(n = 0, expand = True)#n表示切割后保留的资源
employees["First Nmae"] = df[0]
employees["Last name"] = df[1].str.upper()
print(df)
print(employees)

20求和，求平均，统计导引

import pandas as pd

students = pd.read_execl("文件路径")
print(students)

temp = students[["Test_1", "Test_2", "Test_3"]]
result = temp.sum()
print(result)
print(type(result))
print(temp)

row_sum = temp.sum(axis = 1)
row_mean = temp.mean(axis = 1)
print(row_mean)
students["Total"] = row_sum
students["Average"] = row_mean
print(students)

col_mean = students[["Test_1", "Test_2", "Test_3", "Total", "Average"]].mean()
print(col_mean)
col_mean["Name"] = "Summary"
students = students.append(col_mean, ignore_index = True)
print(students)

21 定位，消除重复行

import pandas as pd

students = pd.read_excel("文件路径")
students.drop_duplicates(subset = "Name", inplace = True, keep = "first/last")#多列可以用一个列表
print(students)


#找出重复数据
dupe = students.duplicated(stbset = "Name")
print(dupe.any())
print(type(dupe))

dupe = dupe(dupe == True)#等价于dupe = dupe(dupe)
print(dupe.index)
print(students.iloc(dupe.index))

22 旋转数据表

import pandas as pd

pd.options.display.max_columns = 999
videos = pd.read_excel("文件路径", index_col = "Month")
table = videos.transfose()
print(videos)

23 读取CSV，tsv，txt文件中的数据

import pandas as pd

students1 = pd.read_csv("文件路径", index_col = "id")
print(students)

students2 = pd.read_csv("文件路径", sep = "\t", index_col = "id")
print(students2)

students3 = pd.read_csv("文件路径", sep = '|', index_col = "id")
print(students3)

24 透视表，分组，聚合

import pandas as pd
import numpy as np

pd.options.display.max_columns = 999
orders = pd.read_excel("文件路径")

orders["Year"] = pd.Datetimeindex(orders["Date"]).year

print(orders.head())
print(orders.Date.dtype)

pt1 = orders.pivot_table(index = "Category", column = "Year", values = "Total", aggfunc = np.sum)
groups = orders.grouopby(["Category", "year"])
s = groups["Total"].sum()
c = groups["id"].count()

pt2 = pd.Dateframe({"Sum":s, "Count":c})

25线性回归，数据预测

import pandas as pd
import matplotlib.pyplot as plt
from scipy.stats import linregress

sales = pd.read_excel("文件路径", dtype = ["Month":str])
print(sales)

slpoe, intercept, r, p, std_err = linregress(sales.index, sales.Revenue)
exp = slaes.index * slope intercept

plt.scatter(sales.index, sales.Revenue)
plt.bar(sales.index, sales.Revenue)
plt.plot(sales.index, exp, color = "orange")
plt.title("Sales")
plt.title(f"y = {slope} * x + {intercept}")
plt.xticks(sales.index, sales.Month, rotation = 90)
plt.tight_layout()
plt.show()

26条件格式（上）

import pandas as pd
students = pd.read_excel("文件路径")
def low_score_red(s):
    color = "red" if s < 60 else "green"
    return f"color:{color}"
def high_score_green(col):
    return ["background-color:line" if s == col.max() else "background-color:white" for s in col]
print(students)
students.style.applymap(10w_score_red, subset = ["Test_1", "test_2", "test_3"]).apply(high_score_green, subset = ["Test_1", "test_2", "test_3"])
#applymap是对选中区域的所有单元格，apply是施加在行上或者列上

27 条件格式（下）

import pandas as pd
import seaborn as sns


#通过颜色深浅显示成绩大小
color_map = sns.light_palette("green", as_camp = True)

students = pd.read_excel("文件路径")
students.style.background_gradient(color_mapm, subset = ["Test_1", "test_2", "test_3"])
print(students)

#通过数据条的长度
import pandas as pd
students = pd.read_excel("文件路径")
students.style-bar(color = 'orange', subset = ["Test_1", "test_2", "test_3"])

28行操作集合

import pandas as pd
page_001 = pd.read_excel("文件路径", sheet_name = "page_001")
page_002 = pd.read_excel("文件路径", sheet_name = "page_002")

#将一张数据表的内容追加到另一张数据表的后面
students = page_001.append(page_002).reset_index(drop = True)

#在末尾追加一个手动创建的新行
stu = pd.Serires({"id":41, "name":"abel", "score":"99"})
students = students.append(stu, ignore_index = True)

#更改数据表中已经有的值
students.at[39, "name"] = "baliey"
students.at[39, "score"] = 120

stu = pd.Series({"id":40, "name":"bailey", "score":120})
students.iloc[39] = stu

#在数据表中插入一行
stu = pd.Series({"id":101, "name":"danni", "score":100})
part1 = students[:20]
part2 = students[20:]
students = part1.append(stu, ignore_index = True).append(part2).reset_index(drop = True)

#删除数据行
students.drop(index = [0, 1, 2], inplace = True)
students.drop(index = range(10), inplace = True)
students.drop(index = students[0, 10].index, inplace = True)

#按条件的删除
for i in range(5, 15):
    students["name"].at[i] = ""
missing = students.loc[students["name"] == ""]
students.drop(index = missing.index, inplace = True)
students = students.reset_index(drop = True)

29 列操作集合

import pandas as pd
import numpy as np

page_001 = pd.read_excel("文件路径", sheet+name = "page_001")
page_002 = pd.read_excel("文件路径", sheet_name = "page_002")

#将两张表并列放在一起
students = pd.concat([page_001, page_002], axis = 1).reset_index(drop = True)

#在列的后面追加一列
students["age"] = 25;
#等价于
students["Age"] = np.repeat(25, len(students))
students["Age"] = np.arange(0, len(students))
print(students)

#删除列
students.drop(columns = ["Age", "Score"], inplace = True)
print(students)

#在列之间插入一列
students.insert(1, column = "Foo", value = np.repeat("Foo", len(students)))
students.rename(columns = {"foo":"FOO", "Name":"NAME"}, inplace = True)
print(students)

#去掉空值操作
students["ID"] = students["ID"].astype(float)
for i in range(5, 15):
    students["ID"].at[i] = np.nan
students.dropna(inplace = True)
print(students)

30 读取数据库

import pandas as pd
import pyobdc
import sqlalchemy

connections = pyodbc.connect("DRiver = {SQL Server}; SERVER={local];DATABASE=AdventureWorks;USER=sa;PASSWORD=123456")

query = 'select Firstname, Lastname from Person.Person'

df1 = pd.read_sql_query(query, connection)

print(df1.head())



engine = sqlalchemy.create_engine('mssql+pyodbc://sa:123456@(local)/AdventureWorks?driver=SQL+Server')
df2.pd_read_sql_query(query, engine)
print(df2)

31 编写复杂方程

import pandas as pd
import numpy as np


def get_circumcircle_area(l, h):
    r = np.sqrt(l ** 2 + h ** 2) / 2
    return r * r * np.pi

def wrapper(row):
    return get_circumcircle_area(row("Length", row["Height"]))
rects = pd.read_excel("文件路径", index_col = 'ID')
rects["CA"] = rects.apply(wrapper, axis = 1)

//等价于

rects["CA"] = rects.apply(lambad row:get_circumcle_area(row(["Length"], row["Height"])), axis = 1)
#轴为零的时候是纵向扫描，轴为一的时候是横向扫描
print(rects)