Excel
import xlrd,xlwt
import numpy as np
path='E:/python/wangyiPython/the fifth week/data/'
wb=xlwt.Workbook()
wb
wb.add_sheet("first_sheet",cell_overwrite_ok=True)#添加一个工作表,命名为"first_sheet"
wb.get_active_sheet()#活动工作表的索引
ws_1=wb.get_sheet(0)
ws_1
ws_2=wb.add_sheet('second_sheet')
data=np.arange(1,65,dtype=float).reshape((8,8))
data
data.dtype
ws_1.write(0,0,100) #在工作表ws_1中的第一个单元格(第一行第一列)写入100这个值
for c in range(data.shape[0]):#data.shape[0]=8 指8行
for r in range(data.shape[1]):
ws_1.write(r,c,data[c,r])
ws_2.write(r,c,data[r,c])
wb.save(path+'workbook.xls') #保存文件,并命名为workbook
book=xlrd.open_workbook(path+'workbook.xls')
sheet_1=book.sheet_by_name('first_sheet')#'first_sheet'
sheet_2=book.sheet_by_index(1)#'second_sheet'
book.sheet_names(),sheet_1
sheet_2.name
sheet_1.ncols,sheet_1.nrows
c1=sheet_1.cell(0,0)
c1.value
c1.ctype
sheet_2.row(3),sheet_2.col(3)
sheet_1.col_values(3,start_rowx=3,end_rowx=7) #第三行到第七行的第三列数据
sheet_1.row_values(3,start_colx=2)
for c in range(sheet_1.ncols):
for r in range(sheet_1.nrows):
print('%i'%sheet_1.cell(r,c).value)
print
import pandas as pd
xls_file=pd.ExcelFile(path+'workbook.xls') #pandas读取excel文件
table=xls_file.parse('first_sheet',header=None,names=['one','two','three','four','five','six','seven','eight'])
table
JSON数据
obj='''
{"name":"wes",
"places_live":["United States", "Spain", "Germany"],
"pet":"null",
"siblings":[{"name": "Scott", "age": 25, "pet": "Zuko"},{"name": "Katie", "age": 33, "pet": "Cisco"}]
}'''
#JSON数据
import json
from pandas import DataFrame
result=json.loads(obj) #str转化成dict
result
asjson=json.dumps(result) #dict转化成str
siblings=DataFrame(result['siblings'],columns=['name','age'])
siblings
二进制数据
#二进制格式存储数据
frame=pd.read_csv('E:/python/wangyiPython/the fifth week/data/ex1.csv')
frame
#python 内置的pickle序列化
frame.to_pickle('E:/python/wangyiPython/the fifth week/data/frame_pickle')
pd.read_pickle('E:/python/wangyiPython/the fifth week/data/frame_pickle')
数据库
#使用数据库
import sqlite3
query='''
CREATE TABLE test
(a VARCHAR(20), b VARCHAR(20), c REAL, d INTEGER)
''' ##创建一个表,表名,表属性
con=sqlite3.connect(':memory:') #连接到数据库memory
con.execute(query) #execute方法执行查询,创建一个表
con.commit()#使查询生效
data = [('Atlanta', 'Georgia', 1.25, 6),
('Tallahassee', 'Florida', 2.6, 3),
('Sacramento', 'California', 1.7, 5)]
stmt='INSERT INTO test VALUES(?,?,?,?)'
con.executemany(stmt,data) #向表中批量写入数据
con.commit() #使写入生效
cursor=con.execute('select * from test') #查下数据
rows=cursor.fetchall() #接收全部的返回结果行的每行数据
rows
import pandas.io.sql as sql
sql.read_sql('select * from test',con)