[508]pandas之read_table、read_csv、read_excel、read_sql

周小董

已于 2023-09-20 15:26:32 修改

阅读量3.6k

点赞数 3

CC 4.0 BY-SA版权

分类专栏：数据分析文章标签： pandas excel sql

于 2019-01-30 12:35:58 首次发布

本文链接：https://blog.youkuaiyun.com/xc_zhou/article/details/86701551

数据分析专栏收录该内容

44 篇文章

订阅专栏

本文详细介绍如何使用Pandas库进行各种数据格式的读取与写入操作，包括CSV、Excel、SQL数据库及MongoDB等，涵盖数据处理、筛选、排序及合并技巧。

文章目录

read_table

Pandas读取txt表格

1.原始文本格式：空格分隔的txt,例如

2016-03-22 00:06:24.4463094 中文测试字符 
2016-03-22 00:06:32.4565680 需要编辑encoding 
2016-03-22 00:06:32.6835965 abc 
2016-03-22 00:06:32.8041945 egb

2.pandas 读取数据

import pandas as pd

data = pd.read_table('Z:/test.txt',header=None,encoding='gb2312',delim_whitespace=True,index_col=0)
#header=None:没有每列的column name，可以自己设定
#encoding='gb2312':其他编码中文显示错误
#delim_whitespace=True:用空格来分隔每行的数据
#index_col=0:设置第1列数据作为index

读取csv文件

test.csv

index,a_name,b_name
0,1,3
1,2,3
2,3,4
3,5

读csv文件

# -*- coding:utf-8 -*-
import pandas as pd

df = pd.read_csv('test.csv')
print(df)

输出

   index  a_name  b_name
0      0       1     3.0
1      1       2     3.0
2      2       3     4.0
3      3       5     NaN

读取excel

读取excel主要通过read_excel函数实现，除了pandas还需要安装第三方库xlrd。

pd.read_excel(io, sheetname=0, header=0, skiprows=None, skip_footer=0, index_col=None, 
    names=None,parse_cols=None, parse_dates=False, date_parser=None, na_values=None, 
    thousands=None, convert_float=True, has_index_names=None, converters=None, dtype=None, 
    true_values=None, false_values=None, engine=None, squeeze=False, **kwds)

该函数主要的参数为io、sheetname、header、names、encoding。

io:excel文件，可以是文件路径、文件网址、file-like对象、xlrd workbook;
sheet_name:返回指定的sheet，参数可以是字符串（sheet名）、整型（sheet索引）、list（元素为字符串和整型，返回字典{‘key’:‘sheet’}）、None（返回字典，全部sheet）;
header:指定数据表的表头，参数可以是int、list of ints，即为索引行数为表头;
names:返回指定name的列，参数为array-like对象。
encoding:关键字参数，指定以何种编码读取。

该函数返回pandas中的DataFrame或dict of DataFrame对象，利用DataFrame的相关操作即可读取相应的数据。

数据准备，有一个Excel文件：excel_output.xlsx有两个表单，表单名分别为：Python 以及student

#这个会直接默认读取到这个Excel的第一个表单
df=pd.read_excel('excel_output.xlsx')

#可以通过sheet_name来指定读取的表单
df = pd.read_excel('excel_output.xls',sheet_name='student')
#可以通过表单名同时指定多个
df=pd.read_excel('excel_output.xlsx',sheet_name=['python','student'])
#通过表单索引来指定要访问的表单，0表示第一个表单
df=pd.read_excel('excel_output.xlsx',sheet_name=0)
#也可以采用表单名和索引的双重方式来定位表单
df=pd.read_excel('excel_output.xlsx',sheet_name=['python',1])
#可以通过索引 同时指定多个
df=pd.read_excel('excel_output.xlsx',sheet_name=[1,2])

print(df.head())  #看看读取的数据，默认为前5行
data=df.values#获取所有的数据，注意这里不能用head()方法哦~
print("获取到所有的值:\n{0}".format(data))#格式化输出

