Pandas数据处理神器！轻松加载CSV、Excel、MySQL，数据保存也无忧！

置顶腾飞开源

于 2024-07-14 10:19:35 发布

阅读量872

点赞数 23

分类专栏： Pandas 文章标签： pandas 数据分析科学计算人工智能学习笔记学习数据处理

本文链接：https://blog.csdn.net/luorongxi123/article/details/140407523

版权

Pandas 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

在这里插入图片描述

1. 导包

# 导包
import numpy as np
import pandas as pd

2. CSV数据

data = np.random.randint(0,50,size=(10,5))
df = pd.DataFrame(data=data,columns=["Python","C++","Java","NumPy","Pandas"])
df

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

2.1 df.to_csv：保存到csv

# sep：分隔符，默认是逗号
# header：是否保存列索引
# index：是否保存行索引
df.to_csv("08_Pandas数据加载.csv",sep=",",header=True,index=True)

2.2 df.read_csv：加载csv数据

pd.read_csv("08_Pandas数据加载.csv",sep=",",header=[0],index_col=0)

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

# 不获取列：header=None，第一行的数据会作为内容显示
pd.read_csv("08_Pandas数据加载.csv",sep=",",header=None,index_col=0)

	1	2	3	4	5
0
NaN	Python	C++	Java	NumPy	Pandas
0.0	48	49	30	16	16
1.0	7	46	25	38	41
2.0	17	24	45	42	20
3.0	15	28	1	23	0
4.0	13	10	10	14	48
5.0	0	15	7	10	40
6.0	2	39	9	33	27
7.0	11	9	25	45	3
8.0	47	29	24	14	42
9.0	12	47	25	40	41

2.3 pd.read_table

# read_table：默认分隔符sep='\t'
pd.read_table("08_Pandas数据加载.csv",sep=",",index_col=0)

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

3. excel数据

data = np.random.randint(0,50,size=(10,5))
df = pd.DataFrame(data=data,columns=["Python","C++","Java","NumPy","Pandas"])
df

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

3.1 df.to_excel()：保存到excel文件

# sheet_name：工作表名称
# header：是否保存列索引
# index：是否保存行索引
df.to_excel("08_Pandas数据加载.xlsx",sheet_name="课程成绩",header=True,index=True)

3.2 pd.read_excel：读取excel

pd.read_excel("08_Pandas数据加载.xlsx",sheet_name="课程成绩",header=[0],index_col=0)

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

pd.read_excel("08_Pandas数据加载.xlsx",sheet_name="课程成绩",header=[0,1],index_col=0)

	Python	C++	Java	NumPy	Pandas
	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

# sheet_name=0：读取第1个工作表
pd.read_excel("08_Pandas数据加载.xlsx",sheet_name=0,header=0,index_col=0)

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

# names：设置列名/替代原有的列名
pd.read_excel("08_Pandas数据加载.xlsx",header=0,index_col=0,names=list("ABCDE"))

	A	B	C	D	E
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

4. MySQL数据

4.1 需要安装pymysql

pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple

Defaulting to user installation because normal site-packages is not writeable
Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting pymysql
  Downloading https://pypi.tuna.tsinghua.edu.cn/packages/e5/30/20467e39523d0cfc2b6227902d3687a16364307260c75e6a1cb4422b0c62/PyMySQL-1.1.0-py3-none-any.whl (44 kB)
     ---------------------------------------- 0.0/44.8 kB ? eta -:--:--
     --------- ------------------------------ 10.2/44.8 kB ? eta -:--:--
     --------- ------------------------------ 10.2/44.8 kB ? eta -:--:--
     --------- ------------------------------ 10.2/44.8 kB ? eta -:--:--
     -------------------------------------- 44.8/44.8 kB 200.7 kB/s eta 0:00:00
Installing collected packages: pymysql
Successfully installed pymysql-1.1.0
Note: you may need to restart the kernel to use updated packages.

4.2 需要安装sqlalchemy

pip install sqlalchemy -i

https://pypi.tuna.tsinghua.edu.cn/simple

sqlalchemy是Python语言的数据库引擎库

pip install sqlalchemy -i https://pypi.tuna.tsinghua.edu.cn/simple

Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Requirement already satisfied: sqlalchemy in d:\anaconda3\lib\site-packages (1.4.39)
Requirement already satisfied: greenlet!=0.4.17 in d:\anaconda3\lib\site-packages (from sqlalchemy) (2.0.1)
Note: you may need to restart the kernel to use updated packages.

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --target=d:\anaconda3\lib\site-packages sqlalchemy

Note: you may need to restart the kernel to use updated packages.Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting sqlalchemy
  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/22/80/43ddb1ddeafdcbc3073c0e7a7d45b17678eeac4830c7e91bd6556527f311/SQLAlchemy-2.0.25-cp311-cp311-win_amd64.whl (2.1 MB)
Collecting typing-extensions>=4.6.0 (from sqlalchemy)
  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/b7/f4/6a90020cd2d93349b442bfcb657d0dc91eee65491600b2cb1d388bc98e6b/typing_extensions-4.9.0-py3-none-any.whl (32 kB)
Collecting greenlet!=0.4.17 (from sqlalchemy)
  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/47/79/26d54d7d700ef65b689fc2665a40846d13e834da0486674a8d4f0f371a47/greenlet-3.0.3-cp311-cp311-win_amd64.whl (292 kB)
Installing collected packages: typing-extensions, greenlet, sqlalchemy
Successfully installed greenlet-3.0.3 sqlalchemy-2.0.25 typing-extensions-4.9.0



