pandas玩Excel ---小白笔记1，感谢Timothy老师

一.用pandas创建Excel文件

1.创建一个空格的Excle文件

import pandas as pd

df = pd.DataFrame()
df.to_excel('新建的.xlsx')

效果：

　　  

 2.创建一个有内容的Excel文件

import pandas as pd
df = pd.DataFrame({"ID":[1,2,3],"Name":["张三","李四","王五"]})
df.to_excel('新建的.xlsx')

效果：

　　

3.如果不想要自带的索引，想用自己的ID作为索引，就可以使用一下代码

import pandas as pd
df = pd.DataFrame({"ID":[1,2,3],"Name":["张三","李四","王五"]})
df = df.set_index("ID")
df.to_excel('新建的.xlsx')

效果：

　　

 二.用pandas打开读取Excel文件

1.查看Excel的总行列

import pandas as pd
exl = pd.read_excel("新建的.xlsx")
print(exl.shape)

　　结果是：(3, 2)  

2.查看Excel所有的列名

import pandas as pd
exl = pd.read_excel("新建的.xlsx")
print(exl.columns)

　　结果是：Index(['ID', 'Name'], dtype='object')

3.查看Excel前n行数据

import pandas as pd
exl = pd.read_excel("新建的.xlsx")
#默认是5行，可以自己定义，写2只是显示前两行
print(exl.head(2))

　　结果是：

　　　　　　

 4.查看Excel后n行数据

import pandas as pd
exl = pd.read_excel("新建的.xlsx")
print(exl.tail(1))

　　结果是：

　　　　　　

5.如果Excel文件是这样子的：

　　

　　想要从第2行读取，就用header参数，默认是0，可以指定送第n行读取，这里注意，如果第一行是空行，就不需要用header参数指定

import pandas as pd
exl = pd.read_excel("新建的.xlsx",header=1)
print(exl)

　　效果是：

 　　　　　　

 6.如果表格没有列头，比如下图，此时就默认是0,1这样子

　　　　

import pandas as pd
exl = pd.read_excel("新建的.xlsx",header=None)
print(exl)

　　效果：

　　　　　　

 7.遇到6这种情况 我们想要设置表头就需要指定设置表头操作，并且不想要默认的索引，用ID代替

import pandas as pd
exl = pd.read_excel("新建的.xlsx",header=None)
exl.columns =["ID","name"]
exl.set_index("ID",inplace=True)
print(exl)

　　效果：

　　　　　　

 8.读取文件的时候可以指定索引列

import pandas as pd
exl = pd.read_excel("新建的.xlsx",index_col="ID")
print(exl)

　　效果是：

　　　　　　

 三.pandas的行和列计算

1.把序列写入excel中，把每个序列看做一列写入

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')
exl = pd.DataFrame({s1.name:s1,s2.name:s2,s3.name:s3})
print(exl)

　　效果：

　　　　　　

 2.把序列写入excel中，把每个序列看做一行写入

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')
exl = pd.DataFrame([s1,s2,s3])
print(exl)

　　效果：

　　　　　　

 3.s1 = pd.Series([1,2,3],index=[1,2,3],name='A')这里面的index是和pandas里面的index对齐关系，比如：

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,4],name='C')
exl = pd.DataFrame({s1.name:s1,s2.name:s2,s3.name:s3})
print(exl)

　　效果：

　　　　　　

 4.pandas 填充Excel

　　在pandas里面的每一列其实是一个序列Series。对序列的操作填写就用at函数，下图的数据不是顶格开始写的，读取的时候用skiprows=3,usecols='C:F'来指定读取的开始位置和读取位置usecols也可以写成usecols=“C”,"D","E",“F”,pandas读取Excel的时候如果是空的单元格，默认是浮点类型的。写字符串就会报错，这个时候就需要指定一下指定列的数据类型了。用到的参数是dtype={}字典，来指定

import pandas as pd
from datetime import date,timedelta
case_exl = pd.read_excel("填充.xlsx",skiprows=3,usecols='C:F',dtype={'ID':str,"是否通过":str,"执行日期":str})
start_data = date(2020,1,1)
for i in case_exl.index:
    case_exl.ID.at[i]=i+1
    case_exl.是否通过.at[i] = "Yes" if i%2 ==0 else 'NO'
    case_exl.执行日期.at[i] = start_data
print(case_exl)

　　效果：

　　　　　　

 5.给日期的每一行加1天，只需要循环的时候加 timedelta(days=i)

import pandas as pd
from datetime import date,timedelta
case_exl = pd.read_excel("填充.xlsx",skiprows=3,usecols='C:F',dtype={'ID':str,"是否通过":str,"执行日期":str})
start_data = date(2020,1,25)
for i in case_exl.index:
    case_exl.ID.at[i]=i+1
    case_exl.是否通过.at[i] = "Yes" if i%2 ==0 else 'NO'
    case_exl.执行日期.at[i] = start_data+timedelta(days=i)
print(case_exl)