# 获取行号
df.index.values
# 修改index
df.index = df.index + 1
# 获取列名
df.columns.values
# 获取指定列的值：
df['data'].values

del df['_id'] # 删除_id字段
df.sort_values(by='E') # 以某一列排序

# 选择需要显示的字段
data = df[['a', 'b', 'c','d','e']] # a,b,c 为字段列名 
# 读取指定的单行，0表示第一行，这里读取数据并不包含表头，要注意哦！
data=df.ix[0].values
#读取指定多行的话，就要在ix[]里面嵌套列表指定行数
data=df.ix[[1,2]].values
#读取指定的行列：读取第一行第二列的值，这里不需要嵌套列表
data=df.ix[1,2]
#读取指定的多行多列值：读取第一行第二行的title以及data列的值，这里需要嵌套列表
data=df.ix[[1,2],['title','data']].values
#获取所有行的指定列：读所有行的title以及data列的值，这里需要嵌套列表
data=df.ix[:,['title','data']].values

df = pd.read_excel('excel_output.xls',sheet_name=None)
print(df['2']) #指定sheet
xls_file=pd.ExcelFile('excel_output.xls')
print(xls_file.sheet_names)#显示出读入excel文件中的表名字

sheet1=xls_file.parse('2')
sheet2=xls_file.parse(0)
print('sheet1:',sheet1)
print('sheet2:',sheet2)

df=pd.read_excel('lemon.xlsx')
test_data=[]
for i in df.index.values:#获取行号的索引，并对其进行遍历：
    #根据i来获取每一行指定的数据 并利用to_dict转成字典
    row_data=df.ix[i,['case_id','module','title','http_method','url','data','expected']].to_dict()
    test_data.append(row_data)
print("最终获取到的数据是：{0}".format(test_data))

写入excel

excel的写入函数为pd.DataFrame.to_excel()；必须是DataFrame写入excel, 即Write DataFrame to an excel sheet。

pd.to_excel(self, excel_writer, sheet_name='Sheet1', na_rep='', float_format=None,columns=None, 
	header=True, index=True, index_label=None,startrow=0, startcol=0, engine=None, 
	merge_cells=True, encoding=None,inf_rep='inf', verbose=True, freeze_panes=None)

excel_writer:写入的目标excel文件，可以是文件路径、ExcelWriter对象;
sheet_name:表名
na_rep ：缺失值填充
如果na_rep设置为bool值，则写入excel时改为0和1；也可以写入字符串或数字
na_rep=True --> 1
na_rep=False --> 0
na_rep=3 --> 3
na_rep=‘a’ --> ‘a’
columns ：选择输出的的列存入
index：默认为True，显示index，当index=False 则不显示行索引（名字）
header :指定作为列名的行，默认0，即取第一行，数据为列名行以下的数据；若数据不含列名，则设定 header = None
index_label：设置索引列的列名
encoding:指定写入编码，string类型。

写入excel主要通过pandas构造DataFrame，调用to_excel方法实现。

'''一个excel写入1个sheet'''
df.to_excel('excel_output.xls',sheet_name='2',na_rep=True,
	columns=['index','b_name'],index=False)

'''一个excel写入多个sheet'''
writer = pd.ExcelWriter('output.xlsx')
# df1 = pd.DataFrame(data={'col1':[1,1], 'col2':[2,2]})
df1 = pd.DataFrame(data=[{'col1':1, 'col2':2},{'col1':3, 'col2':4}])
df1.to_excel(writer,sheet_name='Sheet1')
df1.to_excel(writer,sheet_name='2')
writer.save()
writer.close()
#-------------------------------------------------------
df1 = pd.DataFrame({'Data1': [1, 2, 3, 4, 5, 6, 7]})
df2 = pd.DataFrame({'Data2': [8, 9, 10, 11, 12, 13]})
df3 = pd.DataFrame({'Data3': [14, 15, 16, 17, 18]})
with pd.ExcelWriter('output2.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Data1', startcol=0, index=False)
    df2.to_excel(writer, sheet_name='Data1', startcol=1, index=False)
    df3.to_excel(writer, sheet_name='Data3', index=False)

