Python之Pandas

0.创建并写入excel文件

# -*- coding: utf-8 -*-
# @Python学习计划：http://t.cn/A6xrIo4f
# @Software: Python&PyCharm 安装教程：https://mp.weixin.qq.com/s/a0zoCo9DacvdpIoz1LEN3Q
# @Description:
# Python全套学习资源：https://mp.weixin.qq.com/s/G_5cY05Qoc_yCXGQs4vIeg

import pandas as pd

df = pd.DataFrame()
df.to_excel('001.xlsx')

df = pd.DataFrame({'id':[1,2,3],'name':['a','b','c']})
df.to_excel('001-data.xlsx')

df = pd.DataFrame({'id':[1,2,3],'name':['a','b','c']})
df = df.set_index('id')	# 设置索引
df.to_excel('001-data-index.xlsx')

1.读取excel

# -*- coding: utf-8 -*-
# @Python学习计划：http://t.cn/A6xrIo4f
# @Software: Python&PyCharm 安装教程：https://mp.weixin.qq.com/s/a0zoCo9DacvdpIoz1LEN3Q
# @Description:
# Python全套学习资源：https://mp.weixin.qq.com/s/G_5cY05Qoc_yCXGQs4vIeg
import pandas as pd

people = pd.read_excel('people001.xlsx')
print(people.shape)
print(people.columns)
# 默认打印5行
# print(people.head())
# print(people.head(3))
# 默认打印5行
# print(people.tail())

# 脏数据处理：第一行错误数据，或者没有数据
# people = pd.read_excel('people002.xlsx',header=1)
# print(people.columns)

#脏数据处理：第一行没有列名
people = pd.read_excel('people003.xlsx',header=None)
people.columns = ['ID', 'Type', 'Title', 'FirstName', 'MiddleName', 'LastName']
# people = people.set_index('ID',inplace=True)
people.to_excel('output.xlsx')

2.行，列，单元格

# -*- coding: utf-8 -*-
# @Python学习计划：http://t.cn/A6xrIo4f
# @Software: Python&PyCharm 安装教程：https://mp.weixin.qq.com/s/a0zoCo9DacvdpIoz1LEN3Q
# @Description:
# Python全套学习资源：https://mp.weixin.qq.com/s/G_5cY05Qoc_yCXGQs4vIeg
import pandas as pd

d = {
    'x':100,
    'y':200,
    'z':300,
}
s1 = pd.Series(d)
print(s1.index)

L1 = [100,200,300]
L2 = ['x','y','z']
s2 = pd.Series(L1,index=L2)
print(s2.index)

s3 = pd.Series([100,200,300],index=['x','y','z'])
print(s3.index)

3.跳过几行，指定某些列

# -*- coding: utf-8 -*-
# @Python学习计划：http://t.cn/A6xrIo4f
# @Software: Python&PyCharm 安装教程：https://mp.weixin.qq.com/s/a0zoCo9DacvdpIoz1LEN3Q
# @Description:
# Python全套学习资源：https://mp.weixin.qq.com/s/G_5cY05Qoc_yCXGQs4vIeg
import pandas as pd

books = pd.read_excel('books.xlsx',skiprows=4,usecols='C:F',index_col=None)
print(books)

4.excel安装某些关键字对记录排序

# -*- coding: utf-8 -*-
# @Python学习计划：http://t.cn/A6xrIo4f
# @Software: Python&PyCharm 安装教程：https://mp.weixin.qq.com/s/a0zoCo9DacvdpIoz1LEN3Q
# @Description:
# Python全套学习资源：https://mp.weixin.qq.com/s/G_5cY05Qoc_yCXGQs4vIeg

import pandas as pd

products = pd.read_excel('C:/Temp/List.xlsx', index_col='ID')
products.sort_values(by=['Worthy', 'Price'], ascending=[True, False], inplace=True)
print(products)

5.excel数据筛选和过滤

# -*- coding: utf-8 -*-
# @Python学习计划：http://t.cn/A6xrIo4f
# @Software: Python&PyCharm 安装教程：https://mp.weixin.qq.com/s/a0zoCo9DacvdpIoz1LEN3Q
# @Description:
# Python全套学习资源：https://mp.weixin.qq.com/s/G_5cY05Qoc_yCXGQs4vIeg

import pandas as pd


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


def level_b(s):
    return 60 <= s < 90

students = pd.read_excel('C:/Temp/Students.xlsx', index_col='ID')
students = students.loc[students['Age'].apply(validate_age)].loc[students.Score.apply(level_b)]  # 两种语法
print(students)

6.matplotlib绘图

# -*- coding: utf-8 -*-
# @Python学习计划：http://t.cn/A6xrIo4f
# @Software: Python&PyCharm 安装教程：https://mp.weixin.qq.com/s/a0zoCo9DacvdpIoz1LEN3Q
# @Description:
# Python全套学习资源：https://mp.weixin.qq.com/s/G_5cY05Qoc_yCXGQs4vIeg

import pandas as pd
import matplotlib.pyplot as plt

students = pd.read_excel('./data/Students.xlsx')
students.sort_values(by='Number', inplace=True, ascending=False)
students.index = range(0, len(students))
print(students)

plt.bar(students['Field'], students['Number'], color='orange', width=0.7)
plt.xticks(students['Field'], rotation='90')
plt.title('International Student by Field', fontsize=16)
plt.xlabel('Field')
plt.ylabel('Number')
plt.tight_layout()
plt.show()