Pandas数据处理
Pandas数据处理
1.操作行名和列名
(1)设置某一列为行名
df.set_index(列名,drop=布尔值)
# drop表示是否删除这一列
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.set_index("编号", drop=True)
print(df)
![](https://blog.hanstary.com/file/77.png)
(2)重置行名
df.reset_index(drop=布尔值)
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.sample(5)
df=df.reset_index(drop=False)
print(df)
![](https://blog.hanstary.com/file/78.png)
(3)修改行名和列名
df.index=列表
df.columns=列表
df.rename(index=字典,columns=字典)
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df.columns = ["id", "fruit", "price", "producer"]
df = df.rename(
index={0: "000", 1: "001", 2: "002", 3: "003", 4: "004", 5: "005", 6: "006", 7: "007", 8: "008", 9: "009",
10: "010"})
print(df)
![](https://blog.hanstary.com/file/79.png)
2.类型转换
(1)转换为字典
df.to_dict()
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
dic = df.to_dict()
print(dic)
print(type(dic))
![](https://blog.hanstary.com/file/80.png)
(2)转换为数组
np.array(df)
import numpy as np
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
arr = np.array(df)
print(arr)
print(type(arr))
![](https://blog.hanstary.com/file/81.png)
(3)转换为列表
先将DataFrame转换为数组,再将数组转换为列表
import numpy as np
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
arr = np.array(df)
lis = arr.tolist()
print(lis)
print(type(lis))
![](https://blog.hanstary.com/file/82.png)
(4)针对某一列转换
df[列名].astype(类型)
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df["价格"] = df["价格"].astype(float)
print(df)
![](https://blog.hanstary.com/file/83.png)
3.行列转置
df.T
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.T
print(df)
![](https://blog.hanstary.com/file/84.png)
4.大小排序
df.sort_values(by=列表,ascending=布尔值)
# ascending 设置升序或降序,True为升序,False为降序,默认为升序
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.sort_values(by=["价格"], ascending=True)
print(df)
![](https://blog.hanstary.com/file/85.png)
5.数据排名
df[列名].rank(ascending=布尔值,method=值)
method排序值如下
取值 | 说明 |
---|---|
average | 平均值排名(默认值) |
max | 最大值排名 |
min | 最小值排名 |
first | 按值在原始数据中出现的顺序进行排名 |
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df["排名"] = df["价格"].rank(ascending=False, method="max")
print(df)
![](https://blog.hanstary.com/file/86.png)
6.数据替换
df.replace(字典)
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.replace({4: 6})
print(df)
![](https://blog.hanstary.com/file/87.png)
7.数据移动
df.shift(n)
# n为正数,是向后移动,n为负数,是向前移动
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川'],
["0011", None, None, None],
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.shift(1)
print(df)
![](https://blog.hanstary.com/file/88.png)
8.数据清洗
(1)缺失值
A.判断缺失值
df.isnull()
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", None, 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川'],
["0011", None, None, None],
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
res = df.isnull()
print(res)
![](https://blog.hanstary.com/file/89.png)
B.删除缺失值
df.dropna(axis=0或1,how="any"或"all")
# any:当这一行有任意一个空白,那就删除这行
# all:当这一行全都是空白,才将这一行删除
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", None, 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川'],
["0011", None, None, None],
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.dropna(axis=0, how="any")
print(df)
![](https://blog.hanstary.com/file/90.png)
C.填充缺失值
df.fillna(value=字典,method=填充方式)
method的值如下
取值 | 说明 |
---|---|
ffill | 使用前值填充 |
bfill | 使用后值填充 |
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", None, 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川'],
["0011", None, None, None],
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.fillna(value={"水果": "椰子"})
print(df)
![](https://blog.hanstary.com/file/91.png)
(2)重复值
A.判断重复值
df.duplicated()
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '菠萝', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
res = df.duplicated()
print(res)
![](https://blog.hanstary.com/file/92.png)
B.删除重复值
df.drop_duplicates(subset=列表,keep=值)
# subset用于指定对那些列进行判断
keep的取值如下
取值 | 说明 |
---|---|
first | 保留第一次出现的重复行,删除后面的重复行(默认值) |
last | 保留最后一次出现的重复行,删除其他的重复行 |
False | 删除所以的重复行 |
import pandas as pd
data = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁'],
["0006", '葡萄', 8, '云南'],
["0007", '菠萝', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川'],
["0010", '桃子', 5, '四川']
]
df = pd.DataFrame(data, columns=["编号", "水果", "价格", "产地"])
df = df.drop_duplicates(subset="水果",keep="first")
print(df)
![](https://blog.hanstary.com/file/93.png)
9.数据合并
方法 | 说明 |
---|---|
concat() | 轴向合并 |
merge() | 主键合并 |
join() | 行列连接 |
append() | 纵向合并 |
(1)concat()
pd.concat(列表,axis=0或1,join=连接方式,ignore_index=布尔值)
A.axis
axis用于设置维度,0表示沿纵轴,1表示沿横轴
import pandas as pd
data1 = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁']
]
data2 = [
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df1 = pd.DataFrame(data1, columns=["编号", "水果", "价格", "产地"])
df2 = pd.DataFrame(data2, columns=["编号", "水果", "价格", "产地"])
res1 = pd.concat([df1, df2], axis=0, ignore_index=True)
res2 = pd.concat([df1, df2], axis=1, ignore_index=True)
print(res1)
print("=======================")
print(res2)
![](https://blog.hanstary.com/file/94.png)
B.ignore_index
忽略索引则会忽略两个表格原来的索引,合并数据并重新编排数据
import pandas as pd
data1 = [
["0001", '苹果', 5, '山东'],
["0002", '香蕉', 3, '海南'],
["0003", '橙子', 6, '江西'],
["0004", '西瓜', 2, '新疆'],
["0005", '草莓', 10, '辽宁']
]
data2 = [
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df1 = pd.DataFrame(data1, columns=["编号", "水果", "价格", "产地"])
df2 = pd.DataFrame(data2, columns=["编号", "水果", "价格", "产地"])
res1 = pd.concat([df1, df2], axis=0, ignore_index=True)
res2 = pd.concat([df1, df2], axis=0, ignore_index=False)
print(res1)
print("=======================")
print(res2)
![](https://blog.hanstary.com/file/95.png)
C.join
连接方式用如下两种
连接方式 | 说明 |
---|---|
outer | 外连接(默认值) |
inner | 内连接 |
import pandas as pd
data1 = [
["0001", '苹果', 5, '山东', "秋季"],
["0002", '香蕉', 3, '海南', "夏季"],
["0003", '橙子', 6, '江西', "秋季"],
["0004", '西瓜', 2, '新疆', "夏季"],
["0005", '草莓', 10, '辽宁', "春季"]
]
data2 = [
["0006", '葡萄', 8, '云南'],
["0007", '芒果', 7, '广西'],
["0008", '菠萝', 4, '广东'],
["0009", '梨', 4, '河北'],
["0010", '桃子', 5, '四川']
]
df1 = pd.DataFrame(data1, columns=["编号", "水果", "价格", "产地", "季节"])
df2 = pd.DataFrame(data2, columns=["编号", "水果", "价格", "产地"])
res1 = pd.concat([df1, df2], axis=0, join="outer", ignore_index=True)
res2 = pd.concat([df1, df2], axis=0, join="inner", ignore_index=True)
print(res1)
print("=======================")
print(res2)
![](https://blog.hanstary.com/file/96.png)
(2)merge()
pd.merge(df1,df2,how=连接方式)
how的取值如下
取值 | 说明 |
---|---|
inner | 内连接,求交集(默认值) |
outer | 外连接,求并集 |
left | 左连接 |
right | 右连接 |
A.内连接和外连接
import pandas as pd
data1 = [
["0001", '苹果', 5, '山东', "秋季"],
["0002", '香蕉', 3, '海南', "夏季"],
["0003", '橙子', 6, '江西', "秋季"],
["0004", '西瓜', 2, '新疆', "夏季"],
["0005", '草莓', 10, '辽宁', "春季"]
]
data2 = [
["0006", '葡萄', 8, '云南', '紫色'],
["0007", '芒果', 7, '广西', '黄色'],
["0008", '菠萝', 4, '广东', '黄色'],
["0009", '梨', 4, '河北', '黄色'],
["0010", '桃子', 5, '四川', '粉色']
]
df1 = pd.DataFrame(data1, columns=["编号", "水果", "价格", "产地", "季节"])
df2 = pd.DataFrame(data2, columns=["编号", "水果", "价格", "产地", "色泽"])
res1 = pd.merge(df1, df2, how="inner")
res2 = pd.merge(df1, df2, how="outer")
print(res1)
print("=======================")
print(res2)
![](https://blog.hanstary.com/file/97.png)
因为df1和df2没有相同的一行,所以是空白的
B.左连接和右连接
import pandas as pd
data1 = [
["0001", '苹果', 5, '山东', "秋季"],
["0002", '香蕉', 3, '海南', "夏季"],
["0003", '橙子', 6, '江西', "秋季"],
["0004", '西瓜', 2, '新疆', "夏季"],
["0005", '草莓', 10, '辽宁', "春季"]
]
data2 = [
["0006", '葡萄', 8, '云南', '紫色'],
["0007", '芒果', 7, '广西', '黄色'],
["0008", '菠萝', 4, '广东', '黄色'],
["0009", '梨', 4, '河北', '黄色'],
["0010", '桃子', 5, '四川', '粉色']
]
df1 = pd.DataFrame(data1, columns=["编号", "水果", "价格", "产地", "季节"])
df2 = pd.DataFrame(data2, columns=["编号", "水果", "价格", "产地", "色泽"])
res1 = pd.merge(df1, df2, how="left")
res2 = pd.merge(df1, df2, how="right")
print(res1)
print("=======================")
print(res2)
![](https://blog.hanstary.com/file/98.png)
left就会以df1为主,没有的数据会空着,right与之相反
(3)join()
df1.join(df2,on=行名或列名)
import pandas as pd
data1 = [
["0001", '苹果', 5, '山东', "秋季"],
["0002", '香蕉', 3, '海南', "夏季"],
["0003", '橙子', 6, '江西', "秋季"],
["0004", '西瓜', 2, '新疆', "夏季"],
["0005", '草莓', 10, '辽宁', "春季"]
]
data2 = [
["0006", '葡萄', 8, '云南', '紫色'],
["0007", '芒果', 7, '广西', '黄色'],
["0008", '菠萝', 4, '广东', '黄色'],
["0009", '梨', 4, '河北', '黄色'],
["0010", '桃子', 5, '四川', '粉色']
]
df1 = pd.DataFrame(data1, columns=["编号", "水果", "价格", "产地", "季节"]).set_index(["编号", "水果", "价格", "产地"])
df2 = pd.DataFrame(data2, columns=["编号", "水果", "价格", "产地", "色泽"]).set_index(["编号", "水果", "价格", "产地"])
res = df1.join(df2)
print(res)
![](https://blog.hanstary.com/file/99.png)
(4)append()
df1._append(df2,ignore_index=布尔值)
import pandas as pd
data1 = [
["0001", '苹果', 5, '山东', "秋季"],
["0002", '香蕉', 3, '海南', "夏季"],
["0003", '橙子', 6, '江西', "秋季"],
["0004", '西瓜', 2, '新疆', "夏季"],
["0005", '草莓', 10, '辽宁', "春季"]
]
data2 = [
["0006", '葡萄', 8, '云南', '紫色'],
["0007", '芒果', 7, '广西', '黄色'],
["0008", '菠萝', 4, '广东', '黄色'],
["0009", '梨', 4, '河北', '黄色'],
["0010", '桃子', 5, '四川', '粉色']
]
df1 = pd.DataFrame(data1, columns=["编号", "水果", "价格", "产地", "季节"])
df2 = pd.DataFrame(data2, columns=["编号", "水果", "价格", "产地", "色泽"])
res = df1._append(df2, ignore_index=True)
print(res)
![](https://blog.hanstary.com/file/100.png)
10.字符串处理
方法 | 说明 |
---|---|
len() | 获得长度 |
count() | 统计次数 |
strip() | 去除空格 |
split() | 分割字符串 |
replace() | 替换字符串 |
repeat() | 重复字符串 |
cat() | 连接列 |
lower() | 大写字母转小写字母 |
upper() | 小写字母转大写字母 |
swapcase() | 小写字母转大写字母,并且大写字母转换为小写字母 |
contains() | 判断是否包含某子字符串 |
startswith() | 判断是否以某子字符串开头 |
endswith() | 判断是否以某子字符串结尾 |
isnumeric() | 判断所有字符是否为纯数字 |
islower() | 判断所有字符是否为纯小写 |
isupper() | 判断所以字符是否为纯大写 |
这些方法针对Series
Series.str.方法名()