pandas小结

创建dataframe

#字典创建dataframe
weather_data = {
    'day' :['1/1/2017','1/2/2017','1/3/2017','1/4/2017','1/5/2017'],
    'temperature' : [32,31,26,28,19],
    'windspeed' : [6,3,5,4,2],
    'event' : ['Rain','Snow','Sunny','Sunny','Sunny']
    }
df = pd.DataFrame(weather_data) 

#创建日期范围作为索引，然后创建列，然后构造dataframe
ts = pd.Series(np.random.randint(0,10,len(rng)),index=rng)
df2 = pd.DataFrame(ts)


#创建日期索引，表头，元素
range_a = pd.date_range(
    datetime.datetime(2019,1,2),
    datetime.datetime(2019,1,8)
)
df_a = pd.DataFrame(
    index=range_a, 
    data=np.random.randint(2,10,size=len(range_a)),
    columns=['observations_A']
)

读取文件

#读取文件跳过1 3 4行
df  = pd.read_csv('stock_data.csv',skiprows=[1,3,4])
df  = pd.read_csv('stock_data.csv',nrows=3) #读取3行
#读取文件，不读表头，将表头设置为[...]中的值
df  = pd.read_csv('stock_data.csv',header=None,names=['aa','bb','cc','dd','ee'])
#将第3列解析成日期
order_data = pd.read_csv('order_leads.csv',parse_dates=[3])


#读取CSV文件
df = pd.read_csv('weather_data.csv')
#读取execl文件
df = pd.read_excel('weather_data.xlsx',"Sheet1")
# parse_dates将day这列转换成时间类型，否则pandas默认为object类型
df = pd.read_csv('weather_data.csv',parse_dates=['day'])
#写CSV文件，不写索引和表头
df.to_csv('out3.csv',index=False,header=False)
#只保存tickers和eps列
df.to_csv('out4.csv',index=False,columns=['tickers','eps'])

#读文件时同时进行数据过滤
def convert_price(t):
    if t == 'n.a.':
        return 50
    return t
df = pd.read_excel('stock_data.xlsx',"Sheet1",
                  converters = {
                      'price':convert_price
                  })


# 从单元格的第3行第4列开始写
df2.to_excel('weather_data2.xlsx',sheet_name='weather',index=False,startrow=3,startcol=4)

#同时保存两张表
with pd.ExcelWriter('combine.xlsx') as writer:
    df.to_excel(writer,sheet_name='df1')
    df2.to_excel(writer,sheet_name='df2')

获取DataFrame子集数据

df.shape #显示行列信息
df.head()
df.tail()

df.temperature.iloc[2]  #显示temperature列第2行的信息

#获取第0到2行
df[0:3] 
#获取0行0列值
df2.iloc[0,0] 
#按照索引获取数据
df2.loc[['1/1/2016', '1/8/2016']]


#获取EST列，结果类型为Series
df.EST 
#获取温度超过42的日期,结果类型为Series
df2.EST[df2.Temperature > 42]

#获取Temperature和WindSpeedMPH两列，结果类型为dataframe
df2[['Temperature','WindSpeedMPH']] 

#获取满足条件的结果集，df3类型为dataframe
df3 = df2[df2.Temperature>38]
df3[df3.DewPoint>30]

#设置索引
df2.set_index('EST',inplace=True)
#取消索引，EST被取消后，当前dataframe中就获取不到EST了
df2.reset_index(inplace=True)

处理数据

#将空信息填充为0
df.fillna(0,inplace=True)
 
#计算windspeed列的平均值
df.windspeed.mean() 
 
#将df中的空值设置为0
newdf = df.fillna(0)
 
# 按照字典默认值填充空白值
newdf = df.fillna(
{
    'temperature' : 0,
    'event':'No Event',
    'windspeed':'3'
    
})
 
# 当前列前面的值填充
newdf = df.fillna(method='ffill')
 
# 当前列后面的值填充
newdf = df.fillna(method='bfill')
 
# 当前行后面的值填充
newdf = df.fillna(method='bfill' , axis= "columns")
 
#只填充2个
newdf = df.fillna(method='ffill' , limit=2)
 
#采用数字拟合（线性比例）填充
newdf = df.interpolate()
 
# 根据时间拟合（索引为时间类型），按照日期比例取值填充
newdf = df.interpolate(method="time")
 
#删除空值行
newdf= df.dropna()
 
# 全部为空值才删除 how='all'
newdf = df.dropna(how='all')
 
