pandas snippet

1.数据库连接与数据读取

连接数据库，创建表等：

import pandas as pd
from sqlalchemy import create_engine

'''
DB_CONNECT_STRING = 'mysql+mysqldb://root:123@localhost/ooxx?charset=utf8'
engine = create_engine(DB_CONNECT_STRING, echo=True)
'''
engine = create_engine('sqlite:///../data/data.db') 
engine.execute('DROP TABLE tiezi_tb')
# 把tb列表转化为pandas的dataframe，之后把这个表直接插入数据库
df = pd.DataFrame(tb, columns=tb_head)
df.to_sql('author_tb', engine, if_exists='append') 

# 可以使用read_sql直接读取数据，也可以写个函数生成sql
df = pd.read_sql_query('SELECT count(a.tieID) fa_num, avg(a.num_reply) mean_reply, \
                        b.user_name, b.cur_score, b.is_like, b.level_id, b.user_sex, b.bawu \
                        FROM tiezi_tb a, author_tb b \
                        where a.user_id = b.user_id \
                        group by a.user_id', engine)
print df.describe()

读取zip数据

import requests
import StringIO
import zipfile

def getZIP(zipFileName):
    r = requests.get(zipFileName).content
    s = StringIO.StringIO(r)
    zf = zipfile.ZipFile(s, 'r') # Read in a list of zipped files
    return zf
url = 'http://seanlahman.com/files/database/lahman-csv_2014-02-14.zip'
zf = getZIP(url)
print zf.namelist()
salaries = pd.read_csv(zf.open('Salaries.csv'))
salaries.head()

2.选择与定位

# 如果是数字定位，选择 iloc
df.iloc[1,1]
df.iloc[0:2,:]

# 直接定位列
df[['day', 'year']]

# 筛选, 下面三个等价，推荐使用query,针对列名的操作
flights[(flights.month == 1) & (flights.day == 1)]
flights[(flights['month'] == 1) & (flights['day'] == 1)]
flights.query("month == 1 & day == 1")

# where 操作
df['logic'] = np.where(df['AAA'] > 5,'high','low')

3、新增与变更

参考pandas官网的Merge, join, and concatenate操作

# 新增两列
'''
fights 的colum：year,month,day dep_time,dep_delay,arr_time,arr_delay,carrier tailnum flight  origin  dest,air_time,distance,hour, minute
'''
flights.assign(gain=flights.arr_delay - flights.dep_delay,
               speed=flights.distance / flights.air_time * 60)

# 也可以直接如下
flights['gain'] = flights.arr_delay - flights.dep_delay
joined['Wins'] = np.log(joined['W'])

flights.rename(columns={'tailnum': 'tail_num'})  # rename 操作
# 上面是直接增加一列，如果是两个数据合并，采用merge， how = 'left', 'right', 'outer', 'inner'. Defaults to inner.merge是数据库形式的，个人推荐
'''
merge(left, right, how='inner', on=None, left_on=None, right_on=None,
      left_index=False, right_index=False, sort=True,
      suffixes=('_x', '_y'), copy=True)
'''
joined = pd.merge(data, countries, how="inner", on=['Country'])

# 使用concat
'''
concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
       keys=None, levels=None, names=None, verify_integrity=False)
'''
frames = [df1, df2, df3]  # 列不变
result = pd.concat(frames)
result = pd.concat([df1, df4], axis=1, join='inner') # 行不变
result = pd.concat([df1, df4], axis=1, join_axes=[df1.index]) # 基于index匹配

4、聚合

'''
DataFrame.groupby(by=None, axis=0, level=None, as_index=True, sort=True, group_keys=True, squeeze=False)
as_index: For aggregated output, return object with group labels as the index. Only relevant for DataFrame input. as_index=False is effectively “SQL-style” grouped output
sort : boolean, default True;Sort group keys. Get better performance by turning this off.
as_index建议加上去
'''
df = df[["a","b","c"]].groupby(["a", "b"], as_index=False).sum()