pandas之系列操作（一）

1.读Excel：

# coding=utf-8
import pandas as pd
import pymysql
sql_select =" xxxxx "
con = pymysql.connect(host="xxxx", user="xxx", passwd="xxxx", db="xxxx", charset='utf8',port=5366)
df1 = pd.read_excel(r'D:1.xls',header=None,sep=',')
con.close()

2.写Excel：

# coding=utf-8
import pandas as pd
import pymysql
sql_select =" xxx "
con = pymysql.connect(host="xxx", user="xx", passwd="xxx", db="xxx", charset='utf8',port=5366)
df = pd.read_sql(sql_select,con)
con.close()
with pd.ExcelWriter(r'D:2.xls') as writer:
    df.to_excel(writer,sheet_name ='3',encoding = 'utf-8', index = False,header=False)

 3.写入 Mysql：

# coding=utf-8
from sqlalchemy import create_engine
import pandas as pd
import sys
reload(sys)
sys.setdefaultencoding('utf8')
host = 'xx'
port = 5366
db = 'xxx'
user = 'xx'
password = 'xxx'

engine = create_engine(str(r"mysql+mysqldb://%s:" + '%s' + "@%s:%s/%s?charset=utf8") % (user, password, host, port,db))
print(engine)
try:
    df = pd.read_excel(r'D:2.xls')

    print(df)

    pd.io.sql.to_sql(df,'app_errortest',con=engine,if_exists='append',index=False,chunksize=10000)
except Exception as e:
    print(e.message)

 4.根据周统计数据

# coding=utf-8
import pandas as pd
import pymysql
import numpy as np
from pandas import Series,DataFrame
from datetime import datetime
sql_select =" select id, DataChange_LastTime from`app01_student` "
con = pymysql.connect(host="127.0.0.1", user="root", passwd="123456789", db="test", charset='utf8',port=3306)
df = pd.read_sql(sql_select,con)
con.close()
df['DataChange_LastTime'] =pd.to_datetime(df['DataChange_LastTime']) #转化为DatetimeIndex格式
df =df.set_index('DataChange_LastTime')#设置索引


# print(type(df))
# print(df.index)
# print(type(df.index))
# print(df.shape) #查看几行几列
rs=df.resample('w').count()
n = rs.to_dict('split')['index']
v = rs.to_dict(orient="list")['id']
name =[]
value=[]
for i in n:
    i=i.to_pydatetime()
    i =datetime.strftime(i,'%Y-%m-%d')
    name.append(i)
for i in v:
    i =int(i)
    value.append(i)

print(name)
print(value)

 5.pandas 将Excel转换字典

#! /usr/bin/env python
# coding=utf-8
import pandas as  pd
df =pd.read_excel(r'D:pandas.xls') #字典形式
res = df.to_dict(orient="records") #大字典嵌套小字典
res = df.to_dict()
print res