pandas之系列操作（一）

1.读Excel：

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

2.写Excel：

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

3.写入 Mysql：

 1 # coding=utf-8
 2 from sqlalchemy import create_engine
 3 import pandas as pd
 4 import sys
 5 reload(sys)
 6 sys.setdefaultencoding('utf8')
 7 host = 'xx'
 8 port = 5366
 9 db = 'xxx'
10 user = 'xx'
11 password = 'xxx'
12 
13 engine = create_engine(str(r"mysql+mysqldb://%s:" + '%s' + "@%s:%s/%s?charset=utf8") % (user, password, host, port,db))
14 print(engine)
15 try:
16     df = pd.read_excel(r'D:2.xls')
17 
18     print(df)
19 
20     pd.io.sql.to_sql(df,'app_errortest',con=engine,if_exists='append',index=False,chunksize=10000)
21 except Exception as e:
22     print(e.message)

4.根据周统计数据

 1 # coding=utf-8
 2 import pandas as pd
 3 import pymysql
 4 import numpy as np
 5 from pandas import Series,DataFrame
 6 from datetime import datetime
 7 sql_select =" select id, DataChange_LastTime from`app01_student` "
 8 con = pymysql.connect(host="127.0.0.1", user="root", passwd="123456789", db="test", charset='utf8',port=3306)
 9 df = pd.read_sql(sql_select,con)
10 con.close()
11 df['DataChange_LastTime'] =pd.to_datetime(df['DataChange_LastTime']) #转化为DatetimeIndex格式
12 df =df.set_index('DataChange_LastTime')#设置索引
13 
14 
15 # print(type(df))
16 # print(df.index)
17 # print(type(df.index))
18 # print(df.shape) #查看几行几列
19 rs=df.resample('w').count()
20 n = rs.to_dict('split')['index']
21 v = rs.to_dict(orient="list")['id']
22 name =[]
23 value=[]
24 for i in n:
25     i=i.to_pydatetime()
26     i =datetime.strftime(i,'%Y-%m-%d')
27     name.append(i)
28 for i in v:
29     i =int(i)
30     value.append(i)
31 
32 print(name)
33 print(value)

View Code

5.pandas 将Excel转换字典

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