# thresh 对有效数据来讲：至少有3个有效数据的行才有保留的必要，否则删除
newdf = df.dropna(thresh=3)
 
#单值替换
newdf = df.replace(-99999,value=np.NaN)
 
#多值替换
newdf = df.replace(to_replace=[-99999,-88888],value=np.NaN)
 
#根据不同列属性，替换不同的值
newdf= df.replace(
{
    'temperature':-99999,
    'windspeed':[ -99999,-88888],
    'event':'0'
},np.nan)
 
#采用map格式
newdf = df.replace(
{
     -99999:np.nan,
     '0': 'No Event',
     -88888: 10
    
})
 
#采用正则表达式实现替换
newdf2 = newdf.replace(
{
    'temperature':'[A-Za-z]'
},66 ,regex=True)
 
#多值替换
df.replace(['exceptional','average','good','poor'],[5,3,4,1])
 
#将发票表用餐类型转换成枚举类型
invoices['Type of Meal'] = invoices['Type of Meal'].astype('category')
invoices['Date'] = invoices['Date'].astype('datetime64[ns]')
invoices['Meal Price'] = invoices['Meal Price'].astype('int')
 
#检测Meal Price的数据类型，获取当前列有多少个数据
invoices['Meal Price'].apply(lambda x: type(x)).value_counts()
 
#将空值替换成中位数
invoices['Meal Price'] = invoices['Meal Price'].fillna(invoices['Meal Price'].median())
 
# utc=True添加时区信息
invoices['Date of Meal'] = pd.to_datetime(invoices['Date of Meal'],utc=True)
 
#dt过滤器，date获取日期值
invoices['Date of Meal'].dt.date
#weekday_name 获取星期几
invoices['Date of Meal'].dt.weekday_name
#month 获取月份
invoices['Date of Meal'].dt.month
#获取当月有多少天，闰年2月29天
invoices['Date of Meal'].dt.days_in_month
#获取日
invoices['Date of Meal'].dt.day
#获取当前日期在本年内的第几周
invoices['Date of Meal'].dt.week
#获取季度
invoices['Date of Meal'].dt.quarter
#获取年
invoices['Date of Meal'].dt.year
#计算当前日期是否为月末
invoices['Date of Meal'].dt.is_month_end
#获取分钟值
invoices['Date of Meal'].dt.minute
 
#获取月末日期的发票信息
invoices[invoices['Date of Meal'].dt.is_month_end]
#取出发票就餐的年，然后统计相同年的发票数量，然后再按照年（索引）排序
invoices['Date of Meal'].dt.year.value_counts().sort_index()
 
#str过滤器，lower将字符转换成小写
invoices['Type of Meal'].str.lower()
#upper转换成大写
invoices['Type of Meal'].str.upper()
#15个字符空间，左对齐
invoices['Type of Meal'].str.ljust(width=15)
#15个字符空间，右对齐
invoices['Type of Meal'].str.rjust(width=15)
#居中
invoices['Type of Meal'].str.center(width=15)
#多余位置用0填充
invoices['Type of Meal'].str.zfill(width=15)
#大小写转换
invoices['Type of Meal'].str.swapcase()
#重复次数
invoices['Type of Meal'].str.repeat(4)
#连续的运算，title首字符大写
invoices['Participants'].str.lower().str.title()
 
#检索 就餐类型 含有nch的所有发票信息
invoices[invoices['Type of Meal'].str.endswith('nch')]
#检索David参与的就餐发票
invoices[invoices['Participants'].str.contains('David')]
 
#cat过滤器，ordered计算是否排序
invoices['Type of Meal'].cat.ordered
#显示枚举类型信息，系统自动将Type of Meal 的值设置为：0,1,2
invoices['Type of Meal'].cat.categories
invoices['Type of Meal'].cat.codes
 
#两张表结构相同直接连在一起
y_2013 = invoices[invoices['Date of Meal'].dt.year == 2013].copy()
y_2014 = invoices[invoices['Date of Meal'].dt.year == 2014].copy()
newdf = pd.concat([y_2013,y_2014])
 
#两张表结构完全不同，水平方向，日期重叠时，保留一行（各自的列保留）合并两张dateframe
pd.concat([df_a,df_b],axis=1)
#垂直方向合并,简单的将df_b连接在下面
pd.concat([df_a,df_b],axis=0)
 
 
#连接的四种方式，inner outer right left 默认情况是inner，关系采用相同的关键字 Order Id ,Company Id ,Date； on参数可以指定关联连接的字段
pd.merge(order_data,invoices)
pd.merge(order_data,invoices,how='left')
pd.merge(order_data,invoices,how='right')
pd.merge(order_data,invoices,how='outer')
pd.merge(order_data,invoices,on='Order Id')
pd.merge(
    order_data,
    invoices.rename(columns={'Order Id':'New Id'}),
    left_on='Order Id',
    right_on='New Id'
)
 
