pandas之系列操作（一）

时间：2023-03-09 19:30:07
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 ='',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="", 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

相关文章