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)
