def _check_extension_sheets(self, ext):
path = '__tmp_to_excel_from_excel_sheets__.' + ext
self.frame['A'][:5] = nan
self.frame.to_excel(path, 'test1')
self.frame.to_excel(path, 'test1', cols=['A', 'B'])
self.frame.to_excel(path, 'test1', header=False)
self.frame.to_excel(path, 'test1', index=False)
# Test writing to separate sheets
writer = ExcelWriter(path)
self.frame.to_excel(writer, 'test1')
self.tsframe.to_excel(writer, 'test2')
writer.save()
reader = ExcelFile(path)
recons = reader.parse('test1', index_col=0)
tm.assert_frame_equal(self.frame, recons)
recons = reader.parse('test2', index_col=0)
tm.assert_frame_equal(self.tsframe, recons)
np.testing.assert_equal(2, len(reader.sheet_names))
np.testing.assert_equal('test1', reader.sheet_names[0])
np.testing.assert_equal('test2', reader.sheet_names[1])
os.remove(path)
def to_excel(self, path, na_rep=''):
"""
Write each DataFrame in Panel to a separate excel sheet
Parameters
----------
excel_writer : string or ExcelWriter object
File path or existing ExcelWriter
na_rep : string, default ''
Missing data representation
"""
from pandas.io.parsers import ExcelWriter
writer = ExcelWriter(path)
for item, df in self.iteritems():
name = str(item)
df.to_excel(writer, name, na_rep=na_rep)
writer.save()
def export_to(self, file_path, batchsize=1000):
self.xls_writer = ExcelWriter(file_path)
# get record count
record_count = self._query_mongo(count=True)
# query in batches and for each batch create an XLSDataFrameWriter and
# write to existing xls_writer object
start = 0
header = True
while start < record_count:
cursor = self._query_mongo(self.filter_query,
start=start,
limit=batchsize)
data = self._format_for_dataframe(cursor)
# write all cursor's data to their respective sheets
for section_name, section in self.sections.iteritems():
records = data[section_name]
# TODO: currently ignoring nested repeats
# so ignore sections that have 0 records
if len(records) > 0:
# use a different group delimiter if needed
columns = section["columns"]
if self.group_delimiter != DEFAULT_GROUP_DELIMITER:
columns = [
self.group_delimiter.join(col.split("/"))
for col in columns
]
columns = columns + self.EXTRA_COLUMNS
writer = XLSDataFrameWriter(records, columns)
writer.write_to_excel(self.xls_writer,
section_name,
header=header,
index=False)
header = False
# increment counter(s)
start += batchsize
time.sleep(0.1)
self.xls_writer.save()
# big = big.drop('AnnStaticRet', 1)
# big = big.drop('AnnCapitalRet', 1)
# big['AnnStaticRet'] = new_ind.AnnStaticRet.values
# big['AnnCapitalRet'] = new_ind.AnnCapitalRet.values
today_str = str(str(month) + str(day) + str(year))
big = big.rename(columns={'Last': 'OptionPrice', 'industry': 'Industry'})
xlsx = '.xlsx'
csv = '.csv'
file_name = 'All_covered_call' + today_str
sectors = big.Sector.unique().astype(str)
name_xl = file_name + xlsx
writer = ExcelWriter(name_xl)
big.to_excel(writer, sheet_name='All Sectors')
summary = big.groupby(['Sector', 'Industry']).mean()
summary.to_excel(writer, sheet_name='Sector Summary')
for i in sectors:
to_save = big[big.Sector == i]
name = i.replace('/', '-')
to_save.to_excel(writer, sheet_name=name)
writer.save()
name_cs = file_name + csv
big.to_csv(name_cs)
temp_frame2 = temp_frame2.dropna()
if month == 0:
final_frame = final_frame.join(temp_frame2, how='right')
else:
final_frame = pd.concat([final_frame, temp_frame2])
except:
pass
print 'Just finished ticker %s of %s' % (ticker, num_tickers)
today = str(
str(dt.datetime.now().month) + str(dt.datetime.now().day) +
str(dt.datetime.now().year))
file_name = 'NASDAQ_covered_call' + today
xlsx = '.xlsx'
csv = '.csv'
name_xl = file_name + xlsx
name_cs = file_name + csv
writer = ExcelWriter(file_name)
final_frame.to_excel(writer, sheet_name='Covered Call')
writer.save()
final_frame.to_csv(name_cs)
end_time = time()
elapsed_time = end_time - start_time
print elapsed_time