#suffixes()  除了Order Id作为连接条件外，还可以利用前面的 *_base字段 与后面的 *_join字段连接，举例：Company Id_base 与 Company Id_join
pd.merge(order_data,invoices,on='Order Id',suffixes=('_base','_join'))
 
 
#创建索引，构造一个类似map格式的dateframe
lookup = sales_team.set_index('Company Name')['Sales Rep']
#根据订单表Company Name的值，到lookup表查询相同的记录，给订单表添加Sales Rep列
order_data['Sales Rep'] = order_data['Company Name'].map(lookup)
 
 
# get_dummies 根据type of Meal 有多少个值，每行自动增加多少列 
# mul 是矩阵的乘法  
#举例：type of Meal 为 dinner时，转换成3列，breakfase   dinner   lunch
#                                           0          1        0
melt_experiment = pd.merge(
    invoices,
    pd.get_dummies(invoices['Type of Meal']).mul(invoices['Meal Price'].values,axis=0),
    left_index=True,
    right_index=True
)
del melt_experiment['Type of Meal']
del melt_experiment['Meal Price']
#id_vars 字段保留不动；value_vars 待合并字段；var_name 新增字段，存储合并字段名；value_name 新增字段，存储值
#melt功能：将上面merge方法处理过的数据，压缩恢复到初始状态
pd.melt(
    frame=melt_experiment,
    id_vars=['Order Id', 'Date', 'Meal Id', 'Company Id', 'Date of Meal','Participants', 'Heroes Adjustment'],
    value_vars=['Breakfast', 'Dinner', 'Lunch'],
    var_name='Type of Meal', 
    value_name='Expenses'
)
 
 
#按照 两个字段 分组，然后计算平均值
#统计每个公司的 早餐、午餐、晚餐的平均值，展示的方式3列
invoices.groupby(['Company Id','Type of Meal']).agg(
    {'Meal Price':np.mean}
)
#统计每个公司的 早餐、午餐、晚餐的平均值，展示的方式更直观4列，比单纯用merge方法，将列展开，多了一步按照公司id汇总在一起的功能
#将invoices表 行值（【Type of Meal,Meal Price】）转换成列值 【Breakfase,Dinner,Lunch,All】,
pd.pivot_table(
    invoices,
    index=['Company Id'],
    columns=['Type of Meal'],
    values='Meal Price',
    aggfunc=np.mean,
    margins=True
)
 
 
***melt与pivot***
#melt实现：将列数据转换成行（用变量名和变量值，键值对形式保存数据）
df = pd.DataFrame({"A":[12, 4, 5, 44, 1],  
                   "B":[5, 2, 54, 3, 2],  
                   "C":[20, 16, 7, 3, 8],  
                  }) 
df.melt(id_vars =['A'], value_vars =['B','C']) 
  
#pivot实现：将行数据转换成列，B的值作为列的名称（与melt相反）
df = pd.DataFrame({
      'A': ['John', 'Boby', 'Mina'], 
      'B': ['Masters', 'Graduate', 'Graduate'], 
      'C': [27, 23, 21]}) 
#类似pd.getdummies()函数的功能，将B列的不同类型值，作为扩展的列
df.pivot('A', 'B', 'C') 
 
#总结1：pivot操作每一行添加若干列（根据B的值的状态数量）
#总结2：pivot操作df时，A值的某种B状态不能有重复，否则报错

 

数据库

!pip install pymysql

import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:@localhost:3306/demo')
df = pd.read_sql_table('customers',engine)
query ='''
select `Customer Name` from customers where `Customer Name` = 'rafael nadal'
'''
df2 = pd.read_sql_query(query,engine)

#customers2添加到df中
df.to_sql(
   name='customers2', con=engine,if_exists='append',index=False
)

时间索引

# 使用新的方法 加载时间序列
df = pd.read_csv('aapl.csv' ,parse_dates=['Date'], index_col ="Date")

#显示某日期数据
df['2017-06']
df['2017-06-05']

#计算某月收盘价的平均值
df["2017-06"]["Close"].mean()

#计算某时段收盘价的平均值
df["2017-06-15":"2017-05-15"]["Close"].mean()

