读取常见文件
import pandas as pd
import pymysql
def pd_read_csv(path):
df = pd.read_csv(path)
print(df.to_string())
print(df.head())
print(df.head(10))
print(df.tail())
print(df.tail(10))
def pd_read_excel(path):
df = pd.read_excel(path)
print(df.to_string())
def pd_read_sql():
conn = pymysql.connect(host='localhost', user='root', password='root', database='bfl', port=3306)
df = pd.read_sql('select * from bfl_attr', con=conn)
print(df.to_string())
if __name__ == '__main__':
pd_read_csv('./assets/test.csv')
# pd_read_excel('./assets/test.xlsx')
# pd_read_sql()
pass
数据类型解释
import pandas as pd
'''
DataFrame:二维数据,整个表格,多行多列
Series:一维数据,一行或者一列
例1:DataFrame
name age sex --> DataFrame.columns
0 john 13 male
1 lili 15 female
2 mike 13 male
3 Bob 77 male
|
↓
DataFrame.index
例2:
---- ---
|name| |age| sex
______________________________________________
|0 |john| |13 | male | --> Series
----------------------------------------------
1 |lili| |15 | female
2 |mike| |13 | male
3 |Bob | |77 | male
---- ---
| |
↓ ↓
Series Series
'''
def pd_series():
df = pd.Series([1, 'a', 5.2, 7])
print(df)
'''
左侧为索引,右侧是数据
0 1
1 a
2 5.2
3 7
dtype: object
'''
print(df.index) # 获取数据索引
print(df.values) # 获取数据
# 创建一个具有标签索引的Series
def pd_series2():
df = pd.Series([1, 'a', 5.2, 7], index=['a', 'b', 'c', 'd'])
print(df)
# 通过字典建立Series
def pd_series3():
s_data = {'a': 123, 'b': 456, 'c': 789, 'd': 'askdj'}
df = pd.Series(s_data)
print(df)
print(df['a']) # 单值通过字典的方式查询
print(df[['a', 'b']]) # 多值通过[[]]方式查询,返回的也是一个Series
def pd_dataframe():
data = {
'name': ['john', 'lili', 'mike', 'Bob'],
'age': [13, 15, 13, 17],
'gender': ['male', 'female', 'male', 'male']
}
df = pd.DataFrame(data)
print(df)
print(df.dtypes) # 列类型
print(df.columns) # 列索引
print(df.index) # 行索引
# 从DataFrame中查询时,如果是查询的是一行或者一列,返回的Series,如果是多行或者多列返回的还是DataFrame
# 查询列
print(df['name'])
print(type(df['name'])) # 返回的Series
print(df[['name', 'age']]) # 多值通过[[]]方式查询
print(type(df[['name', 'age']])) # 返回的DataFrame
# 查询行,loc相当于两个值[[行(单个或区间时省略方括号)],[列(单个或区间时省略方括号)]]
print(df.loc[0])
print(type(df.loc[0])) # 返回的Series
print(df.loc[0:2])
print(type(df.loc[0:2])) # 返回的DataFrame
if __name__ == '__main__':
# pd_series()
# pd_series2()
# pd_series3()
pd_dataframe()
pass
查询和添加
import pandas as pd
def search():
df = pd.read_csv('./assets/test.csv')
# 此时已经将索引切换到了’name‘这一栏上
df.set_index('name', inplace=True)
# # 单条件查询,loc的参数用[]包裹,[索引的值,查询的值]
# # 查询的是单个条件返回一个值
# print(df.loc['Bob', 'age'])
# # 多条件查询,loc的参数用[]包裹,[[索引的值list],[查询的值list]]
# # 查询的是多个条件返回DataFrame
# print(df.loc['john', ['age', 'gender']])
# print(df.loc[['john', 'Bob'], ['age', 'gender']])
# # 分区查询,loc[索引值的参数区间(不能重复), 查询值的区间(不能重复)]
# print(df.loc['john':'mike', 'age':'gender'])
# # 条件查询
# print((df['age'] > 13) & (df['age'] < 24))
# 函数查询
print(df.loc[lambda df: (df['age'] > 13) & (df['age'] < 24), :])
def add():
df = pd.read_csv('./assets/test.csv')
# # 增加一列 after10year 为10年后的年龄
# df.loc[:, 'after10year'] = df['age'] + 10
# print(df)
# 通过apply增加列,传入一个方法。axis=1指列。=0指行。
df.loc[:, 'isAdult'] = df.apply(lambda x: '成年' if x['age'] >= 18 else '未成年', axis=1)
print(df['isAdult'].value_counts()) # 查看计数
print(df)
if __name__ == '__main__':
# search()
add()
统计和去重
import pandas as pd
def describe():
df = pd.read_csv('./assets/test.csv')
print(df.describe()) # 统计csv的平均值、数量、标准差、最大最小值等
print(df['age'].max())
def unique():
df = pd.read_csv('./assets/test.csv')
print(df['age'].unique()) # 去重,可以看到一列中有哪些值
if __name__ == '__main__':
unique()
数据清洗
import pandas as pd
def clean():
df = pd.read_csv('./assets/test2.csv')
# print(df)
# print(df.isnull()) # 检测空值
# axis指定删除'columns' 或者 'index', how指定删除方式,'all'全部为空才删除,'any'只要有空就删除, inplace指代是否替换df
df.dropna(axis='columns', how='all', inplace=True)
print(df)
# 空置填充,如此处,age为空的地方用18填充
# df.fillna({'age': 18}, inplace=True)
# print(df)
# 设置method='ffill',空置处用上一个有的值填充
df.fillna(method='ffill', inplace=True)
print(df)
# 生成清洗后的csv文件,设置index=False可以去除默认的索引
df.to_csv('./assets/test2_clean.csv', index=False)
if __name__ == '__main__':
clean()
数据排序
import pandas as pd
def pd_sort():
df = pd.read_csv('./assets/test.csv')
print(df)
# 根据age降序排序
df.sort_values(by='age', inplace=True, ascending=False)
print(df)
df.sort_values(by=['gender', 'age'], inplace=True, ascending=[True, False])
print(df)
if __name__ == '__main__':
pd_sort()
文件遍历
import pandas as pd
import time
def traverse(path):
df = pd.read_csv(path)
for index, row in df.iterrows():
# print(index)
# print(row['name'])
# print(row['age'])
# print(row['gender'])
pass
def traverse2(path):
df = pd.read_csv(path)
for row in df.itertuples():
# print(row.name)
# print(row.age)
# print(row.gender)
# # 或者
# print(getattr(row, 'name'))
# print(getattr(row, 'age'))
# print(getattr(row, 'gender'))
pass
# 通过列遍历
def traverse3(path):
df = pd.read_csv(path)
for item in df.iteritems():
# print(item)
pass
# 此方法遍历速度最快,推荐使用
def traverse4(path):
df = pd.read_csv(path)
for index in df.index:
# print(df.loc[index]['name'])
pass
def use_time(func, path):
times = time.time()
for i in range(50):
func(path)
res = (time.time() - times) / 50
print(res)
if __name__ == '__main__':
file_path = './assets/mfs.csv'
use_time(traverse2, file_path)
use_time(traverse3, file_path)
use_time(traverse4, file_path)
未完待续…