将一个EXCEL文件分割成多个文件

有时因为一个EXCEL文件的数据量很大，需要分割成多个文件进行处理。这时用Pandas的切片操作即可达到要求。

import pandas as pd
data = pd.read_excel('./public opinion.xlsx', sheetname='public opinion')

row_num, column_num = data.shape    #数据共有多少行，多少列
print('the sample number is %s and the column number is %s' % (row_num, column_num))
#这里我们的数据共有210000行，假设要让每个文件1万行数据，即分成21个文件
for i in range(0, 21):
    save_data = data.iloc[i*10000+1:(i+1)*10000+1, :] #每隔1万循环一次
    file_name= './public opinion' + str(i) + '.xlsx'
    save_data.to_excel(file_name, sheet_name = 'public opinion', index = False)

分割前的文件是这样

分割后的文件就有这么些了

将多个EXCEL文件合并成一个文件

分割的文件处理完了我们可能又要把它们合并在一起。这时可以用pandas的concat功能来实现。

import pandas as pd

data0 = pd.read_excel('./public opinion0.xlsx', sheetname='public opinion')
data1 = pd.read_excel('./public opinion1.xlsx', sheetname='public opinion')
data = pd.concat([data0, data1])

for i in range(2, 21):
    file_name = './public opinion' + str(i) + '.xlsx'
    data2 = pd.read_excel(file_name)
    data = pd.concat([data, data2])
data.to_excel('./public opinion-concat.xlsx', index = False)

这样就把所有的文件都合并在了一起。

read_sql

pandas.read_sql()
pandas.read_sql_query()
pandas.read_sql_table()

read_sql是综合了read_sql_table和read_sql_query的，所以一般用read_sql就好了

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

各参数意义：

sql：sql命令字符串
con：连接sql数据库的engine，一般可以用sqlalchemy或者pymysql之类的包建立；
index_col：选择某一列作为index；
coerce_float：将数字形式的字符串直接以float型读入；
parse_dates：将某一列日期型字符串转换为datetime型数据，与pd.to_datetime功能类似。可以直接提供需要转换的列名以默认的日期形式转换，也可以用字典的格式提供列名和转换的日期格式，比如{column_name: format string}（format string：“%Y:%m:%H:%M:%S”）；
columns：要选取的列，一般没啥用，因为在sql命令里面一般就指定要选择的列了；
chunksize：如果提供了一个整数值，那么就会返回一个generator，每次输出的行数就是提供的值的大小。

设置参数con
1、用sqlalchemy构建数据库链接

import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine
 
connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'
engine = create_engine(connect_info)
 
sql_cmd = 'SELECT * FROM table'
df = pd.read_sql(sql=sql_cmd, con=engine)
engine.close()  #使用完后记得关掉

2、用DBAPI构建数据库链接

import pandas as pd
import pymysql
 
conn = pymysql.connect(host=localhost, user=username, password=password, database=dbname, charset='utf8', use_unicode=True)
 
sql_cmd = 'SELECT * FROM table'
df = pd.read_sql(sql=sql_cmd, con=conn)
conn.close()  #使用完后记得关掉

在Pandas中直接加载MongoDB的数据

import pymongo
import pandas as pd

client = pymongo.MongoClient('localhost',27017)
db  = client['Lottery']
collection = db['Pk10']

data = pd.DataFrame(list(collection .find()))

#删除mongodb中的_id字段
del data['_id']

#选择需要显示的字段
data = data[['date','num1','num10']]
print(data)

参考：https://www.jb51.net/article/139147.htm
https://blog.youkuaiyun.com/brucewong0516/article/details/79097909
https://zhuanlan.zhihu.com/p/36031795
https://www.cnblogs.com/snaildev/archive/2018/04/22/8907952.html
https://www.cnblogs.com/liulinghua90/p/9935642.html
https://blog.youkuaiyun.com/Ghjkku/article/details/131364726