pandas 读取常见各类数据源

# encoding:utf-8
# pandas  主要的数据读取功能
#  

import pandas as pd
from pandas import DataFrame,Series
import numpy as np

df=pd.read_csv('data/ch06/ex1.csv') # 通过read_csv读取csv文件
pd.read_table('data/ch06/ex1.csv',sep=',') # 通过read_table读取csv文件
pd.read_csv('data/ch06/ex2.csv',header=None)  #使用默认的列名

#=======2.指定列名========
pd.read_csv('data/ch06/ex2.csv',names=['a','b','c','d','message'])  #使用指定列名

#=======3.指定索引，层次索引=======
pd.read_csv('data/ch06/ex2.csv',names=['a','b','c','d','message'],index_col='message') 
#将message作为行索引
pd.read_csv('data/ch06/csv_mindex.csv',index_col=['key1','key2'])   #将'key1','key2'作为层次索引

#=======4.分隔符数量不等，跳过某些行=======
pd.read_table('data/ch06/ex3.txt',seq='\s+') #针对数量不定的空白符分隔，使用“\s+” +表示1或多次,*表示0,1，多次，？表示0或1次
pd.read_csv('data/ch06/ex4.csv',skiprows=[0,2,3]) #跳过0,2,3行

#=======5.处理缺失值======
result=pd.read_csv('data/ch06/ex5.csv',na_value={'message':['foo','NA'],'something':['two']})

#=======6.批量读取======
result=pd.read_csv('data/ch06/ex6.csv',nrows=5)  #只读取前5行
#批量读取 统计数量
chunker=pd.read_csv('data/ch06/ex6.csv',chunksize=1000)
tot=Series([])
for piece in chunker:
    #print(piece['key'].value_counts())
    tot=tot.add(piece['key'].value_counts(),fill_value=0)
tot=tot.sort_values(ascending=False)
tot

#=======7.将数据写出到文本格式======
data=pd.read_csv("data/ch06/ex5.csv")
data.to_csv("data/ch06/out.csv",sep=",") #设置分隔符
import sys
data.to_csv(sys.stdout,na_rep="NULL")  #设置空值输出
data.to_csv(sys.stdout,index=False,header=False) #禁用行列标签输出
data.to_csv(sys.stdout,index=False,columns=['a','c','b']) #输出指定列
#Series 也有to_csv方法
dates=pd.date_range('1/1/2000',periods=7)  #生成时间序列索引
ts=Series(np.arange(7),index=dates)
ts.to_csv('data/ch06/tseries.csv')
Series.from_csv('data/ch06/tseries.csv',parse_dates=True) #读入时间序列数据

#=======8.json数据读取======
obj="""{
	"name": "Wes",
	"places_lived": ["United States", "Spain", "Germany"],
	"pet": null,
	"siblings": [{
		"name": "Scott",
		"age": 25,
		"pet": "Zuko"
	}, {
		"name": "Katie",
		"age": 33,
		"pet": "Cisco"
	}]
}"""
import json
result=json.loads(obj)
siblings=DataFrame(result["siblings"],columns=['name','age'])

#=======9.二进制数据读写======
frame=pd.read_csv("data/ch06/ex1.csv")
frame
frame.to_pickle('data/ch06/frame_pickle')
pd.read_pickle("data/ch06/frame_pickle")


#=======10.HDFS数据读写======
#写数据
dfs_store=pd.HDFStore("data/ch06/dfs_store2.h5")
dfs_store['obj1']=frame
dfs_store['obj1_col']=frame['a']
dfs_store.close()
#读数据
oHdfStore = pd.HDFStore("data/ch06/dfs_store2.h5", mode='r')
oHdfStore['obj1']

#=======11.数据库读写======
#1.创建数据库
import sqlite3
query="""Create Table test(a varchar(20),b varchar(20),c real,d integer);"""
con=sqlite3.connect(':memory:')
con.execute(query)
con.commit()
#2.写入数据
data=[('Atlanta','Georgia',1.25,6),('Tallnahassee','Florida',2.6,3),('Sacramento','California',1.7,5)]
stmt="insert into test values(?,?,?,?)"
con.executemany(stmt,data)
con.commit()
#3.查询数据
sql="select * from test"
cursor=con.execute(sql)
rows=cursor.fetchall()
rows
#4.查询数据到DataFrame
import pandas.io.sql as sql
df=sql.read_sql_query('select * from test',con)