pythonpanda包操作excel（写⼊）读取csv⽂件toList排序获取指定⾏ 1.读取csv⽂件，转成list ([[xx],[xx]])，按⽇期列排序，获取最后⼀⾏的数据，写⼊excel.
2.代码:
大理石清洗剂
# -*- coding:utf-8 -*-
import requests
from bs4 import BeautifulSoup
import pandas as pd
from urllib import urlencode
from xml.dom import minidom
from xlwt import *
import numpy as np
import MySQLdb
import datetime
import time
import sys
reload(sys)
sys.setdefaultencoding('utf8')
# ⽣成excel 写⼊指定内容
def write_excel():
w = Workbook(encoding='utf-8')
# 第⼀個sheet⾴
ws = w.add_sheet('N. China Ports In&Out')
蓝莓素胶囊
# 第⼀⾏煤的种类
ws.write(0, 0, 'Inbound Rail & Shipment at N. China Ports')
ws.write(1, 0, 'Unit: 10k tons')
ws.write(2, 0, '铁路调⼊量（万吨）')
ws.write(2, 13, '吞吐量(万吨)')
# 第4⾏
ws.write(3, 0, 'Date')
ws.write(3, 1, 'QHD')
ws.write(3, 2, 'SDIC JT')
ws.write(3, 3, 'Old JT ')
ws.write(3, 4, '36-40 JT')
ws.write(3, 5, 'SDIC CFD')
ws.write(3, 6, 'CFD2')
ws.write(3, 7, 'Huaneng CFD')
ws.write(3, 8, 'HH')
ws.write(3, 9, 'TOTAL')
ws.write(3, 10, 'Inbound\nRail\n5dma')
ws.write(3, 11, ' ')
ws.write(3, 12, 'Date')
ws.write(3, 13, 'QHD')
ws.write(3, 14, 'SDIC JT')
ws.write(3, 15, 'Old JT ')
ws.write(3, 16, '36-40 JT')
ws.write(3, 17, 'SDIC CFD')
ws.write(3, 18, 'CFD2')
ws.write(3, 19, 'Huaneng CFD')
ws.write(3, 20, 'HH')
ws.write(3, 21, 'TOTAL')
ws.write(3, 22, 'Inbound\nRail\n5dma')
ws.write(3, 23, ' ')
ws.write(3, 24, 'Date')
ws.write(3, 25, 'INBOUND RAIL')
ws.write(3, 26, 'SHIPMENT')
ws.write(3, 27, 'YEAR')
ws.write(3, 28, 'MONTH')
ws.write(3, 29, 'WEEK')
ws.write(3, 30, 'DAY')
ws.write(3, 30, 'DAY')
ws.write(3, 31, ' ')
ws.write(3, 32, 'SHIPMENT\n5dma')
ws.write(3, 33, ' ')
ws.write(3, 34, 'Date')
ws.write(3, 35, 'Implied Daqin\nRailway')
ws.write(3, 36, 'Implied Mengji\nRailway')
ws.write(3, 37, 'Implied Shuohuang\nRailway')
ws.write(3, 38, 'Daqin Railway\n5dma')
ws.write(3, 39, 'Mengji Railway\n5dma')
ws.write(3, 40, 'Shuohuang Railway\n5dma')
# 第五⾏中⽂描述
ws.write(4, 1, '秦皇岛港')
ws.write(4, 2, '国投京唐港')
ws.write(4, 3, '京唐⽼港')
ws.write(4, 4, '36-40码头')
ws.write(4, 5, '国投曹妃甸港')
ws.write(4, 6, '曹妃甸⼆期')
ws.write(4, 7, '华能曹妃甸')
ws.write(4, 8, '黄骅港')
ws.write(4, 9, '')
ws.write(4, 13, '秦皇岛港')
ws.write(4, 14, '国投京唐港')
ws.write(4, 15, '京唐⽼港')
ws.write(4, 16, '36-40码头')
ws.write(4, 17, '国投曹妃甸港')
ws.write(4, 18, '曹妃甸⼆期')
ws.write(4, 19, '华能曹妃甸')
ws.write(4, 20, '黄骅港')
ws.write(4, 21, '')
# 獲取當天⽇期
# 寫⼊數據
today = day()
qinghuangdaoList = read_cvs()
guotoujingtanggangList = read_guotoujingtanggangcvs()
jingtanglaogangList = read_jingtanglaogangcvs()
read_36_40Lsit = read_36_40cvs()
guotoucaofeiList = read_guotoucaofeicvs()
caofeierqiList = read_caofeierqicvs()
huanengcaofeierqiLsit = read_huanengcaofeierqicvs()
huanghuaList = read_huanghuacvs()
# 铁路调⼊量（万吨）
ws.write(5, 0, today.__str__())
ws.write(5, 1, qinghuangdaoList[8])
ws.write(5, 2, guotoujingtanggangList[6])
ws.write(5, 3, jingtanglaogangList[5])
ws.write(5, 4, read_36_40Lsit[5])
ws.write(5, 5, guotoucaofeiList[7])
ws.write(5, 6, caofeierqiList[6])
ws.write(5, 7, huanengcaofeierqiLsit[6])
ws.write(5, 8, huanghuaList[6])
ws.write(5, 9, float(qinghuangdaoList[8])+float(guotoujingtanggangList[6])+float(jingtanglaogangList[5])+float(read_36_40Lsit[5])+float(guotoucaofeiList[7 ])+float(caofeierqiList[6])+float(huanengcaofeierqiLsit[6])+float(huanghuaList[6]))
# 吞吐量(万吨)
ws.write(5, 12, today.__str__())
ws.write(5, 13, qinghuangdaoList[7])
ws.write(5, 14, guotoujingtanggangList[5])
ws.write(5, 15, jingtanglaogangList[4])
ws.write(5, 16, read_36_40Lsit[4])
ws.write(5, 17, guotoucaofeiList[6])
ws.write(5, 18, caofeierqiList[5])
ws.write(5, 19, huanengcaofeierqiLsit[5])
ws.write(5, 20, huanghuaList[5])
ws.write(5, 21, float(qinghuangdaoList[7])+float(guotoujingtanggangList[5])+float(jingtanglaogangList[4])+float(read_36_40Lsit[4])+float(guotoucaofeiList[
ws.write(5, 21, float(qinghuangdaoList[7])+float(guotoujingtanggangList[5])+float(jingtanglaogangList[4])+float(read_36_40Lsit[4])+float(guotoucaofeiList[ 6])+float(caofeierqiList[5])+float(huanengcaofeierqiLsit[5])+float(huanghuaList[5]))
# 第⼆個sheet⾴
ws = w.add_sheet('N. China Ports Vessels')
# 第⼀⾏煤的种类
ws.write(0, 0, 'Vessels at N. China Ports')
ws.write(1, 0, 'Unit: 10k tons')
ws.write(2, 0, '锚地船舶(艘)')
ws.write(2, 13, '预到船舶（艘）')
# 第4⾏
ws.write(3, 0, 'Date')
ws.write(3, 1, 'QHD')
ws.write(3, 2, 'SDIC JT')
ws.write(3, 3, 'Old JT ')
ws.write(3, 4, '36-40 JT')
ws.write(3, 5, 'SDIC CFD')
ws.write(3, 6, 'CFD2')
ws.write(3, 7, 'Huaneng CFD')
ws.write(3, 8, 'HH')
ws.write(3, 9, 'TOTAL')
ws.write(3, 10, 'Inbound\nRail\n5dma')
ws.write(3, 11, ' ')
ws.write(3, 12, 'Date')
ws.write(3, 13, 'QHD')
ws.write(3, 14, 'SDIC JT')
ws.write(3, 15, 'Old JT ')
ws.write(3, 16, '36-40 JT')
ws.write(3, 17, 'SDIC CFD')
ws.write(3, 18, 'CFD2')
imcaws.write(3, 19, 'Huaneng CFD')
ws.write(3, 20, 'HH')
ws.write(3, 21, 'TOTAL')
ws.write(3, 22, 'Inbound\nRail\n5dma')
ws.write(3, 23, ' ')
ws.write(3, 24, 'Date')
ws.write(3, 25, 'INBOUND RAIL')
ws.write(3, 26, 'SHIPMENT')
ws.write(3, 27, 'YEAR')
ws.write(3, 28, 'MONTH')
ws.write(3, 29, 'WEEK')
ws.write(3, 30, 'DAY')
ws.write(3, 31, ' ')
ws.write(3, 32, 'SHIPMENT\n5dma')
ws.write(3, 33, ' ')
ws.write(3, 34, 'Date')
ws.write(3, 35, 'Implied Daqin\nRailway')
ws.write(3, 36, 'Implied Mengji\nRailway')
ws.write(3, 37, 'Implied Shuohuang\nRailway')
ws.write(3, 38, 'Daqin Railway\n5dma')
ws.write(3, 39, 'Mengji Railway\n5dma')
ws.write(3, 40, 'Shuohuang Railway\n5dma')
# 第五⾏中⽂描述
ws.write(4, 1, '秦皇岛港')
ws.write(4, 2, '国投京唐港')消息队列实现
ws.write(4, 3, '京唐⽼港')
ws.write(4, 4, '36-40码头')
ws.write(4, 5, '国投曹妃甸港')
ws.write(4, 6, '曹妃甸⼆期')
ws.write(4, 7, '华能曹妃甸')
ws.write(4, 8, '黄骅港')
ws.write(4, 9, '')
ws.write(4, 13, '秦皇岛港')
ws.write(4, 13, '秦皇岛港')
ws.write(4, 14, '国投京唐港')
ws.write(4, 15, '京唐⽼港')
ws.write(4, 16, '36-40码头')
ws.write(4, 17, '国投曹妃甸港')
ws.write(4, 18, '曹妃甸⼆期')
ws.write(4, 19, '华能曹妃甸')
ws.write(4, 20, '黄骅港')
ws.write(4, 21, '')
# 獲取當天⽇期
# 寫⼊數據
# 锚地船舶(艘)
ws.write(5, 0, today.__str__())
ws.write(5, 1, qinghuangdaoList[0])
ws.write(5, 2, guotoujingtanggangList[1])
ws.write(5, 3, jingtanglaogangList[2])
ws.write(5, 4, read_36_40Lsit[2])
ws.write(5, 5, guotoucaofeiList[0])
ws.write(5, 6, caofeierqiList[0])
ws.write(5, 7, huanengcaofeierqiLsit[0])
ws.write(5, 8, huanghuaList[0])
ws.write(5, 9, float(qinghuangdaoList[0]) + float(guotoujingtanggangList[1]) + float(jingtanglaogangList[2]) + float(read_36_40Lsit[2]) + float(guotoucaofei List[0]) + float(caofeierqiList[0]) + float(huanengcaofeierqiLsit[0]) + float(huanghuaList[0]))
# 预到船舶（艘）
ws.write(5, 12, today.__str__())
ws.write(5, 13, qinghuangdaoList[1])
ws.write(5, 14, guotoujingtanggangList[2])
ws.write(5, 15, jingtanglaogangList[3])
ws.write(5, 16, read_36_40Lsit[3])
ws.write(5, 17, guotoucaofeiList[1])
点火ws.write(5, 18, caofeierqiList[1])
ws.write(5, 19, huanengcaofeierqiLsit[1])
ws.write(5, 21, float(qinghuangdaoList[1]) + float(guotoujingtanggangList[2]) + float(jingtanglaogangList[3]) + float(read_36_40Lsit[3]) + float(guotoucaof eiList[1]) + float(caofeierqiList[1]) + float(huanengcaofeierqiLsit[1]))
w.save('china_coal.xls')
dataList = []
# 读取本地cvs⽂件获取指定内容 (讀取秦皇島)
def read_cvs():
# 1. ⽤pandas读取csv ；把哪⼀列的数据转换成⽇期类型 parse_dates=[哪⼀列，从0开始],infer_datetime_format=True
data = pd.read_csv(u'C:/pythonProject/port/20190621/秦皇岛港⽇调度量历史数据_data.csv',parse_dates=[5],infer_datetime_format=True)
print (data)
# 按⽇期排序升序
data.sort_values('⽇期',inplace=True)
print '====================='
print data
print '------------------------'
# 读取CSV，并将数据转化为数组
list=list()
listLen = len(list)
print listLen
# 获取最后⼀⾏的数据
endCol = list[listLen-1]
print '最後⼀列的list : ' ,endCol
# 獲取當天⽇期
today = day()
# 把⽇期格式成所需格式 .strftime("%Y/%m/%d")
todays = today.strftime("%Y/%m/%d")
print 'today : ',todays
yesterday = today - datetime.timedelta(days=1)
yesterdays = yesterday.strftime("%Y/%m/%d")
print 'yesterday : ' ,yesterdays
print 'read day : ' ,endCol[5].strftime("%Y/%m/%d")
if endCol[5].strftime("%Y/%m/%d") == todays:
print '1'
print '1'
return endCol
耳光室
else:
print '2'
return endCol
# 读取本地cvs⽂件获取指定内容 (讀取国投京唐港)
def read_guotoujingtanggangcvs():
# 1. ⽤pandas读取csv
data = pd.read_csv(u'C:/pythonProject/port/20190621/国投京唐港⽇调度量历史数据_data.csv',parse_dates=[4],infer_datetime_format=True) print (data)
# 按⽇期降序
data.sort_values('⽇期',inplace=True)
print '====================='
print data
print '------------------------'
# 读取CSV，并将数据转化为数组
list=list()
listLen = len(list)
print listLen
endCol = list[listLen-1]
print '最後⼀列的list : ' ,endCol
# 獲取當天⽇期
today = day()
todays = today.strftime("%Y/%m/%d")
print 'today : ', todays
yesterday = today - datetime.timedelta(days=1)
yesterdays = yesterday.strftime("%Y/%m/%d")
print 'yesterday : ', yesterdays
print 'read day : ', endCol[4].strftime("%Y/%m/%d")
if endCol[4].strftime("%Y/%m/%d") == todays:
print '1'
return endCol
else:
print '2'
return endCol
# 读取本地cvs⽂件获取指定内容 (讀取京唐⽼港)
def read_jingtanglaogangcvs():
# 1. ⽤pandas读取csv
data = pd.read_csv(u'C:/pythonProject/port/20190621/京唐港⽼港⽇调度量历史数据_data.csv',parse_dates=[0],infer_datetime_format=True) print (data)
# 按⽇期降序
data.sort_values('⽇期',inplace=True)
print '====================='
print data
print '------------------------'
# 读取CSV，并将数据转化为数组
list=list()
listLen = len(list)
print listLen
endCol = list[listLen-1]
print '最後⼀列的list : ' ,endCol
# 獲取當天⽇期
today = day()
todays = today.strftime("%Y/%m/%d")
print 'today : ', todays
yesterday = today - datetime.timedelta(days=1)
yesterdays = yesterday.strftime("%Y/%m/%d")
print 'yesterday : ', yesterdays
print 'read day : ', endCol[0].strftime("%Y/%m/%d")
if endCol[0].strftime("%Y/%m/%d") == todays:
print '1'
return endCol
else:
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议