Pandas操作数据库及保存csv

数据的保存

import pandas as pd
import numpy as np
from pandas import Seriescol_db = [['one',1,2,3,4,np.nan],['two',5,6,8,'world',np.nan],['three',9,10,11,12,'foo']]
data = pd.DataFrame(col_db,columns = ['somthing','a','b','c','d','message'])
data somthingabcdmessage
0one1234NaN
1two568worldNaN
2three9101112foo# 利用DataFrame的to_csv方法,csv默认为逗号分隔
data.to_csv('save.csv')
# 指定分隔符
data.to_csv('save_.csv',sep='|')
# 缺失值在输出结果中会被表示为空字符串，你可以指定
data.to_csv('save_1.csv',na_rep='NULL')
# 禁用行和列的标签，只保留数据
data.to_csv('save_2.csv',header=False,index=False)
# 输出指定的列，并以指定的顺序排列
data.to_csv('save_2.csv',index=False,columns=['a','b','c'])
# Series也有一个to_csv方法,from_csv可以直接读取csv
Series.from_csv('save_2.csv')a b
1 2
5 6
9 10
dtype: object


手工处理分隔符格式

大部分的表格型数据都能用pd.read_table进行加载，但是由于含有畸形行的文件而使read_table出毛病的情况并不少见

例如如下的格式文件：

a,	b,	c	d
1,	2,	3
1,	2,	3,	4

import csv
# 直接使用pd.read_csv会报错，这里需要引入csv模块，进行处理
f = open('save_2.csv')
# 将已打开的文件型对象传给csv.reader
reader = csv.reader(f)
for line in reader:
print(line,type(line))['a', 'b', 'c'] <class 'list'>
['1', '2', '3'] <class 'list'>
['5', '6', '8', '10'] <class 'list'># 整理这个reader
lines = list(csv.reader(open('save_2.csv')))
lines[['a', 'b', 'c'], ['1', '2', '3'], ['5', '6', '8', '10']]header, values = lines[0], lines[1:]
# 压缩为元组,再把值恢复矩阵
p = zip(header, zip(*values))
for i in p:
print(i)('a', ('1', '5'))
('b', ('2', '6'))
('c', ('3', '8'))# 字典推导式
{h:v for h,v in zip(header, zip(*values))}{'a': ('1', '5'), 'b': ('2', '6'), 'c': ('3', '8')}# 手工输出分隔符文件，可以使用csv.writer
# w模式会覆盖并重新生成
with open('save_2.csv','w') as f:
writer = csv.writer(f)
writer.writerow(('new_1','new_2'))


JSON数据

如何将JSON对象转为DataFrame或其他便于分析的数据结构


import json
obj = '''
{
"name":"wes",
"places_lived":["United Statues","Spain","Germany"],
"pet": null,
"siblings":[{"name":"Scott","age":25,"pet":"Zuko"},
{"name":"Katie","age":33,"pet":"Cisco"}]
}
'''
# 选取一部分符合dataFrame格式的
result = json.loads(obj)
pd.DataFrame(result['siblings']) agenamepet
025ScottZuko
133KatieCisco


使用数据库

# 导入内置的SQLite数据库
import sqlite3
query = '''
CREATE TABLE test
(
a VARCHAR(20),
b VARCHAR(20),
c REAL,
d INT
);
'''# 直接在内存中创建
con = sqlite3.connect(':memory:')
con.execute(query)
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[('Atlanta', 'Georgia', 1.25, 6),
('Tallahassee', 'Florida', 2.6, 3),
('Sacramento', 'California', 1.7, 5)] #取出列表名
cursor.description
(('a', None, None, None, None, None, None),
('b', None, None, None, None, None, None),
('c', None, None, None, None, None, None),
('d', None, None, None, None, None, None)) # zip(*)返回矩阵，与zip作用相反
k = zip(*cursor.description)
# for i in k:
# print(i)
# 直接使用k[0]会报错，zip对象不支持'zip' object is not subscriptable，需要借助list包装
list(k)[0]('a', 'b', 'c', 'd')pd.DataFrame(rows,columns=list(zip(*cursor.description))[0]) abcd
0AtlantaGeorgia1.256
1TallahasseeFlorida2.603
2SacramentoCalifornia1.705


pandas有一个可以简化上面过程的read_sql函数，只需要传入select语句链接对象即可

import pandas.io.sql as sql
sql.read_sql('select * from test',con) abcd
0AtlantaGeorgia1.256
1TallahasseeFlorida2.603
2SacramentoCalifornia1.705# pandas链接mysql同理
import pymysqlconn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='123456',db='taobao',charset='utf8')
sql.read_sql('select * from tblive2',conn)


存取MongoDB中的数据

import pymongo# 创建链接对象
con2 = pymongo.MongoClient('localhost',port=27017)# 链接数据库
db = con2.wechat_spider# 使用posts集合(这是我电脑里的)
find_list = db.posts.find()
# 这里由于不能直接传入迭代对象，需要借助list
pd.DataFrame(list(find_list))