WARNING: Target directory d:\anaconda3\lib\site-packages\greenlet already exists. Specify --upgrade to force replacement.
WARNING: Target directory d:\anaconda3\lib\site-packages\sqlalchemy already exists. Specify --upgrade to force replacement.
WARNING: Target directory d:\anaconda3\lib\site-packages\typing_extensions.py already exists. Specify --upgrade to force replacement.
WARNING: Target directory d:\anaconda3\lib\site-packages\__pycache__ already exists. Specify --upgrade to force replacement.

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --target=d:\anaconda3\lib\site-packages sqlalchemy --upgrade

Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple
Collecting sqlalchemy
  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/22/80/43ddb1ddeafdcbc3073c0e7a7d45b17678eeac4830c7e91bd6556527f311/SQLAlchemy-2.0.25-cp311-cp311-win_amd64.whl (2.1 MB)
Collecting typing-extensions>=4.6.0 (from sqlalchemy)
  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/b7/f4/6a90020cd2d93349b442bfcb657d0dc91eee65491600b2cb1d388bc98e6b/typing_extensions-4.9.0-py3-none-any.whl (32 kB)
Collecting greenlet!=0.4.17 (from sqlalchemy)
  Using cached https://pypi.tuna.tsinghua.edu.cn/packages/47/79/26d54d7d700ef65b689fc2665a40846d13e834da0486674a8d4f0f371a47/greenlet-3.0.3-cp311-cp311-win_amd64.whl (292 kB)
Installing collected packages: typing-extensions, greenlet, sqlalchemy
Successfully installed greenlet-3.0.3 sqlalchemy-2.0.25 typing-extensions-4.9.0
Note: you may need to restart the kernel to use updated packages.

from sqlalchemy import create_engine

# 创建数据
data = np.random.randint(0,150,size=(150,3))
df = pd.DataFrame(data=data,columns=["Python","Pandas","PyTorch"])
# 查看前5条数据
df.head()

	Python	Pandas	PyTorch
0	83	118	84
1	78	141	102
2	11	4	47
3	104	0	88
4	140	64	33

4.3 先连接MySQL

# mysql+pymysql：数据库类型+驱动
# root:123456：数据库用户名和密码
# localhost:3306：数据库地址和端口
# pandas：数据库名
conn = create_engine("mysql+pymysql://root:123456@localhost:3306/pandas")

4.4 df.to_sql：保存到MySQL

df.to_sql(
    name="score",   # 数据库中的名字
    con = conn,   # 数据库连接对象
    index=False,   # 是否保存行索引
    if_exists="append"   # 如果表存在，则追加数据
)

4.5pd.read_sql：从MySQL中加载数据

pd.read_sql(
    sql = "select * from score",   # sql语句
    con = conn   # 数据库连接对象
)

	Python	Pandas	PyTorch
0	83	118	84
1	78	141	102
2	11	4	47
3	104	0	88
4	140	64	33
...	...	...	...
145	100	133	87
146	25	53	33
147	57	8	46
148	97	134	108
149	46	76	138

150 rows × 3 columns

pd.read_sql(
    sql = "select * from score",   # sql语句
    con = conn,   # 数据库连接对象
    index_col = "Python"   # 指定行索引的列名
)

	Pandas	PyTorch
Python
83	118	84
78	141	102
11	4	47
104	0	88
140	64	33
...	...	...
100	133	87
25	53	33
57	8	46
97	134	108
46	76	138

150 rows × 2 columns

腾飞开源

关注

23
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
0
评论
Pandas数据处理神器！轻松加载CSV、Excel、MySQL，数据保存也无忧！

在数据驱动的时代，Pandas凭借其卓越的数据处理能力成为了数据分析师和科学家们的首选工具。本文深入探讨了Pandas如何作为数据处理的大杀器，无缝对接CSV、Excel、MySQL等多种数据源，实现了数据的轻松加载与高效保存。通过简洁明了的API，Pandas不仅让复杂的数据读取和存储任务变得轻松无比，还大大提升了数据处理的速度和效率。无论是面对海量数据还是精细的数据操作需求，Pandas都能凭借其强大的功能集和灵活性，为用户提供无忧的数据处理体验。
复制链接

扫一扫

专栏目录

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	A	B	C	D	E
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	Pandas	PyTorch
0	83	118	84
1	78	141	102
2	11	4	47
3	104	0	88
4	140	64	33
...	...	...	...
145	100	133	87
146	25	53	33
147	57	8	46
148	97	134	108
149	46	76	138

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	A	B	C	D	E
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	Pandas	PyTorch
0	83	118	84
1	78	141	102
2	11	4	47
3	104	0	88
4	140	64	33
...	...	...	...
145	100	133	87
146	25	53	33
147	57	8	46
148	97	134	108
149	46	76	138

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	48	49	30	16	16
1	7	46	25	38	41
2	17	24	45	42	20
3	15	28	1	23	0
4	13	10	10	14	48
5	0	15	7	10	40
6	2	39	9	33	27
7	11	9	25	45	3
8	47	29	24	14	42
9	12	47	25	40	41

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	C++	Java	NumPy	Pandas
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	A	B	C	D	E
0	17	37	47	39	6
1	6	12	24	17	1
2	37	33	45	34	23
3	41	16	2	1	21
4	40	18	22	36	48
5	48	17	14	11	0
6	17	39	0	3	15
7	23	0	40	23	31
8	20	23	27	8	8
9	1	41	21	6	44

	Python	Pandas	PyTorch
0	83	118	84
1	78	141	102
2	11	4	47
3	104	0	88
4	140	64	33
...	...	...	...
145	100	133	87
146	25	53	33
147	57	8	46
148	97	134	108
149	46	76	138