Pandas数据读取csv、excel、mysql

一：pandas读取纯文本文件

1.1读取纯文本文件

• 读取csv，使用默认的标题行，逗号分隔符

import pandas as pd

fpath = r"D:\node\nd\Pandas_study\pandas_test\ratings.csv"

#使用pd.read_csv读取数据
ratings = pd.read_csv(fpath)
print(ratings)

#查看前几行数据
a = ratings.head()
# print(a)

#查看数据的形状，返回（行数，列数）
b = ratings.shape
print("行数是：",b)  
#(100836, 4)

#查看列名列表
c = ratings.columns
print("列名列表：",c) 
#Index(['userId', 'movieId', 'rating', 'timestamp'], dtype='object')

#查看索引列
d = ratings.index
print("索引列：",d) 
#RangeIndex(start=0, stop=100836, step=1)

#查看每列的数据类型
e = ratings.dtypes
print("每列的数据类型是:",e)
#userId         int64
#movieId        int64
#rating       float64
#timestamp      int64
#dtype: object

• 读取txt文件

import pandas as pd

fpath = r"D:\node\nd\Pandas_study\pandas_test\access_pvuv.txt"

#使用pd.read_csv读取数据
#sep为读取txt文件时，内容的切割符
#header说明txt文件没有title行
#names自定义title行
ratings = pd.read_csv(fpath,sep = "\t",
                      header = None,names=["pdate","pv","uv"])
print("读取txt结果是：",ratings)
#pdate   pv   uv
#0  2019-09-10  139   92
#1  2019-09-09  185  153
#2  2019-09-08  123   59
#3  2019-09-07   65   40
#4  2019-09-06  157   98
#5  2019-09-05  205  151
#6  2019-09-04  196  167
#7  2019-09-03  216  176
#8  2019-09-02  227  148
#9  2019-09-01  105   61

1.2读取xlsx格式excel文件

import pandas as pd

fpath = r"D:\node\nd\Pandas_study\pandas_test\access_pvuv.xlsx"
rating = pd.read_excel(fpath)
print(rating)
#日期   PV   UV
#0  2019-09-10  139   92
#1  2019-09-09  185  153
#2  2019-09-08  123   59
#3  2019-09-07   65   40
#4  2019-09-06  157   98
#5  2019-09-05  205  151
#6  2019-09-04  196  167

1.3pandas读取mysql数据表

import pymysql
import pandas as pd
conn = pymysql.connect(
    host = "127.0.0.1",user="root",password = "",
    database = "ceshi",charset="utf8"
)

mysql_page = pd.read_sql("select * from class",conn)
print(mysql_page)
#id name
#0   1   1班
#1   2   2班