对多张表数据处理并整合成一张表输出

记录下第一次按需求写代码

import pandas as pd
import csv
import numpy as np


BrokerList=['FXTM','FXCM','GKFX','IC','IG','Gain']
DateList = ['03232020','03242020','03252020']
HOUR=['00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23']

#输出结果为7列，第一列为时间，后面6列对应6张表处理后的数据
Datehour=[]
FXTM_MeanSpread_list=[]
FXCM_MeanSpread_list=[]
GKFX_MeanSpread_list=[]
IC_MeanSpread_list=[]
IG_MeanSpread_list=[]
Gain_MeanSpread_list=[]

#存储第一列数据
for Date in DateList:
    for i in HOUR:
        datehour = Date + "-" + str(i)
        Datehour.append(datehour)

#循环遍历处理6张表中的数据，并存储在对应列表
for Date in DateList:
    for Broker in BrokerList:
        file="E:/GER30/GER30/{0}-{1}.csv".format(Broker,Date)
        roughdata = pd.read_csv(file,sep=',')
        roughdata['Spread']=roughdata['Ask'] - roughdata['Bid']   #两列数据相减得到一列新数据
        roughdata['hour']=[t[:2] for t in roughdata['Time']]     #取时间的前两位，例00:29:59 取00
      #  roughdata.drop(['Swap Long', 'Swap Short','Time','Bid','Ask'], axis=1, inplace=True)    #删除列
        Spreaddict= dict(roughdata.groupby(['hour'])['Spread'].mean())      #对hour列分组求对应Spread列均值，并存储为hour：Spread的字典
        #gruop()筛选分组对数据量以万计时也是秒出结果，但是以for循环分组就慢非常多
        for h in HOUR:
            if Broker == 'FXTM':        #以表名匹配需对应写入的列表
                if  h in Spreaddict.keys():    #判断hour是否在字典的key中，因为每张表的条目数是不同的
                    for key,value in Spreaddict.items():      #相当于vlookup
                        if key == h:        #以时间匹配字典中的值，存入对应列表
                            FXTM_MeanSpread_list.append(value)   
                else:
                    FXTM_MeanSpread_list.append("")        #没有对应key则存入空值，为了后面zip()做准备，保证7张表条数相同
            elif Broker == 'FXCM':
                if h in Spreaddict.keys():
                    for key, value in Spreaddict.items():
                        if key == h:
                            FXCM_MeanSpread_list.append(value)
                else:
                    FXCM_MeanSpread_list.append("")
            elif Broker == 'GKFX':
                if h in Spreaddict.keys():
                    for key, value in Spreaddict.items():
                        if key == h:
                            GKFX_MeanSpread_list.append(value)
                else:
                    GKFX_MeanSpread_list.append("")
            elif Broker == 'IC':
                if h in Spreaddict.keys():
                    for key, value in Spreaddict.items():
                        if key == h:
                            IC_MeanSpread_list.append(value)
                else:
                    IC_MeanSpread_list.append("")
            elif Broker == 'IG':
                if h in Spreaddict.keys():
                    for key, value in Spreaddict.items():
                        if key == h:
                            IG_MeanSpread_list.append(value)
                else:
                    IG_MeanSpread_list.append("")
            elif Broker == 'Gain':
                if h in Spreaddict.keys():
                    for key, value in Spreaddict.items():
                        if key == h:
                            Gain_MeanSpread_list.append(value)
                else:
                    Gain_MeanSpread_list.append("")
# print(FXTM_MeanSpread_list)
# print(FXCM_MeanSpread_list)
# print(GKFX_MeanSpread_list)
# print(IC_MeanSpread_list)
# print(IG_MeanSpread_list)
# print(Gain_MeanSpread_list)

#相当于是转置，为了后面按行写入数据
zip1= zip(Datehour,
          FXTM_MeanSpread_list,
          FXCM_MeanSpread_list,
          GKFX_MeanSpread_list,
          IC_MeanSpread_list,
          IG_MeanSpread_list,
          Gain_MeanSpread_list,)



with open('E:/finaldata1.csv','w',newline='') as csvfile:
    #创建一个写入对象
    FD = csv.writer(csvfile)
    FD.writerow(["Datehour",'FXTM','FXCM','GKFX','IC','IG','Gain'])   #新建列名
    for row in zip1:      #对zip中的数据按行写入
        FD.writerow(row)

输出表格如下大致格式如下：