def gerar_sheet(): writer = ExcelWriter("sheet.xlsx") # criando arquivo workbook = writer.book # instanciando para uso dos métodos formato = workbook.add_format({'text_wrap': True}) xls_trabalhos = pd.ExcelFile('trabalhos.xlsx') # carrega xlsx df = xls_trabalhos.parse('Trabalho_Eventos') # xlsx -> dataframe df.to_excel(writer, "Trabalhos em Eventos", index=False) # df -> excel worksheet1 = writer.sheets['Trabalhos em Eventos'] # escrevendo dados worksheet1.set_column(0, 4, 30, formato) # formato das colunas xls_periodicos = pd.ExcelFile('artigos.xlsx') df = xls_periodicos.parse('Artigos_publicados') df.to_excel(writer, "Trabalhos em Periódicos", index=False) worksheet2 = writer.sheets['Trabalhos em Periódicos'] worksheet2.set_column(0, 3, 30, formato) xls_capitulos = pd.ExcelFile('capitulos.xlsx') df = xls_capitulos.parse('Capitulos_Publicados') df.to_excel(writer, "Capítulos de Livros", index=False) worksheet3 = writer.sheets['Capítulos de Livros'] worksheet3.set_column(0, 3, 30, formato) df = pd.read_csv('apresentacoes.csv') df.to_excel(writer, "Apresentação de Trabalhos", index=True, index_label='TITULO DO TRABALHO') worksheet4 = writer.sheets['Apresentação de Trabalhos'] worksheet4.set_column(0, 6, 30, formato) writer.save() # salva e fecha o arquivo writer.close() os.remove("trabalhos.xlsx") # remove as planilhas originais os.remove("artigos.xlsx") os.remove("capitulos.xlsx") os.remove("apresentacoes.csv")
def to_excel(self, write_mode='a', highest_score=True): out_put_path = self.output_file.with_suffix('.xlsx') columns = self._init_columns() dict_data_list = self.get_list_dict_data(self.gcms, highest_score=highest_score) df = DataFrame(dict_data_list, columns=columns) if write_mode == 'a' and out_put_path.exists(): writer = ExcelWriter(out_put_path, engine='openpyxl') # try to open an existing workbook writer.book = load_workbook(out_put_path) # copy existing sheets writer.sheets = dict( (ws.title, ws) for ws in writer.book.worksheets) # read existing file reader = read_excel(out_put_path) # write out the new sheet df.to_excel(writer, index=False, header=False, startrow=len(reader) + 1) writer.close() else: df.to_excel(self.output_file.with_suffix('.xlsx'), index=False, engine='openpyxl') self.write_settings(self.output_file, self.gcms)
def read_Queue(q, Opath): "处理子进程数据" df = pd.DataFrame() while not q.empty(): if df.empty: df = q.get() else: df = df.join(q.get()) df = df.round(2) ### save Codon usage writer = ExcelWriter(Opath + '.RSCU.xlsx', engine='xlsxwriter') df.T.to_excel(writer, sheet_name='sheet1') worksheet = writer.sheets["sheet1"] worksheet.conditional_format("B4:BH" + str(df.T.shape[0] + 3), {'type': '3_color_scale'}) writer.close() ### save Codon usage df.reset_index(inplace=True) df['Codon'] = df['AmAcid'] + '.' + df['Codon'] del df['AmAcid'] df.set_index("Codon", inplace=True) df.to_csv(Opath + ".RSCU.txt", sep='\t')
class excelWriter(object): def __init__(self, fileName): self.writer = ExcelWriter(fileName + '.xlsx', engine="xlsxwriter") self.sheetCount = 1 self.book = self.writer.book def write(self, data, sheetName="Sheet", title=None): sName = sheetName + str(self.sheetCount) data.to_excel(self.writer, sheet_name=sName, startrow=(1 if title else 0)) sheet = self.writer.sheets[sName] sheet.set_column(0, last_col=data.columns.size, width=18) if title: merge_format = self.book.add_format({ 'bold': 1, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': 'yellow' }) sheet.merge_range(first_row=0, last_row=0, first_col=0, last_col=data.columns.size, data=title, cell_format=merge_format) self.sheetCount += 1 def __del__(self): self.writer.save() self.writer.close()
def WriteExcel(Prefix,df,Ref): writer=ExcelWriter(Prefix+'.nonsynonymous.AmAcid.substitution.xlsx',engine='xlsxwriter') # Add a format. Light red fill with dark red text. format1 = writer.book.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'}) # Add a format. Green fill with dark green text. format2 = writer.book.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'}) df.to_excel(writer,sheet_name="Sheet1") worksheet=writer.sheets["Sheet1"] count=0 for i,k in df.iteritems(): RefCodon=k.loc[Ref] count+=1 worksheet.conditional_format(3,count,len(df)+2,count, {'type':'text', 'criteria': 'not containing', 'value':str(RefCodon), 'format':format1} ) worksheet.conditional_format(3,count,len(df)+2,count, {'type':'text', 'criteria':'containing', 'value':str(RefCodon), 'format':format2} ) writer.close()
def generateReport(self): print('Generating final report...') xlWriter = ExcelWriter('caseWhenThenOutput.xlsx') if (self.mismatchDF.empty): print("There are NO Mismatch found...") else: self.mismatchDF.to_excel(xlWriter, 'report') if (self.dataMismatchDF.empty): print("There are NO Mismatches found for scenario - Data Mismatches") else: self.dataMismatchDF.to_excel(xlWriter, 'dataMismatch') if (self.qaIsNullProdNotNullMismatchDF.empty): print("There are NO Mismatches found for scenario - QA Is Null & PROD Is Not Null") else: self.qaIsNullProdNotNullMismatchDF.to_excel(xlWriter, 'QAIsNullProdIsNotNull') if(self.qaIsNotNullProdNullMismatchDF.empty): print("There are NO Mismatches found for scenario - QA Is Not Null & PROD Is Null") else: self.qaIsNotNullProdNullMismatchDF.to_excel(xlWriter, 'QAIsNotNullProdIsNull') if(self.qaIsNullProdNullMismatchDF.empty): print("There are NO Mismatches found for scenario - QA Is Null & PROD Is Null") else: self.qaIsNullProdNullMismatchDF.to_excel(xlWriter, 'QAIsNullProdIsNull') time.sleep(3) xlWriter.save() time.sleep(5) xlWriter.close()
def ExportToFile(self, queryStr, filename, overwrite_policy='delete'): """ Export the results of a query to an Excel or CSV file The file must end in "csv", "xls*" or ".json". This method may not be suitable for large data exports, as an internal Pandas DataFrame is created. overwrite_policy is an enumerable with the following options/behaviors: policy behavior ======================================================================== delete Overwrite existing file move Rename the existing file (random file name, see log messages) If move failed, an exception is raised :param queryStr: Query to export :type queryStr: basestring :param filename: Fully specified location of file to create :type filename: basestring :param overwrite_policy: What to do if a file exists at the location specified (enum, see doc) :type overwrite_policy: basestring :raises: SquareRootException """ ## Check if file already exsists if is_file(filename): if overwrite_policy == 'move': old_filename = '{0}.{1}'.format(filename, random_string()) self.logger.warning( 'A file exists at {0}. It will be renamed to {1}.'.format( filename, old_filename)) was_moved = move_file(filename, old_filename) if not was_moved: raise SquareRootException( '{0} could not be moved to {1}.'.format( filename, old_filename)) elif overwrite_policy == 'delete': was_deleted = delete_file(filename) if not was_deleted: raise SquareRootException( 'A file exists at {0} and could not be deleted.'. format(filename)) ## get results results = self.RunQuery(queryStr, output_type='data_frame') ## get extension extension = filename.split('.')[-1] if extension == 'csv': results.to_csv(filename, sep=',', index=False) elif 'xls' in extension: writer = ExcelWriter(filename) results.to_excel(writer, 'Results', index=False) writer.close() elif extension == 'json': results else: raise SquareRootException( '{0} has an unsupported export file type.'.format(filename))
def build_and_send_email(self, data, options): date = timezone.now().date().strftime('%Y_%m_%d') if 'recipients' in options: print 'yes' recipients = options['recipients'] else: print 'no' recipients = settings.DEFAULT_WEEKLY_RECIPIENTS print 'recipients:', recipients message = EmailMessage(subject='Kikar Hamedina, Weekly Report: %s' % date, body='Kikar Hamedina, Weekly Report: %s.' % date, to=recipients) w = ExcelWriter('Weekly_report_%s.xlsx' % date) for datum in data: # csvfile = StringIO.StringIO() pd.DataFrame.from_dict(datum['content']).to_excel(w, sheet_name=datum['name']) w.save() w.close() # f = open(w.path, 'r', encoding='utf-8') message.attach_file(w.path) message.send()
def save(self, header): i = 0 while i<5: try: writer = ExcelWriter('temp.xlsx', 'xlsxwriter') self.df.to_excel(writer, sheet_name='Sheet1', columns=header, index=False) writer.save() writer.close() break except OSError: try: del writer except: pass i += 1 if i == 5: if os.path.exists('temp.xlsx'): os.remove('temp.xlsx') return while True: try: if os.path.exists(self.fp): os.remove(self.fp) if not os.path.exists(self.fp): os.rename('temp.xlsx', self.fp) break except PermissionError: print("[{}] 파일이 열려 있습니다. 10 초후 다시 실행합니다.".format(self.fp)) time.sleep(10)
def excelMaker(dictionary): df = pd.DataFrame(dictionary[0]) #Cria o dataframe pelo pandas writer = ExcelWriter("Curriculo.xlsx") #Cria o arquivo excel workbook = writer.book #Cria a instância book para podermos utilizar a função formato = workbook.add_format({ 'text_wrap': True }) #Armazena o formato que buscamos, nesse caso de quebra de texto df.to_excel( writer, "Trabalho_Eventos", index=False ) #É adicionado o nome do sheet e em seguida seleciona a opção de ter ou não index worksheet = writer.sheets[ 'Trabalho_Eventos'] #Variável para identificar com qual sheet será trabalhado worksheet.set_column( 0, dictionary[1], 20, formato ) #É modificado o tamanho da coluna, selecionando de qual até qual coluna será modificado #No caso acima o último parâmetro passado se trata do formato de ter quebra de texto for i in range(dictionary[1]): worksheet.set_row( i, 90, formato ) #Diferente da definição da coluna, no set_row não existe parâmetro de início e fim para linha, apenas #da linha em questão, por isso é necessário a iteração que se trata da variável contador da função anterior writer.save() writer.close() #Salva e finaliza a edição do arquivo
def to_excel(self, directory, filename, startrow, startcol, sheet_name): """Сохранить собранные данные в эксель-файл.""" directory = directory + self.DIR_NAME if not os.path.exists(directory): os.makedirs(directory) filename = directory + filename + self.EXTENSION writer = None try: book = load_workbook(filename) writer = ExcelWriter(filename, engine='openpyxl') writer.book = book except FileNotFoundError: pass df = DataFrame( data={k: v for k, v in self.__dict__.items() if k in self.COLUMNS}) df.to_excel(excel_writer=writer or filename, sheet_name=sheet_name, startrow=startrow, startcol=startcol, index=False) if writer: writer.save() writer.close() print('Сохранено в ' + filename + ' на лист ' + sheet_name)
def correggi_file_asta(): """ Crea una copia del file originale contenente le rose definite il giorno dell'asta ma con i nomi dei calciatori corretti secondo il formato di Fantagazzetta. """ asta = pd.read_excel(os.getcwd() + '/Asta{}.xlsx'.format(anno), header=0, sheet_name="Foglio1") players = dbf.db_select(database=dbase, table='players', columns_in=['player_name', 'player_team'], dataframe=True) for i in range(0, len(asta.columns), 3): temp_pl = asta[asta.columns[i:i + 3]].dropna() for j in range(len(temp_pl)): pl, tm = temp_pl.loc[j, temp_pl.columns[0:2]] flt_df = players[players['player_team'] == tm.upper()] names = flt_df['player_name'].values correct_pl = jaccard_result(pl, names, 3) asta.loc[ j, [asta.columns[i], asta.columns[i + 1]]] = correct_pl, tm.upper() writer = ExcelWriter('Asta{}_2.xlsx'.format(anno), engine='openpyxl') asta.to_excel(writer, sheet_name='Foglio1') writer.save() writer.close()
def writeExcelFileByRep(owner_value, output_folder): owner = str(owner_value) # Filter by owner df_abridged = df[df['Sales Representative'] == owner] rows_target = dataframe_to_rows(df_abridged) # ------------ # Write to Excel # ------------ FILE_PATH = output_folder print(FILE_PATH) book = load_workbook(FILE_PATH) writer = ExcelWriter(FILE_PATH, engine='openpyxl') writer.book = book for sheet in book.worksheets: if sheet.title == 'Contacts': for row in sheet['A1:H4']: for cell in row: cell.value = None # Replenish for r_idx, row in enumerate(rows_target, 1): for c_idx, value in enumerate(row, 1): sheet.cell(row=r_idx, column=c_idx, value=value) constant_tries = 2000 tries = 2000 assert tries > 0 error = None result = None while tries: try: writer.save() writer.close() except IOError as e: error = e tries -= 1 print('Attempt #', (constant_tries - tries) + 1) except ValueError as e: error = e tries -= 1 print('Attempt #', (constant_tries - tries) + 1) else: break if not tries: print('Attempt #', (constant_tries - tries) + 1) raise error print('Attempt #', (constant_tries - tries) + 1) #print(df_abridged.loc[:,'Company':'Industry'].head(5)) print("Done writing Excel file!")
def transDf(input_file, outfile, inter, name): #determine the file name if not outfile.endswith(".xlsx"): outfile = outfile.split(".")[0] + ".xlsx" data = read_excel(input_file, index=False, sheet_name="Results") #get number of target target = repeat(data["Target Name"]) target_num = len(target) #get number of sample sample = repeat(data["Sample Name"]) sample_num = len(sample) #some necessary number data_num = len(data) group_num = int(data_num / (target_num * sample_num)) #create Panel dataformat cols = list(data.columns) + ["RQ"] out_data = DataFrame(columns=cols, index=data.index, dtype="float64") for i in range(group_num): trans_df = data.iloc[range(i, data_num, target_num)] trans_df = data_conduct(trans_df, inter) out_data.loc[trans_df.index] = trans_df result = integrate(target, sample, name, group_num, out_data) #save data writer = ExcelWriter(outfile) #result.to_excel() out_data.to_excel(writer, index=False, sheet_name="Treated") result.to_excel(writer, index=True, sheet_name="Transform") writer.close()
def saveExcel(data_frame, file_name,sr, use_index=False): #Define name of excel file writefilestr = file_name writer = ExcelWriter(writefilestr, engine='openpyxl') #Write and save data to 'Sheet1' (default) data_frame.to_excel(writer,'Sheet1',index=use_index, startrow=sr,startcol=0) writer.save() writer.close()
def saveFile2(x): name = list(x['name'])[0] xlsx = ExcelWriter(path + name + self.thisYear + '年' + self.thisMonth + '月员工考勤原始记录表.xlsx') temp = x[['userid', 'name', 'workDept', 'anotherdate', 'time']] temp.columns = [['考勤号码', '姓名', '部门', '日期', '时间']] temp.to_excel(xlsx, '员工考勤原始记录表', index=False, header=True) xlsx.save() xlsx.close()
def save_data(Working_Directory, Result_Directory, name_file, Duration_ON, Duration_OFF, Num_pixels_ON, Num_pixels_OFF): ## Excel data #Save duration Duration = list() Stimulus_Type = list() Matched_Pixels = list() Stimulus_Index = list() count = 0 for ii in xrange(size(Duration_ON, 0)): Duration.append(mean(Duration_ON[ii, :])) Matched_Pixels.append(Num_pixels_ON[ii, :]) Stimulus_Type.append(str(count + 1) + 'ON') Stimulus_Index.append(count) count = count + 1 for ii in xrange(size(Duration_OFF, 0)): Duration.append(mean(Duration_OFF[ii, :])) Matched_Pixels.append(Num_pixels_OFF[ii, :]) Stimulus_Type.append(str(count + 1) + 'OFF') Stimulus_Index.append(count) count = count + 1 ## For fish 23, change OFF to ON and save # Stimulus_Type[2] = '3ON' #Save matched_pixels Name_stimulus = get_list_of_stimulus_name(Working_Directory) Label_plane, Label_stimulus = label_stimulus(Name_stimulus, Stimulus_Type) Stim_type_all = repeat(Stimulus_Type, size(Matched_Pixels, 1)) Matched_Pixels_all = reshape(Matched_Pixels, (size(Matched_Pixels))) Name_stimulus_all = tile(Name_stimulus, size(Matched_Pixels, 0)) # Some data frames df1 = DataFrame({ 'Stimulus_Type': Stimulus_Type, 'TDuration': Duration }) #Only duration df2 = DataFrame( index=Stimulus_Index, columns=Name_stimulus) # pixels to concatenate with duration df3 = DataFrame(index=Stimulus_Type, columns=Name_stimulus) #pixels tandalone df4 = DataFrame({'Stimulus_Type':Stim_type_all, 'Pixels':Matched_Pixels_all,\ 'Label_plane':Label_plane, 'Label_stimulus':Label_stimulus, 'Original_Stim':Name_stimulus_all}) #label pixels with stimulus and z plane df4["Stimulus"] = df4.Label_stimulus.map(Label_Odor_reverse) for ii in xrange(0, size(Stimulus_Index)): df2.ix[ii] = Matched_Pixels[ii] df3.ix[ii] = Matched_Pixels[ii] df = concat([df1, df2], join='inner', axis=1) #Save to excel writer = ExcelWriter(Result_Directory + filesep + 'Classified_Results' + filesep + name_file + '.xlsx', engine='xlsxwriter') df.to_excel(writer, sheet_name='sheet1') writer.close() return df, df1, df3, df4
def return_tears_data(self, display=False, to_excel=False, filename=None): # A list to store all results to export to excel results_list = [ 'basic_results_table', 'cumulative_returns', 'vol_matched_returns', 'rolling_sharpe', 'rolling_sortino', 'annual_returns', 'monthly_returns', 'returns_table' ] rolling_sharpe = self.returns.rolling(126).apply( lambda x: self.sharpe(x)) rolling_sortino = self.returns.rolling(126).apply( lambda x: self.sortino(x)) # Total returns per year annual_returns = pd.DataFrame(self.aggregate_returns('annual')) * 100 # Distribution of monthly returns monthly_returns = self.returns.resample('1M').sum() * 100 cumulative_returns = self.cumulative_returns(starting_val=1) # Cumulative returns scaled to the benchmark volatility if self.benchmark is not None: bench_vol = self.benchmark.loc[self.returns.index].std() vol_matched_returns = (self.returns / self.returns.std()) * bench_vol vol_matched_returns = self.cumulative_returns(vol_matched_returns, starting_val=1) # Returns table showing the return for each month and year. returns_table = self.aggregate_returns(MONTHLY) returns_table = returns_table.unstack().round(3) # Table of all basic statistics. Sharpe, cagr, drawdown etc. basic_results_table = self.results_table(display) results_dict = {} if to_excel: writer = ExcelWriter(filename) for x in results_list: stat = eval(x) if type(stat) == pd.Series: stat.name = x stat = stat.to_frame() stat.to_excel(writer, x) writer.close() for x in results_list: try: results_dict[x] = eval(x) except: pass return results_dict
def save_peaks_excel(peakOnlyHdf5,xlsxFile): dsets = h5py.File(peakOnlyHdf5,'r') writer = ExcelWriter(xlsxFile) for _key in dsets.keys(): dset = dsets[_key] _df = pd.DataFrame(list(dset)) _df.to_excel(writer,_key,header=False, index=False) print(_key+'sheet is created') writer.save() writer.close()
def WriteToExcelSheet(template, filepath): """ Write a Pandas Spreadsheet Data Object to File :param template: Template to start from (with header) :param filepath: Location to write to """ writer = ExcelWriter(filepath) template.to_excel(writer, index=False) writer.save() writer.close()
def writeExcel(self): """Create xls for later analysis""" dir_path = os.path.dirname(os.path.realpath(__file__)) excelfile = os.path.join( dir_path, '..', 'output', 'offender.{}.xls'.format(time.strftime('%Y%m%d_%H%M%S'))) excelwriter = ExcelWriter(excelfile) self.model_df.to_excel(excelwriter, sheet_name='Model') self.agent_df.to_excel(excelwriter, sheet_name='Agent') excelwriter.close()
def saveFile2(x): name = list(x['姓名'])[0] xlsx = ExcelWriter(path + name + self.thisYear + '年' + self.thisMonth + '月员工考勤记录表.xlsx') temp = x[list_valuable] temp.to_excel(xlsx, '员工考勤记录表', index=False, header=True) ##index=false 不写行名(索引) ##header=true 写出列名,如果是给定字符串列表,则假定它是列表名称的别名 xlsx.save() xlsx.close()
def saveFile(x): dept = list(x['部门'])[0].strip().replace('/', '和').replace('?', '') if not os.path.exists(path + dept + self.thisYear + '年' + self.thisMonth + '月员工考勤记录表.xlsx'): # print (dept) xlsx = ExcelWriter(path + dept + self.thisYear + '年' + self.thisMonth + '月员工考勤记录表.xlsx') temp = x[list_valuable] temp.to_excel(xlsx, '员工考勤记录表', index=False, header=True) xlsx.save() xlsx.close()
def multiSample(self): ''' Single Thread and multi-account sample. ''' print('%d accounts to sample in total.' % len(self.acctli)) from openpyxl import load_workbook, Workbook from pandas import ExcelWriter wb = Workbook() wb.save(self.savedir) wb.close() wb = load_workbook(self.savedir) wter = ExcelWriter(self.savedir, engine='openpyxl') wter.book = wb self.logw('==multiSample==') # thread_list=[] for i in self.acctli: acct = Acct(i, self.chart.getna(i)) # th=MultiThread(acct,self) # thread_list.append(th) print('==start:%s==' % str(acct.accid)) self.logw('==start:%s==' % acct.accid) m_sample = self.getSample(acct) # one of the multi-samples. # try: # m_sample=self.getSample(acct) # one of the multi-samples. # except: # from pandas import DataFrame # m_sample=self.getSample(acct) # one of the multi-samples. # # m_sample= # print('sample for this account failed:') # print(acct.accid,'\t',acct.accna) # logline='\t'.join([acct.accid,acct.accna]) # self.logw(logline) # self.logw('sample for this account failed:') # lgline=acct.accid+'\t'+acct.accna # self.logw(lgline) # pass if m_sample.shape[0] == 0: self.logw('no sample for this account:') no_sample_line = ''.join( [str(acct.accid), '\t', str(acct.accna)]) self.logw(no_sample_line) pass else: m_sample.to_excel(wter, sheet_name=str(acct.accid + acct.accna)) # yield list(m_sample.loc[:,'glid'].drop_duplicates()) # print(m_sample) # 查看样本 wter.save() # yield m_sample print('==end:%s==' % str(acct.accid)) self.logw('==end:%s==' % acct.accid) wter.close() return
def saveFile2(x): name = list(x['姓名'])[0] xlsx = ExcelWriter(path + name + self.thisYear + '年' + self.thisMonth + '月员工考勤汇总表.xlsx') temp = x[['部门','考勤号码','姓名','出勤天数',\ '说明1','出差、会议、培训等天数','说明2','迟到次数','说明3','早退次数','说明4','缺勤天数','说明5',\ '法定+企业年休假天数','说明6','福利积点兑换年休假','说明7','病假','说明8','事假','说明9','产假','说明10',\ '其他假期','说明11','备注']] temp.to_excel(xlsx, '员工考勤汇总表', index=False, header=True) xlsx.save() xlsx.close()
def to_excel_file(dataframe, file_name, if_index): writer = ExcelWriter(file_name, engine='openpyxl') if not os.path.exists(file_name): dataframe.to_excel(writer, dataframe.name, index=if_index) else: writer.book = load_workbook(writer.path) dataframe.to_excel(excel_writer=writer, sheet_name=dataframe.name, index=if_index) writer.save() writer.close()
def writeExcelData(x,excelfile,sheetname,startrow,startcol): from pandas import DataFrame, ExcelWriter from openpyxl import load_workbook df=DataFrame(x) book = load_workbook(excelfile) writer = ExcelWriter(excelfile, engine='openpyxl') writer.book = book writer.sheets = dict((ws.title, ws) for ws in book.worksheets) df.to_excel(writer, sheet_name=sheetname,startrow=startrow-1, startcol=startcol-1, header=False, index=False) writer.save() writer.close()
def save_session(df, session_name): try: book = load_workbook('trans_record.xlsx') writer = ExcelWriter('trans_record.xlsx', engine='openpyxl') writer.book = book df.to_excel(writer, sheet_name=session_name, index=False) writer.save() writer.close() except: print( "ERROR: Cannot load/save to workbook. Ensure validity of files and that the sheet is not open." )
def export_to_excel(df_mortgage, df_deltas, df_us_treasury): # Export to Excel for use with openpyxl writer = ExcelWriter(at.file_name, engine='openpyxl') df_mortgage.to_excel(writer, index=False, sheet_name='mortgage_rates') df_deltas.to_excel(writer, index=False, sheet_name='treasury_delta_data') df_us_treasury.to_excel(writer, index=True, sheet_name='us_treasury_data') writer.save() writer.close()
def saveFile5(x): path = self.op + '所有管理序列原始数据拆分表/' isExists = os.path.exists(path) if not isExists: os.makedirs(path) xlsx = ExcelWriter(path + self.thisYear + '年' + self.thisMonth + '月' + '原始数据表.xlsx') temp = x[['userid', 'name', 'departure_x', 'date', 'output_time']] temp.columns = [['考勤号码', '姓名', '部门', '日期', '时间']] temp.to_excel(xlsx, '管理序列', index=False, header=True) xlsx.save() xlsx.close()
def core(filePath): pcMat = [] lpcMat = [] plasmalogenMat = [] #initializing pcMat, lpcMat and plasmalogen arrays data = pd.ExcelFile(filePath) #reading data from given path df = data.parse(data.sheet_names[0]) #creating dataFrame for index in range(0, len(df)): #traversing every row of dataFrame target = df.iloc[index][2] try: returnObject = findEnd(target) if returnObject == " PC": pcMat.append(df.iloc[index]) #if contains PC in end, add to pcMat elif returnObject == "LPC": lpcMat.append(df.iloc[index]) #if contains LPC in end, add to lpcMat else: plasmalogenMat.append(df.iloc[index]) #if contains Plasmalogen in end, add to plasmalogenMat except: pass pcDF = pd.DataFrame(pcMat, columns=df.columns) lpcDF = pd.DataFrame(lpcMat, columns=df.columns) plasmalogenDF = pd.DataFrame(plasmalogenMat, columns=df.columns) #converting arrays to pandas dataFrame writer = ExcelWriter('PythonExport.xlsx', engine='xlsxwriter') pcDF.to_excel(writer, index=False, startrow=0, startcol=0, sheet_name="PC_DataFrame") lpcDF.to_excel(writer, index=False, startrow=0, startcol=0, sheet_name="LPC_DataFrame") plasmalogenDF.to_excel(writer, index=False, startrow=0, startcol=0, sheet_name="Plasmalogen_DataFrame") writer.save() writer.close()
def saveFile1(x): dept = list(x['工作部门'])[0].strip().replace('/', '和').replace('?', '') if dept != '管理序列': xlsx = ExcelWriter(path + dept + self.thisYear + '年' + self.thisMonth + '月员工考勤记录表.xlsx') x.iloc[:, 1:].to_excel(xlsx, '员工考勤记录表', index=False, header=True) xlsx.save() xlsx.close()
def GetPrices(): """ Goes to the URL, Reads the CSV download link, and creates the CSV DataFrame""" url = "http://fundresearch.fidelity.com/mutual-funds/fidelity-funds-daily-pricing-yields/download" CSV_Import = urllib.request.urlopen(url).read() CSV = pd.read_csv(url, skiprows=3) """ Creates CSV File to be opened in Excel. This can be removed if you don't need Excel and you can just use CSV as the DataFrame """ File = 'DailyPrices' writer = ExcelWriter(str(File) + '.xlsx') CSV.to_excel(writer, 'DailyReport', index = False) writer.close() os.startfile(File + '.xlsx')
def save_data(Working_Directory, Result_Directory, name_file, Duration_ON, Duration_OFF, Num_pixels_ON, Num_pixels_OFF): ## Excel data #Save duration Duration = list() Stimulus_Type = list() Matched_Pixels = list() Stimulus_Index = list() count=0 for ii in xrange(size(Duration_ON,0)): Duration.append(mean(Duration_ON[ii,:])) Matched_Pixels.append(Num_pixels_ON[ii,:]) Stimulus_Type.append(str(count+1)+'ON') Stimulus_Index.append(count) count=count+1 for ii in xrange(size(Duration_OFF,0)): Duration.append(mean(Duration_OFF[ii,:])) Matched_Pixels.append(Num_pixels_OFF[ii,:]) Stimulus_Type.append(str(count+1)+'OFF') Stimulus_Index.append(count) count=count+1 ## For fish 23, change OFF to ON and save # Stimulus_Type[2] = '3ON' #Save matched_pixels Name_stimulus = get_list_of_stimulus_name(Working_Directory) Label_plane, Label_stimulus = label_stimulus(Name_stimulus,Stimulus_Type) Stim_type_all = repeat(Stimulus_Type, size(Matched_Pixels,1)) Matched_Pixels_all = reshape(Matched_Pixels, (size(Matched_Pixels))) Name_stimulus_all = tile(Name_stimulus, size(Matched_Pixels,0)) # Some data frames df1 = DataFrame({'Stimulus_Type':Stimulus_Type,'TDuration':Duration}) #Only duration df2 = DataFrame(index=Stimulus_Index, columns=Name_stimulus) # pixels to concatenate with duration df3 = DataFrame(index=Stimulus_Type, columns=Name_stimulus) #pixels tandalone df4 = DataFrame({'Stimulus_Type':Stim_type_all, 'Pixels':Matched_Pixels_all,\ 'Label_plane':Label_plane, 'Label_stimulus':Label_stimulus, 'Original_Stim':Name_stimulus_all}) #label pixels with stimulus and z plane df4["Stimulus"] = df4.Label_stimulus.map(Label_Odor_reverse) for ii in xrange(0,size(Stimulus_Index)): df2.ix[ii] = Matched_Pixels[ii] df3.ix[ii] = Matched_Pixels[ii] df = concat([df1,df2], join='inner', axis=1) #Save to excel writer = ExcelWriter(Result_Directory+ filesep+'Classified_Results'+filesep+name_file+ '.xlsx', engine='xlsxwriter') df.to_excel(writer, sheet_name='sheet1') writer.close() return df, df1, df3, df4
def build_and_send_email(self, data, options): date = timezone.now().date().strftime('%Y_%m_%d') if options['beta_recipients_from_db']: print 'beta recipients requested from db.' recipients = [a.email for a in WeeklyReportRecipients.objects.filter(is_active=True, is_beta=True)] elif options['recipients_from_db']: print 'recipients requested from db.' recipients = [a.email for a in WeeklyReportRecipients.objects.filter(is_active=True)] elif options['recipients']: print 'manual recipients requested.' recipients = options['recipients'] else: print 'no recipients requested.' recipients = settings.DEFAULT_WEEKLY_RECIPIENTS if not recipients: print 'no recipients in db.' recipients = settings.DEFAULT_WEEKLY_RECIPIENTS print 'recipients:', recipients message = EmailMessage(subject='Kikar Hamedina, Weekly Report: %s' % date, body='Kikar Hamedina, Weekly Report: %s.' % date, to=recipients) w = ExcelWriter('Weekly_report_%s.xlsx' % date) for datum in data: # csvfile = StringIO.StringIO() pd.DataFrame.from_dict(datum['content']).to_excel(w, sheet_name=datum['name']) w.save() w.close() # f = open(w.path, 'r', encoding='utf-8') message.attach_file(w.path) message.send()
elif df.loc[l[i], 'Signal'] == "Hold": df.loc[l[i], 'Investment'] = df.loc[l[i-1], 'Investment'] * (1 + df.loc[l[i], "Returns"]) print(df.head()) #Excess Return over S&P500 Column #for i in range(1,len(l)): # df.loc[l[i], 'Excess Return'] = df.loc[l[i], 'Investment'] - df.loc[l[i], 'S&P500 Investment'] file = ExcelWriter('Time1.xlsx') df.to_excel(file, 'Data') file.close() os.startfile('Time1.xlsx') df.plot(y = ['Investment', 'S&P500 Investment']) plt.show() print("Average Monday return: %s" % (Monday/MonCount)) print("Average Tuesday return: %s" % (Tuesday/TueCount)) print("Average Wednesday return: %s" % (Wednesday/WedCount)) print("Average Thursday return: %s" % (Thursday/ThuCount)) print("Average Friday return: %s" % (Friday/FriCount)) print("1 sample t-tests for each day to test significance of daily returns against 0 are as follows:")
print 'get balance' print 'retrieving marg' marg = gdx_to_df(gdx_file, 'marg') old_index = marg.index.names marg['C'] = [zone_dict[z] for z in marg.index.get_level_values('Z')] marg.set_index('C', append=True, inplace=True) marg = marg.reorder_levels(['C'] + old_index) marg.reset_index(inplace=True) marg = pivot_table(marg, 'marg', index=['Y', 'P', 'T'], columns=['C'], aggfunc=np.sum) print 'Writing balances.m to Excel' marg.to_excel(writer, na_rep=0.0, sheet_name='balance', merge_cells=False) writer.close() # wb = load_workbook(writefile) # ws1 = wb.active # gen_techn = list() # gen_energ = list() # gen_margc = list() # final = list() # for r in range (2,len(ws1.rows)+1,1): # #smaller loop for testing # #for r in range (2,100,1): # currentg = ws1.cell(row = r, column = 4).value # currente = ws1.cell(row = r, column = 5).value # currentc = ws1.cell(row = r, column = 6).value # if currentg not in gen_techn: # gen_techn.append(currentg)