#%%
import pandas as pd
import numpy as np
#%% md
# 1 CSV文件读取
df = pd.read_csv('./student.csv',encoding='gbk')
df
#%%
# 自定义索引(index_col)
df_file = pd.read_csv('./student.csv',encoding='gbk',index_col=['Name'])
df_file
#%%
# 查看每一列的dtype
df_file.dtypes
#%%
# 更改数据类型
df_dat = pd.read_csv('./student.csv',encoding='gbk',
dtype={'Age':np.float64})
df_dat
#%%
# 更改文件名:用header 控制 代表从那一行截取
df_dat1 = pd.read_csv('./student.csv',encoding='gbk',
names=['q','w','e'],header=1)
df_dat1
#%%
# 跳过指定的行数 默认从第一行开始从1开始计数
df_dat2 = pd.read_csv('./student.csv',encoding='gbk',
skiprows=0)
df_dat2
#%% md
CSV 文件写入
#%%
data = {'Name':["SMITH","ALLEN"],'ID':[101,102],
"Language":['Python','Javascript']}
ifo = pd.DataFrame(data)
ifo
#%%
csv_data = ifo.to_csv('./student.csv')
#%% md
EXCEL 文件操作
~~~python
pd.read_excel(io, sheet_name=0, header=0, names=None, index_col=None,
usecols=None, squeeze=False,dtype=None, engine=None,
converters=None, true_values=None, false_values=None,
skiprows=None, nrows=None, na_values=None, parse_dates=False,
date_parser=None, thousands=None, comment=None, skipfooter=0,
convert_float=True, **kwds)
~~~
| 参数名称 | 说明 |
| :--------: | ------------------------------------------------------------ |
| io | 表示 Excel 文件的存储路径。 |
| sheet_name | 要读取的工作表名称。 |
| header | 指定作为列名的行，默认0，即取第一行的值为列名；若数据不包含列名，则设定 header = None。若将其设置 为 header=2，则表示将前两行作为多重索引。 |
| names | 一般适用于Excel缺少列名，或者需要重新定义列名的情况；names的长度必须等于Excel表格列的长度，否则会报错。 |
| index_col | 用做行索引的列，可以是工作表的列名称，如 index_col = '列名'，也可以是整数或者列表。 |
| usecols | int或list类型，默认为None，表示需要读取所有列。 |
| squeeze | boolean，默认为False，如果解析的数据只包含一列，则返回一个Series。 |
| converters | 规定每一列的数据类型。 |
| skiprows | 接受一个列表，表示跳过指定行数的数据，从头部第一行开始。 |
| nrows | 需要读取的行数。 |
| skipfooter | 接受一个列表，省略指定行数的数据，从尾部最后一行开始。
#%%
df_hh = pd.read_excel(r'F:\pythonProject\student.xlsx',sheet_name='Sheet1')
df_hh
#%%
# 写入数据
info_data = pd.DataFrame({'name':[i for i in list('ABCD')],
'rank':[1,2,3,4],
'luaguage':['chinese','japanese','english','ruial'],
'url':['TX',"ALI","GT","SX"]})
info_data
#%%
pf = pd.ExcelWriter('web.xlsx')
info_data.to_excel(pf)
pf._save()
print("OK")
#%% md
SQL操作:
#%%
import pymysql
#%%
coon = pymysql.connect(
host="127.0.0.1",
port=3307,
user="gaohao",
password="123456",
db="yuqing"
)
#%%
sql = 'select * from emp'
print(pd.read_sql(sql,con=coon))
#%%
sql2 = 'select * from emp where sal>2500'
print(pd.read_sql(sql2,con=coon))
#%%
sql3 ='select * from yuqing.SC'
#%%
pd.read_sql(sql3,con=coon)
#%%
'''
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
查询"李"姓老师的数量查询学过"张三"老师授课的同学的信息
查询没学过"张三"老师授课的同学的信息
查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
'''
sql_1 = "select sid f,score w from yuqing.SC where cid = 1"
sql_1_1 = "select sid f2,score w2 from yuqing.SC where cid = 2"
sql_1_0 = 'select f,w from (select sid f,score w from yuqing.SC where cid = 1) s,(select sid f2,score w2 from yuqing.SC where cid = 2) k where f=f2 and w>w2'
sql_11_11 = '''
SELECT * from Student gg,(select f,w from (select sid f,score w from yuqing.SC where cid = 1) s,
(select sid f2,score w2 from yuqing.SC where cid = 2) k where f=f2 and w>w2) jj where f=gg.sid
'''
pd.read_sql(sql_11_11,con=coon)
#%% md
###### 2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数
sql_22_11 = '''
SELECT * from Student gg,(select f2,w2 from (select sid f,score w from yuqing.SC where cid = 1) s,
(select sid f2,score w2 from yuqing.SC where cid = 2) k where f=f2 and w<w2) jj where f2=gg.sid
'''
pd.read_sql(sql_22_11,con=coon)
#%%
# 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
sql_3 = '''
select k.sid,ll.sname,avg(score) from yuqing.SC k ,Student ll where k.sid = ll.sid group by k.sid,ll.sname having avg(score) >60
'''
pd.read_sql(sql_3,con=coon)
#%%
# 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
sql_4 = '''
select ll.sid , ll.sname ,count(score), sum(score) from Student ll left join yuqing.SC k on k.sid =ll.sid
group by ll.sid ,ll.sname
'''
pd.read_sql(sql_4,con=coon)
#%%
# 查询"李"姓老师的数量查询学过"张三"老师授课的同学的信息
sql_5 = '''
SELECT r.sname,r.sid,r.ssex from Teacher q,Course w,yuqing.SC e, Student r where
q.tid=w.tid and w.cid = e.cid and e.sid =r.sid and q.tname = '张三'
'''
pd.read_sql(sql_5,con=coon)
#%%
# 查询没学过"张三"老师授课的同学的信息
sql_6 ='''
select * from Student where sid not in(
SELECT r.sid from Teacher q,Course w,yuqing.SC e, Student r where
q.tid=w.tid and w.cid = e.cid and e.sid =r.sid and q.tname = '张三'
)
'''
pd.read_sql(sql_6,con=coon)
#%%
# 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
sql_7 = '''
select * from Student gg
join (select sid from yuqing.SC where cid = 01) kk on kk.sid = gg.sid
join (select sid from yuqing.SC where cid = 02) jj on kk.sid = jj.sid
'''
pd.read_sql(sql_7,con=coon)
#%%
#%%
# 输出数据:
sql_data = '''
select * from emp
'''
data = pd.read_sql(sql_data,con=coon)
kk = pd.ExcelWriter('emp.xlsx')
data.to_excel(kk)
kk._save()
print('OK')
#%% md
# index 操作
#%%
df = pd.read_csv(r'F:\pythonProject\movie.csv',encoding='gbk')
df
#%%
# 修改行列名
# df.shape 表示几行几列
# df.head() 查看头五行
xx = df.rename(columns={'color':'颜色'},index={0:'第一列'})
xx
#%%
# 整体修改
df.columns = ['颜色', '姓名', 'num_critic_for_reviews', 'duration',
'director_facebook_likes', 'actor_3_facebook_likes', 'actor_2_name',
'actor_1_facebook_likes', 'gross', 'genres', 'actor_1_name',
'movie_title', 'num_voted_users', 'cast_total_facebook_likes',
'actor_3_name', 'facenumber_in_poster', 'plot_keywords',
'movie_imdb_link', 'num_user_for_reviews', 'language', 'country',
'content_rating', 'budget', 'title_year', 'actor_2_facebook_likes',
'imdb_score', 'aspect_ratio', 'movie_facebook_likes']
print(df.head())
#%%
lis = list(df.columns)
lis[0] = "Color"
df.columns = lis
df.head()
df
#%%
# 添加 删除 修改
#%%
sql_0010 = '''
select username,
(select score from hzlb where username= '张三' and subject = '语文') 语文
,(select score from hzlb where username= '张三' and subject = '数学') 数学
,(select score from hzlb where username= '张三' and subject = '英语') 英语
,(select score from hzlb where username= '张三' and subject = '生物') 生物
from hzlb where username= '张三'
group by username
union
select username,
(select score from hzlb where username= '码农' and subject = '语文') 语文
,(select score from hzlb where username= '码农' and subject = '数学') 数学
,(select score from hzlb where username= '码农' and subject = '英语') 英语
,(select score from hzlb where username= '码农' and subject = '生物') 生物
from hzlb where username= '码农'
group by username
union
select username,
(select score from hzlb where username= '李四' and subject = '语文') 语文
,(select score from hzlb where username= '李四' and subject = '数学') 数学
,(select score from hzlb where username= '李四' and subject = '英语') 英语
,(select score from hzlb where username= '李四' and subject = '生物') 生物
from hzlb where username= '李四'
group by username
'''
pd.read_sql(sql_0010,con=coon)
#%%
sql_0011 = '''
select * from hzlb;
'''
# select score from hzlb where subject ='语文' and username in(select username from hzlb group by username);
df_rrr = pd.read_sql(sql_0011,con=coon)
print(type(df_rrr))
#%%
df_rrr.T
#%%
# 添加 删除 插入
df_fg = pd.read_csv(r'F:\pythonProject\movie.csv',encoding='gbk')
# print(df.head()) 展示 列名
df.columns
#%%
# 指定列名进行添加列
df_fg ['new_test'] = 0
#%%
# 进行计算运算得到新的列
df_fg['money'] = df_fg['gross'] - df_fg['budget']
#%%
# 通过列与列参与计算得到新的一列 或者给列赋值
df_fg['new_test'] = df_fg['actor_1_facebook_likes']
+ df_fg['actor_2_facebook_likes']
df_fg
#%%
# 删除列
print(df_fg.drop(2,axis=0).columns)
#%%
print(df_fg.drop('color',axis='columns').columns)
# 删除指定列
#%%
# 保存到 xlsx 文件
fgh = df_fg.iloc[:3,:]
wriet = pd.ExcelWriter('./movie_new.xlsx')
fgh.to_excel(wriet)
wriet._save()
print('OK')
#%%
sql_1000 = '''
select a.username,a.语文,b.数学,c.英语,d.生物
from (select username ,score 语文 from hzlb where subject='语文' group by username,score) a
join
(select username ,score 数学 from hzlb where subject='数学' group by username,score) b
on a.username = b.username
join (select username ,score 英语 from hzlb where subject='英语' group by username,score) c
on b.username =c.username
join (select username ,score 生物 from hzlb where subject='生物' group by username,score) d
on d.username = c.username
'''
pd.read_sql(sql_1000,con=coon).T
#%%
本文详细介绍了使用Pythonpandas库处理CSV文件（包括读取、自定义索引、数据类型转换和写入），以及如何使用pandas和pymysql进行Excel文件操作和SQL查询，包括连接数据库、执行SQL语句和数据聚合。
扫一扫
830
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