　　效果：

　　　　　　

 6.给日期的每一行加1年，date(start_data.year+i,start_data.month,start_data.day)

import pandas as pd
from datetime import date,timedelta
case_exl = pd.read_excel("填充.xlsx",skiprows=3,usecols='C:F',dtype={'ID':str,"是否通过":str,"执行日期":str})
start_data = date(2020,1,25)
for i in case_exl.index:
    case_exl.ID.at[i]=i+1
    case_exl.是否通过.at[i] = "Yes" if i%2 ==0 else 'NO'
    case_exl.执行日期.at[i] = date(start_data.year+i,start_data.month,start_data.day)
print(case_exl)

效果：

　　　　　　

  7.给日期的每一行加1月，就不能这样写了date(start_data.year,start_data.month+i,start_data.day)

　　　　　　

 　　正确的写法是：

import pandas as pd
from datetime import date,timedelta
case_exl = pd.read_excel("填充.xlsx",skiprows=3,usecols='C:F',dtype={'ID':str,"是否通过":str,"执行日期":str})
start_data = date(2020,10,25)
#d是给进来的时间，md是要加的月数
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)
for i in case_exl.index:
    case_exl.ID.at[i]=i+1
    case_exl.是否通过.at[i] = "Yes" if i%2 ==0 else 'NO'
    case_exl.执行日期.at[i] = add_month(start_data,i)

case_exl.set_index("ID",inplace=True)
case_exl.to_excel('填充.xlsx')

print(case_exl)

　　效果：

　　　　　　

 8.以上4,5,6,7,是在序列中来填充数据的，下面直接是直接在DataFrame中填充数据

# *_*coding:utf-8 *_*
# @Author : zyb
import pandas as pd
from datetime import date,timedelta
case_exl = pd.read_excel("填充.xlsx",skiprows=3,usecols='C:F',dtype={'ID':str,"是否通过":str,"执行日期":str})
start_data = date(2020,10,25)
#d是给进来的时间，md是要加的月数
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)
for i in case_exl.index:
    case_exl.at[i,"ID"]=i+1
    case_exl.at[i,"是否通过"] = "Yes" if i%2 ==0 else 'NO'
    case_exl.at[i,"执行日期"] = add_month(start_data,i)
print(type(case_exl))
case_exl.set_index("ID",inplace=True)
case_exl.to_excel('填充1.xlsx')
print(case_exl)

效果和 7的效果是一样的，写法只是for循环中的小区别

9.pandas 计算Excel后填充，源文件如下：最后售价 = 价格*折扣

import pandas as pd
exl = pd.read_excel('计算.xlsx',index_col="ID")
exl['最后售价'] = exl.价格*exl.折扣
exl.to_excel('计算.xlsx')
print(exl)

　　效果：

　　　　　　

 10.上面的9是整体运算的，效率高，但是有时候需要按照行来计算，就可以这样写：

import pandas as pd
exl = pd.read_excel('计算.xlsx',index_col="ID")
for i in exl.index:
    exl.最后售价.at[i] = exl.价格.at[i]*exl.折扣.at[i]
print(exl)

　　效果同9，可以修改循环次数，决定从那里开始和结束，效率没有9高，但是场景应该比9多

 11.计算某一列还可以调用函数，比如给价格涨5块钱

import pandas as pd
exl = pd.read_excel('计算.xlsx',index_col="ID")
def add_5(x):
    return x+5
exl['价格'] = exl.价格.apply(add_5)
# exl.价格 = exl['价格'].apply(add_5)
# exl.价格 = exl['价格'].apply(lambda x:x+2)
print(exl)

　　效果：

　　　　　　

 四.pandas对Excel排序，源文件：

排序使用sort_values函数，by是排序的列名，多个排序是传列表，ascending是正序和倒序，默认是从小到大，多个排序方式用列表传递

 1.按照价格排序

import pandas as pd
exl = pd.read_excel('排序.xlsx',index_col="ID")
exl.sort_values(by='价格',inplace=True)
print(exl)

　　效果：

　　　　　　

2.先按照贵不贵排序，在按照价格排序

import pandas as pd
exl = pd.read_excel('排序.xlsx',index_col="ID")
exl.sort_values(by=['贵不贵','价格'],inplace=True,ascending=[False,True])
print(exl)

　　效果：

　　　　　　

  五.pandas对Excel数据赛选：

源数据：

 1.看18-28岁 分数是85分以上的信息

# *_*coding:utf-8 *_*
# @Author : zyb
import pandas as pd
def age_18_to_28(a):
    return 18<=a<28
def fen_85(s):
    return 85<=s<100
exl = pd.read_excel('筛选.xlsx',index_col="ID")
# exl = exl.loc[exl.年龄.apply(age_18_to_28)].loc[exl.分数.apply(fen_85)]
exl = exl.loc[exl.年龄.apply(lambda a:18<=a<28)].loc[exl.分数.apply(lambda s:85<=s<100)]
print(exl)

　　效果：