def setUpClass(cls):
cls.simple_df = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3],
b=[100, 200, 300],
c=[True, False, True]),
index=[1, 2, 3])
cls.simple_pm = pdpr.build_presentation_model(df=cls.simple_df)
cls.multi_df = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3, 0.4],
b=[100, 200, 300, 100],
c=[True, False, True, False]))
cls.multi_df.index = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1), ('b', 2)])
cls.multi_df.columns = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1)])
cls.multi_df.index.name = 'Sample_Index'
cls.multi_pm = pdpr.build_presentation_model(df=cls.multi_df)
cls.multi_df_1 = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3, 0.4],
b=[100, 200, 300, 100],
c=[True, False, True, False],
d=[10, 20, 40, 30]))
cls.multi_df_1.columns = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1),
('b', 2)])
cls.multi_df_1.index = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1), ('b', 2)])
cls.mult_pm_1 = pdpr.build_presentation_model(df=cls.multi_df_1)
cls.multi_df.index.name = 'Sample_Index'
def setUpClass(cls):
cls.simple_df = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3],
b=[100, 200, 300],
c=[True, False, True]),
index=[1, 2, 3])
cls.simple_pm = tc.build_presentation_model(df=cls.simple_df)
cls.multi_df = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3, 0.4],
b=[100, 200, 300, 100],
c=[True, False, True, False]))
cls.multi_df.index = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1), ('b', 2)])
cls.multi_df.columns = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1)])
cls.multi_df.index.name = 'Sample_Index'
cls.multi_pm = tc.build_presentation_model(df=cls.multi_df)
cls.multi_df_1 = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3, 0.4],
b=[100, 200, 300, 100],
c=[True, False, True, False],
d=[10, 20, 40, 30]))
cls.multi_df_1.columns = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1),
('b', 2)])
cls.multi_df_1.index = pd.MultiIndex.from_tuples([('a', 1), ('a', 2),
('b', 1), ('b', 2)])
cls.multi_pm_1 = tc.build_presentation_model(df=cls.multi_df_1)
# use for nesting - though we use same df, the instance
# of presentation model is new
cls.multi_pm_outer = tc.build_presentation_model(df=cls.multi_df_1)
cls.multi_pm_2 = tc.build_presentation_model(df=cls.multi_df_1)
cls.multi_df.index.name = 'Sample_Index'
def render_xlsx(cls):
# Prepare first data frame (same as in render_xlsx)
df = sample_names_data()
# build presentation model
klass_ = XLSXExample3
pm_all = tc.build_presentation_model(
df=df,
output_format='xlsx',
data_value_func=klass_.data_value_func(df),
data_style_func=klass_.data_style_func(df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func(df),
index_value_func=klass_.index_value_func,
index_name_style_func=klass_.index_name_style_func,
index_name_func=klass_.index_name_value_func)
male_df = top_names_for_year(gender='M')
pm_top_male = tc.build_presentation_model(
df=male_df,
output_format='xlsx',
data_value_func=klass_.data_value_func(male_df),
data_style_func=klass_.data_style_func(male_df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func(male_df),
index_value_func=klass_.index_value_func,
index_name_style_func=klass_.index_name_style_func,
index_name_func=klass_.index_name_value_func)
female_df = top_names_for_year(gender='F')
pm_top_female = tc.build_presentation_model(
df=female_df,
output_format='xlsx',
data_value_func=klass_.data_value_func(female_df),
data_style_func=klass_.data_style_func(female_df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func(female_df),
index_value_func=klass_.index_value_func,
index_name_style_func=klass_.index_name_style_func,
index_name_func=klass_.index_name_value_func)
layout = [pm_all, [pm_top_female, pm_top_male]]
# render to xlsx
tempdir = tempfile.gettempdir()
fp = os.path.join(tempdir, 'example3.xlsx')
print('Writing to ' + fp)
xlsxw.XLSXWriter.to_xlsx(layout,
output_fp=fp,
orientation='horizontal')
def render_html(cls):
# Prepare first data frame (same as in render_xlsx)
df = sample_names_data()
# build presentation model
klass_ = HTMLExample4
pm_all = tc.build_presentation_model(
df=df,
output_format='html',
data_value_func=klass_.data_value_func(df),
data_style_func=klass_.data_style_func(df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func,
index_value_func=klass_.index_value_func,
index_name_func=lambda _: 'Sample Data')
male_df = top_names_for_year(gender='M')
pm_top_male = tc.build_presentation_model(
df=male_df,
output_format='html',
data_value_func=klass_.data_value_func(male_df),
data_style_func=klass_.data_style_func(male_df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func,
index_value_func=klass_.index_value_func,
index_name_func=lambda _: 'Max by Year')
female_df = top_names_for_year(gender='F')
pm_top_female = tc.build_presentation_model(
df=female_df,
output_format='html',
data_value_func=klass_.data_value_func(female_df),
data_style_func=klass_.data_style_func(female_df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func,
index_value_func=klass_.index_value_func,
index_name_func=lambda _: 'Max by Year')
layout = [pm_all, [pm_top_female, pm_top_male]]
# render to xlsx
html = htmlw.HTMLWriter.to_html(layout,
border=1,
orientation='horizontal')
output_fp = os.path.join(tempfile.gettempdir(), 'example3.html')
print('Writing to =', output_fp)
with open(output_fp, 'w') as f:
f.write(html)
def get_multi_hierarchical_df_with_layouts(grid=True,
nested=False,
callback_func_cls=XlsxCallBackFunc):
df = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3, 0.4],
b=[100, 200, 300, 100],
c=[True, False, True, False]))
df.index = pd.MultiIndex.from_tuples([('a', 1), ('a', 2), ('b', 1),
('b', 2)])
df.columns = pd.MultiIndex.from_tuples([('a', 1), ('a', 2), ('b', 1)])
df.index.name = 'Sample_Index'
call_back_func_inst = callback_func_cls(df)
# excel calls begin here
layout_model = tbc.build_presentation_model(
df=df,
data_value_func=call_back_func_inst.data_value_func,
data_style_func=call_back_func_inst.data_style_func,
header_value_func=call_back_func_inst.header_value_func,
header_style_func=call_back_func_inst.header_style_func,
index_value_func=call_back_func_inst.index_value_func,
index_style_func=call_back_func_inst.index_style_func,
index_name_func=call_back_func_inst.index_name_value_func,
index_name_style_func=call_back_func_inst.index_name_style_func)
inner_df = df.copy()
inner_df.index = df.index.copy()
inner_df.index.name = 'inner_df'
layout_model_inner = tbc.build_presentation_model(
df=inner_df,
data_value_func=call_back_func_inst.data_value_func,
data_style_func=call_back_func_inst.data_style_func,
header_value_func=call_back_func_inst.header_value_func,
header_style_func=call_back_func_inst.header_style_func,
index_value_func=call_back_func_inst.index_value_func,
index_style_func=call_back_func_inst.index_style_func,
index_name_func=call_back_func_inst.index_name_value_func,
index_name_style_func=call_back_func_inst.index_name_style_func)
if nested:
layout_model.data.values.loc[('a', 2), ('a', 1)] = layout_model_inner
if grid:
layout = [[layout_model_inner, layout_model, layout_model],
[layout_model, layout_model_inner], [layout_model_inner]]
else:
layout = [layout_model]
return layout
def render_html(cls):
# Prepare first data frame (same as in render_xlsx)
data_df = load_names_data()
data_df = data_df[data_df['year'] >= 2000]
g = data_df.groupby(('year', 'gender'))
df = g.max()
klass_ = cls
pm = tc.build_presentation_model(
df=df,
output_format='html',
data_value_func=klass_.data_value_func(df),
data_style_func=klass_.data_style_func(df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func,
index_value_func=klass_.index_value_func,
index_name_func=klass_.index_name_value_func)
layout = [pm]
# render to xlsx
html = htmlw.HTMLWriter.to_html(layout, border=1)
output_fp = os.path.join(tempfile.gettempdir(), 'example4.html')
print('Writing to =', output_fp)
with open(output_fp, 'w') as f:
f.write(html)
def test_presentation_model_resolve_loc_multi_df_hide_index_and_columns(
self):
df = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3, 0.4],
b=[100, 200, 300, 100],
c=[True, False, True, False],
d=[10, 20, 40, 30]))
df.columns = pd.MultiIndex.from_tuples([('a', 1), ('a', 2), ('b', 1),
('b', 2)])
df.index = pd.MultiIndex.from_tuples([('a', 1), ('a', 2), ('b', 1),
('b', 2)])
pm = tc.build_presentation_model(df=df,
**{
"hide_index": True,
"hide_header": True
})
locs = ptm.PresentationLayoutManager.resolve_loc(pm).locs
self.assertIsNone(locs.header_loc, 'header_loc not None')
self.assertIsNone(locs.index_loc, 'index_loc not None')
# asserts in loop here is OK, since this is more
# an integrity check
for r, index_label in enumerate(locs.data_loc.index):
for c, column in enumerate(locs.data_loc.columns):
offset = (r, c, r, c)
self.assertEqual(locs.data_loc.loc[index_label, column],
offset)
def render_xlsx(cls):
# Prepare first data frame (same as in render_xlsx)
data_df = load_names_data()
data_df = data_df[data_df['year'] >= 2000]
g = data_df.groupby(('year', 'gender'))
df = g.max()
klass_ = cls
pm = tc.build_presentation_model(
df=df,
output_format='xlsx',
#data_value_func=None, # use default
data_style_func=klass_.data_style_func(df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func(df),
index_value_func=klass_.index_value_func,
index_name_style_func=klass_.index_name_style_func,
index_name_func=klass_.index_name_value_func)
layout = [pm]
# render to xlsx
tempdir = tempfile.gettempdir()
fp = os.path.join(tempdir, 'example4.xlsx')
print('Writing to ' + fp)
xlsxw.XLSXWriter.to_xlsx(layout,
output_fp=fp,
orientation='horizontal')
def basic_example2():
df = pd.DataFrame(dict(a=[10, 20, 30, 40, 50], b=[0.1, 0.9,0.2, 0.6,0.3]), index=[1,2,3,4,5])
def style_func(idx, col):
if col == 'b':
return OpenPyxlStyleHelper.get_style(number_format='0.00%')
else:
# for 'a' we do dollar format
return OpenPyxlStyleHelper.get_style(number_format='$#,##.00')
# create a presentation model
# note the OpenPyxlStyleHelper function available in xlsx_styles module. But a return value of style function
# can be any dict whose keys are attributes of the OpenPyxl cell object.
presentation_model = build_presentation_model(
df=df,
data_value_func=lambda idx, col: df.loc[idx, col] * 10 if col == 'a' else df.loc[idx, col],
data_style_func=style_func,
header_value_func=lambda node: node.value.capitalize(),
header_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_value_func=lambda node: node.value * 100,
index_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_name_func=lambda _: 'Basic Example',
index_name_style_func=lambda _: OpenPyxlStyleHelper.default_header_style())
# create a layout, which is usually a nested list of presentation models
layout = [presentation_model]
# render to xlsx
output_fp = os.path.join(tempfile.gettempdir(), 'basic_example2.xlsx')
OpenPyxlCompositor.to_xlsx(layout=layout, output_fp=output_fp)
def get_simple_df_with_layout(grid=False,
nested=False,
callback_func_cls=XlsxCallBackFunc):
df = pd.DataFrame(data=dict(a=[0.1, 0.2, 0.3],
b=[100, 200, -300],
c=[True, False, True]),
index=[100, 200, 300])
df.index.name = 'Sample_Index'
call_back_func_inst = callback_func_cls(df)
layout_model = tbc.build_presentation_model(
df=df,
data_value_func=call_back_func_inst.data_value_func,
data_style_func=call_back_func_inst.data_style_func,
header_value_func=call_back_func_inst.header_value_func,
header_style_func=call_back_func_inst.header_style_func,
index_value_func=call_back_func_inst.index_value_func,
index_style_func=call_back_func_inst.index_style_func,
index_name_func=call_back_func_inst.index_name_value_func,
index_name_style_func=call_back_func_inst.index_name_style_func)
inner_df = df.copy()
inner_df.index = df.index.copy()
inner_df.loc[400] = (1, 2, 3)
#inner_df.index = [1, 2, 3]
inner_df.index.name = 'inner_df'
call_back_func_inst = callback_func_cls(inner_df)
layout_model_inner = tbc.build_presentation_model(
df=inner_df,
data_value_func=call_back_func_inst.data_value_func,
data_style_func=call_back_func_inst.data_style_func,
header_value_func=call_back_func_inst.header_value_func,
header_style_func=call_back_func_inst.header_style_func,
index_value_func=call_back_func_inst.index_value_func,
index_style_func=call_back_func_inst.index_style_func,
index_name_func=call_back_func_inst.index_name_value_func,
index_name_style_func=call_back_func_inst.index_name_style_func)
if nested:
layout_model.data.values.loc[200, 'b'] = layout_model_inner
if grid:
return [[layout_model, layout_model_inner], layout_model]
return [layout_model]
def layout_example1():
df = pd.DataFrame(dict(a=[10, 20, 30, 40, 50], b=[0.1, 0.9, 0.2, 0.6,
0.3]),
index=[1, 2, 3, 4, 5])
def style_func(idx, col):
if col == 'b':
return OpenPyxlStyleHelper.get_style(number_format='0.00%')
else:
# for 'a' we do dollar format
return OpenPyxlStyleHelper.get_style(number_format='$#,##.00')
# create a presentation model
# note the OpenPyxlStyleHeloer function available in xlsx_styles module. But a return value of style function
# can be any dict whose keys are attributes of the OpenPyxl cell object.
presentation_model = build_presentation_model(
df=df,
data_value_func=lambda idx, col: df.loc[idx, col] * 10
if col == 'a' else df.loc[idx, col],
data_style_func=style_func,
header_value_func=lambda node: node.value.capitalize(),
header_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_value_func=lambda node: node.value * 100,
index_style_func=lambda _: OpenPyxlStyleHelper.default_header_style(),
index_name_func=lambda _: 'Basic Example',
index_name_style_func=lambda _: OpenPyxlStyleHelper.
default_header_style())
# start_layout_code_1
# create a layout, which is usually a nested list of presentation models
layout = [[presentation_model], [[presentation_model],
[presentation_model]]]
# render to xlsx
output_fp = os.path.join(tempfile.gettempdir(),
'layout_vertical_example1.xlsx')
# the default value for orientation is 'vertical'
XLSXWriter.to_xlsx(layout, output_fp=output_fp, orientation='vertical')
output_fp = os.path.join(tempfile.gettempdir(),
'layout_horizontal_example1.xlsx')
XLSXWriter.to_xlsx(layout, output_fp=output_fp, orientation='horizontal')
print('Writing xlsx file=', output_fp)
# mutiple nesting
layout_complex = [
presentation_model,
[presentation_model, [presentation_model, presentation_model]]
]
output_fp = os.path.join(tempfile.gettempdir(),
'layout_complex_example1.xlsx')
XLSXWriter.to_xlsx(layout_complex,
output_fp=output_fp,
orientation='vertical')
print('Writing xlsx file=', output_fp)
def _create_presentation_model(df, callback_funcs_cls, slow=0):
call_back_func_inst = callback_funcs_cls(df)
if slow == 0:
presentation_model = tbc.build_presentation_model(
df=df,
data_value_func=None,
data_style_func=None,
column_style_func=call_back_func_inst.data_style_func_column_level,
header_value_func=call_back_func_inst.header_value_func,
header_style_func=call_back_func_inst.header_style_func,
index_value_func=call_back_func_inst.index_value_func,
index_style_func=call_back_func_inst.index_style_func,
index_name_func=call_back_func_inst.index_name_value_func,
index_name_style_func=call_back_func_inst.index_name_style_func)
elif slow == 1:
presentation_model = tbc.build_presentation_model(
df=df,
data_value_func=None,
column_style_func=call_back_func_inst.data_style_func_column_level,
header_value_func=call_back_func_inst.header_value_func,
header_style_func=call_back_func_inst.header_style_func,
index_value_func=call_back_func_inst.index_value_func,
index_style_func=call_back_func_inst.index_style_func,
index_name_func=call_back_func_inst.index_name_value_func,
index_name_style_func=call_back_func_inst.index_name_style_func)
elif slow == 2:
presentation_model = tbc.build_presentation_model(
df=df,
data_value_func=call_back_func_inst.data_value_func,
data_style_func=call_back_func_inst.data_style_func,
header_value_func=call_back_func_inst.header_value_func,
header_style_func=call_back_func_inst.header_style_func,
index_value_func=call_back_func_inst.index_value_func,
index_style_func=call_back_func_inst.index_style_func,
index_name_func=call_back_func_inst.index_name_value_func,
index_name_style_func=call_back_func_inst.index_name_style_func)
return [presentation_model]
def basic_example3():
df = pd.DataFrame(
dict(a=[10, 20, 30, 40],
b=[0.1, 0.9, 0.2, 0.6],
d=[50, 60, 70, 80],
e=[200, 300, 400, 500]))
df.columns = pd.MultiIndex.from_tuples([('A', 'x'), ('A', 'y'), ('B', 'x'),
('B', 'y')])
df.index = pd.MultiIndex.from_tuples([(1, 100), (1, 200), (2, 100),
(2, 200)])
print(df)
def index_style_func(node):
# node.key here could be one of (1,), (1, 100), (2,), (2, 100), (2, 200)
bg_color = 'FFFFFF'
if node.key == (1, ) or node.key == (2, ):
bg_color = '9E80B8'
elif node.key[1] == 100:
bg_color = '4F90C1'
elif node.key[1] == 200:
bg_color = '6DC066'
return OpenPyxlStyleHelper.get_style(bg_color=bg_color)
def header_style_func(node):
bg_color = 'FFFFFF'
if node.key == ('A', ) or node.key == ('B', ):
bg_color = '9E80B8'
elif node.key[1] == 'x':
bg_color = '4F90C1'
elif node.key[1] == 'y':
bg_color = '6DC066'
return OpenPyxlStyleHelper.get_style(bg_color=bg_color)
# create a presentation model
# note the OpenPyxlStyleHeloer function available in xlsx_styles module. But a return value of style function
# can be any dict whose keys are attributes of the OpenPyxl cell object.
presentation_model = build_presentation_model(
df=df,
index_style_func=index_style_func,
header_style_func=header_style_func,
index_name_func=lambda _: 'Multi-Hierarchy Example')
# create a layout, which is usually a nested list of presentation models
layout = [presentation_model]
# render to xlsx
output_fp = os.path.join(tempfile.gettempdir(), 'basic_example3.xlsx')
XLSXWriter.to_xlsx(layout, output_fp=output_fp)
def render_xlsx(cls):
'''
Render the df to a xlsx file.
'''
# load data
df = sample_names_data()
# build presentation model
pm = tc.build_presentation_model(df=df, output_format='xlsx')
# render to xlsx
tempdir = tempfile.gettempdir()
fp = os.path.join(tempdir, 'example1.xlsx')
layout = [pm]
print('Writing to ' + fp)
xlsxw.XLSXWriter.to_xlsx(layout, output_fp=fp)
def render_html(cls):
# load data
df = load_names_data()
df = df[:100]
# build presentation model
pm = tc.build_presentation_model(df=df, output_format='html')
# render to xlsx
tempdir = tempfile.gettempdir()
fp = os.path.join(tempdir, 'example_1.html')
layout = [pm]
print('Writing to ' + fp)
html = htmlw.HTMLWriter.to_html(layout, border=1)
output_fp = os.path.join(tempfile.gettempdir(), 'example1.html')
with open(output_fp, 'w') as f:
f.write(html)
def render_html(cls):
# load data
df = sample_names_data()
# build presentation model
klass_ = HTMLExample2
pm = tc.build_presentation_model(
df=df,
output_format='html',
data_value_func=klass_.data_value_func(df),
data_style_func=klass_.data_style_func(df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func,
index_value_func=klass_.index_value_func,
index_name_func=klass_.index_name_value_func)
layout = [pm]
html = htmlw.HTMLWriter.to_html(layout, border=1)
output_fp = os.path.join(tempfile.gettempdir(), 'example2.html')
print('Writing to =', output_fp)
with open(output_fp, 'w') as f:
f.write(html)
def render_xlsx(cls):
# load data
df = sample_names_data()
# build presentation model
klass_ = XLSXExample2
pm = tc.build_presentation_model(
df=df,
output_format='xlsx',
data_value_func=klass_.data_value_func(df),
data_style_func=klass_.data_style_func(df),
header_value_func=klass_.header_value_func,
header_style_func=klass_.header_style_func,
index_style_func=klass_.index_style_func(df),
index_value_func=klass_.index_value_func,
index_name_style_func=klass_.index_name_style_func,
index_name_func=klass_.index_name_value_func)
# render to xlsx
tempdir = tempfile.gettempdir()
fp = os.path.join(tempdir, 'example2.xlsx')
layout = [pm]
print('Writing to ' + fp)
xlsxw.XLSXWriter.to_xlsx(layout, output_fp=fp)