#以月为单位计算收盘价的平均值
df.Close.resample('M').mean().head(16)

#创建连续日期范围
rng = pd.date_range(start = '6/1/2016',end= '6/30/2016')

#创建连续的工作日范围
rng = pd.date_range(start = '6/1/2016',end= '6/30/2016' ,freq = 'B')

#以月为单位创建日期范围
rng = pd.date_range(start = '6/1/2016',end= '11/30/2016' ,freq='M')

#比较两个日期的不同
rng2.difference(rng)

#将以工作日为索引的 dataframe ，添加休息日的数据，转换成以每天为索引的dataframe，按照周五添加周日周六数据
df.asfreq('D',method='pad')

#计算每周的最后一天的dataframe
df.asfreq('W',method='pad')

#计算每小时dataframe
df.asfreq('H',method='pad')

#创建连续的工作日范围
rng = pd.date_range(start='2010-01-01',periods=20)
rng = pd.date_range(start='2010-01-01',periods=20,freq='m')
rng = pd.date_range(start='2016-07-01',periods=70,freq='H')


#五一假期和我的生日5月15日的处理方式
from pandas.tseries.holiday import AbstractHolidayCalendar , nearest_workday , Holiday
from pandas.tseries.offsets import CustomBusinessDay
class myCalendar(AbstractHolidayCalendar):
    rules = [Holiday('五一',month=5,day=1),Holiday('mybirth',month=5,day=15)]
    
wuyi = CustomBusinessDay(calendar=myCalendar())
pd.date_range(start='5/1/2017',end='5/21/2017',freq=wuyi)

#埃及工作日的处理
egypt_weekdays = "Sun Mon Tue Wed Thu"
b2 = CustomBusinessDay(weekmask=egypt_weekdays)
pd.date_range(start='5/1/2017',end='5/21/2017',freq=b2)


# 埃及15个工作日的时间
from datetime import datetime
dt = datetime(2017,7,10) 
dt + 15*b2

#字符串与日期的转换
dates2 = ['2017-01-01 2:30:00 pm','Jan 5 ,2017 14:55:00','01/06/2017','2017.05.01','20170501']
pd.to_datetime(dates2)
pd.to_datetime('12-11-2017',dayfirst=True)
pd.to_datetime('2017|06|01',format='%Y|%m|%d')
pd.to_datetime('2017,06,01',format='%Y,%m,%d')

# 由于32超出日 月 的范围，所以是非法日期，ignore不转换保留原值
pd.to_datetime(['2017-01-01','2017-09-32'],errors='ignore')
#将非法日期设置为NaT
pd.to_datetime(['2017-01-01','2017-09-32'],errors='coerce')

#时间戳
epoch = 1501325555
t= pd.to_datetime(epoch,unit='s')
t.view()
t.view('int64')


#年周期
y = pd.Period('2019')
y.start_time
y.end_time
y.is_leap_year
#月周期
m = pd.Period('2019-05')
m.start_time
m.end_time
m+1
#日周期
d = pd.Period('2019-07-10',freq='D')
d+10
d.start_time
#小时周期
h = pd.Period('2019-07-10 23:00:00',freq='H')
h +5
#季度周期
q1 = pd.Period('2017q1',freq='q-dec')
q1.start_time
q1.end_time
q1.asfreq('M')
q1.asfreq('M',how='start')
q1.asfreq('M',how='end')
#星期周期
w = pd.Period('2017-07-06',freq='W')
w-1

#创建周期索引
r = pd.period_range('2011','2017',freq='q')


#时区相关
df = pd.read_csv("msft.csv",header = 1,parse_dates= True,index_col = 'Date Time')
#以美国东部时区显示时间
df.tz_localize(tz='US/Eastern')
df.index = df.index.tz_localize(tz='US/Eastern')
df = df.tz_convert('Europe/Berlin')
df = df.tz_convert('Asia/Shanghai')

#所有时区
from pytz import all_timezones
print (all_timezones)


#时间位移
df['Prev Day Price'] = df['Price'].shift(1)
df['Price Change' ] = df['Price'] - df['Prev Day Price']
#5日的回报率
df['5 day return' ] = ((df['Price'] - df['Price'].shift(5))/df['Price'].shift(5) ) * 100

#日期索引的移动，原来是8-15 现在是8-16日
df.tshift(1)


#当前位置的值减上面位置的值之差
df.diff()
#当前位置的值减右边位置的值之差
df.diff(axis=1)
#当前位置的值减下面值的差，periods负数减下面，正数减上面
df.diff(periods=-1)