214 lines
6.2 KiB
Markdown
214 lines
6.2 KiB
Markdown
|
# Pandas-Dataframe使用笔记
|
|||
|
|
|
|||
|
|
## 一、Dataframe的读取和保存
|
|||
|
|
|
|||
|
|
**1.1 Dataframe导出csv**
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# Dataframe转CSV
|
|||
|
|
xlsx_file.to_csv('F:/XXX/XXX.csv', encoding="utf-8-sig",header=True)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**1.2 Pandas读取xlsx**
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
# xlsx_file_name 如：'F:/XXX/XXX.xlsx'
|
|||
|
|
# 一般xlsx默认的sheet_name是Sheet1
|
|||
|
|
xlsx_file = pd.read_excel(xlsx_file_name, sheet_name='Sheet1')
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**1.3 Dataframe的创建**
|
|||
|
|
|
|||
|
|
dataframe可以通过读取csv或者xlsx等方式创建，同时也可以通过数组创建
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
# 创建数组
|
|||
|
|
data_list = [[6,10,3],[1,5,4],[1,2,4],[1,15,24],[1,0,2],[3,7,9],[2,8,5]]
|
|||
|
|
# 通过数组创建dataframe, columns并不是必须的, 如果不提供的话默认用0,1,...,n表示
|
|||
|
|
df = pd.DataFrame(data_list,columns=['A','B','C'])
|
|||
|
|
# 指定dataframe的行索引, 这也不是必须的, 如果不提供的话默认用0,1,...,n表示
|
|||
|
|
df.index = ['G','H','I','J','K','L','M']
|
|||
|
|
# 打印结果
|
|||
|
|
print(df)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
## 二、Dataframe的操作
|
|||
|
|
|
|||
|
|
**2.1 获取Dataframe和行数和列数**
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
import numpy as np
|
|||
|
|
# 创建dataframe
|
|||
|
|
df = pd.DataFrame(np.arange(24).reshape(6,4), columns=['A', 'B', 'C', 'D'])
|
|||
|
|
row_nums = df.shape[0]
|
|||
|
|
col_nums = df.columns.size
|
|||
|
|
print(row_nums)
|
|||
|
|
print(col_nums)
|
|||
|
|
|
|||
|
|
# 获取特定行data.iloc[x,y]
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**2.2 Dataframe删除行、列**
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
import numpy as np
|
|||
|
|
# 创建dataframe
|
|||
|
|
df = pd.DataFrame(np.arange(24).reshape(6,4), columns=['A', 'B', 'C', 'D'])
|
|||
|
|
print(df)
|
|||
|
|
# 删除单行
|
|||
|
|
df1 = df.drop(axis=0, index = 1, inplace=False)
|
|||
|
|
print(df1)
|
|||
|
|
# 删除多行
|
|||
|
|
df2 = df.drop(axis=0, index = [1,2,4], inplace=False)
|
|||
|
|
print(df2)
|
|||
|
|
# 删除列
|
|||
|
|
df3 = df.drop(axis=1, columns = ['A','D'], inplace=False)
|
|||
|
|
print(df3)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
注意删除多行的时候要确保index存在，一种非常隐蔽的错误是：
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
import numpy as np
|
|||
|
|
df1 = pd.DataFrame(np.arange(12).reshape(3,4), columns=['A', 'B', 'C', 'D'])
|
|||
|
|
df2 = pd.DataFrame(np.arange(12).reshape(3,4), columns=['A', 'B', 'C', 'D'])
|
|||
|
|
# ignore_index=True 保留原索引
|
|||
|
|
new_df = pd.concat([df1,df2], ignore_index=False)
|
|||
|
|
# 打印可以看到拼接之后索引只有0,1,2
|
|||
|
|
print(new_df)
|
|||
|
|
# 当我们调用删除行函数的时候会报错,因为没有index=3,虽然这个dataframe是6x4大小的
|
|||
|
|
# 这是一个非常隐蔽的错误
|
|||
|
|
df3 = new_df.drop(axis=0, index = 3, inplace=False)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**2.3 Dataframe的排序**
|
|||
|
|
|
|||
|
|
dataframe的排序有通过行列的名称进行排序，也有同行的数值或者列的数值进行排序。对于数值排序，采用sort_values函数。
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
# 创建dataframe
|
|||
|
|
data_list = [[6,10,3],[1,5,4],[1,2,4],[1,15,24],[1,0,36],[3,7,9],[2,8,5]]
|
|||
|
|
df = pd.DataFrame(data_list,columns=['A','B','C'])
|
|||
|
|
df.index = ['G','H','I','J','K','L','M']
|
|||
|
|
# 对列A进行降序排列
|
|||
|
|
# ascending表示是否升序排列, inplace表示在自身进行排序
|
|||
|
|
df.sort_values(by='A',axis=0,ascending=False,inplace=True)
|
|||
|
|
print(df)
|
|||
|
|
|
|||
|
|
df = pd.DataFrame(data_list,columns=['A','B','C'])
|
|||
|
|
df.index = ['G','H','I','J','K','L','M']
|
|||
|
|
# 对A列和B列进行升序排列,按照A、B的优先级进行排序
|
|||
|
|
df_data_order = df.sort_values(by=['A','B'],ascending=[True,True])
|
|||
|
|
print(df_data_order)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
很多时候，对于一些默认行号的dataframe，排序之后会把把行号打乱。这个时候可以通过reset_index函数重置索引。
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
data = [['a','3'],['b','1'],['c','2']]
|
|||
|
|
df = pd.DataFrame(data)
|
|||
|
|
df = df.sort_values(by = 1,axis = 0,ascending = False)
|
|||
|
|
# 排序后的行号是乱的
|
|||
|
|
print(df)
|
|||
|
|
# 重置索引后行号按照0,1,2,...顺序
|
|||
|
|
df = df.reset_index(drop=True)
|
|||
|
|
print(df)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**2.4 Dataframe的拼接**
|
|||
|
|
|
|||
|
|
Dataframe的拼接有几个函数：merge、concat等函数
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
import numpy as np
|
|||
|
|
df1 = pd.DataFrame(np.arange(12).reshape(3,4), columns=['A', 'B', 'C', 'D'])
|
|||
|
|
df2 = pd.DataFrame(np.arange(12).reshape(3,4), columns=['A', 'B', 'C', 'D'])
|
|||
|
|
# 拼接df1和df2,默认的拼接方向axis=0垂直方向拼接
|
|||
|
|
# ignore_index=True 忽略原索引
|
|||
|
|
new_df = pd.concat([df1,df2], ignore_index=True)
|
|||
|
|
print(new_df)
|
|||
|
|
# ignore_index=True 保留原索引
|
|||
|
|
new_df = pd.concat([df1,df2], ignore_index=False)
|
|||
|
|
print(new_df)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**2.5 Dataframe数据筛选**
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
# 创建数组
|
|||
|
|
data_list = [['拖动',10,3],[1,5,4],['拖动',2,4],[1,15,24],['滑动',0,2],[3,7,9],[2,8,5]]
|
|||
|
|
# 通过数组创建dataframe, columns并不是必须的, 如果不提供的话默认用0,1,...,n表示
|
|||
|
|
df = pd.DataFrame(data_list,columns=['A','B','C'])
|
|||
|
|
print(df)
|
|||
|
|
# 去掉A列中包含拖动的数值
|
|||
|
|
df1 = df[~(df['A']=='拖动')]
|
|||
|
|
# 重建索引序号
|
|||
|
|
df1 = df1.reset_index(drop=True)
|
|||
|
|
print(df)
|
|||
|
|
# 更加复杂的运算操作
|
|||
|
|
# df=df[~((df['B']>7)|(df['D']==0))]
|
|||
|
|
|
|||
|
|
df1 = df[(df['A'].isin(['拖动','滑动']) == True)]
|
|||
|
|
df1 = df1.reset_index(drop=True)
|
|||
|
|
print(df1)
|
|||
|
|
|
|||
|
|
# 列筛选A列和B列
|
|||
|
|
df = pd.DataFrame(data_list,columns=['A','B','C'])
|
|||
|
|
df = df[['A','B']]
|
|||
|
|
print(df)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
对dataframe的字符串筛选也可以通过Dataframe的contain函数，这种方式可以允许子串的搜索，同时contain函数也支持正则表达式。
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
# 创建数组
|
|||
|
|
data_list = [['拖动',10,3],[1,5,4],['拖动',2,4],[1,15,24],['滑动',0,2],[3,7,9],[2,8,5]]
|
|||
|
|
# 通过数组创建dataframe, columns并不是必须的, 如果不提供的话默认用0,1,...,n表示
|
|||
|
|
df = pd.DataFrame(data_list,columns=['A','B','C'])
|
|||
|
|
print(df)
|
|||
|
|
# 去掉A列中包含动的数值
|
|||
|
|
df=df[(df['A'].str.contains('动') == True)]
|
|||
|
|
# 重建索引序号
|
|||
|
|
df = df.reset_index(drop=True)
|
|||
|
|
print(df)
|
|||
|
|
|
|||
|
|
# contains函数支持正则表达式
|
|||
|
|
df = pd.DataFrame(data_list,columns=['A','B','C'])
|
|||
|
|
parttern = r'.*?'
|
|||
|
|
df=df[(df['A'].str.contains(parttern) == True)]
|
|||
|
|
print(df)
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
**2.6 Dataframe NaN处理**
|
|||
|
|
|
|||
|
|
axis: default 0指行,1为列
|
|||
|
|
|
|||
|
|
how: {‘any’, ‘all’}, default **‘any’指带缺失值的所有行**;**'all’指清除全是缺失值的**
|
|||
|
|
|
|||
|
|
thresh: int,保留含有int个非空值的行
|
|||
|
|
|
|||
|
|
subset: 对特定的列进行缺失值删除处理
|
|||
|
|
|
|||
|
|
```python
|
|||
|
|
import pandas as pd
|
|||
|
|
import numpy as np
|
|||
|
|
|
|||
|
|
df = pd.DataFrame({'A': [np.nan, 1, 2], 'B': [10, np.nan, 10], 'C': [10, 25, 15]})
|
|||
|
|
print(df)
|
|||
|
|
# any表示某一行或者某一列有NaN即被抛弃, all表示清除全部都是NaN
|
|||
|
|
df = df.dropna(axis=0, how='any')
|
|||
|
|
print(df)
|
|||
|
|
# 删除pkg中存在NaN的列， subset=['pkg','xxx','xxxxx']
|
|||
|
|
# df2 = df.dropna(axis='index', how='all', subset=['pkg'])
|
|||
|
|
```
|
|||
|
|
|