def dataframe_to_ipysheet(dataframe):
columns = dataframe.columns.tolist()
rows = dataframe.index.tolist()
cells = []
cells.append(
ips.Cell(value=[p.strftime('%Y-%m-%d') for p in dataframe.index],
row_start=0,
row_end=len(rows) - 1,
column_start=0,
column_end=0,
type='date',
date_format='YYYY-MM-DD',
squeeze_row=False,
squeeze_column=True))
idx = 1
for c in columns:
cells.append(
ips.Cell(value=dataframe[c].values,
row_start=0,
row_end=len(rows) - 1,
column_start=idx,
column_end=idx,
type='numeric',
numeric_format='0.00',
squeeze_row=False,
squeeze_column=True))
idx += 1
return ips.Sheet(rows=len(rows),
columns=len(columns) + 1,
cells=cells,
row_headers=False,
column_headers=['period'] +
[str(header) for header in columns])
def create_fwd_input_sheet(dates, prices, num_rows):
if len(dates) > num_rows:
raise ValueError(
'Length of dates cannot exceed number of rows {}.'.format(
num_rows))
if len(prices) > num_rows:
raise ValueError(
'Length of prices cannot exceed number of rows {}.'.format(
num_rows))
dates = dates + [None] * (num_rows - len(dates))
prices = prices + [None] * (num_rows - len(prices))
dates_cells = ips.Cell(value=dates,
row_start=0,
row_end=len(dates) - 1,
column_start=0,
column_end=0,
type='date',
date_format=date_format,
squeeze_row=False,
squeeze_column=True)
prices_cells = ips.Cell(value=prices,
row_start=0,
row_end=len(prices) - 1,
column_start=1,
column_end=1,
type='numeric',
numeric_format='0.000',
squeeze_row=False,
squeeze_column=True)
cells = [dates_cells, prices_cells]
return ips.Sheet(rows=len(dates),
columns=2,
cells=cells,
row_headers=False,
column_headers=['fwd_start', 'price'])
def create_ratchets_sheet(dates, inventories, inject_rates, withdraw_rates,
num_rows):
if len(inventories) > num_rows:
raise ValueError(
'Length of inventories in ratchets cannot exceed number of rows {}.'
.format(num_rows))
dates = dates + [''] * (num_rows - len(dates))
inventories = inventories + [''] * (num_rows - len(inventories))
inject_rates = inject_rates + [''] * (num_rows - len(inject_rates))
withdraw_rates = withdraw_rates + [''] * (num_rows - len(withdraw_rates))
dates_cells = ips.Cell(value=dates,
row_start=0,
row_end=len(dates) - 1,
column_start=0,
column_end=0,
type='date',
date_format=date_format,
squeeze_row=False,
squeeze_column=True)
inventory_cells = create_numeric_col(inventories, 1)
inject_rate_cells = create_numeric_col(inject_rates, 2)
withdraw_rate_cells = create_numeric_col(withdraw_rates, 3)
cells = [
dates_cells, inventory_cells, inject_rate_cells, withdraw_rate_cells
]
return ips.Sheet(
rows=len(dates),
columns=4,
cells=cells,
row_headers=False,
column_headers=['date', 'inventory', 'inject_rate', 'withdraw_rate'])
def _df_to_sheet(self, df: pd.DataFrame) -> sh.Sheet:
"""
Transforms a pandas DataFrame into a ipysheet Sheet.
The cells are set to read only except for the values.
:param df: the pandas DataFrame to be converted
:return: the equivalent ipysheet Sheet
"""
if not df.empty:
# Adapted from_dataframe() method of ipysheet
columns = df.columns.tolist()
rows = df.index.tolist()
cells = []
read_only_cells = ["Name", "Unit", "Description", "Value"]
style = self._cell_styling(df)
row_idx = 0
for r in rows:
col_idx = 0
for c in columns:
value = df.loc[r, c]
if c in read_only_cells:
read_only = True
numeric_format = None
else:
read_only = False
# TODO: make the number of decimals depend on the module ?
# or chosen in the ui by the user
numeric_format = "0.000"
# If no output file is provided make it clearer for the user
if c == "Value" and self._MISSING_OUTPUT_FILE:
value = "-"
cells.append(
sh.Cell(
value=value,
row_start=row_idx,
row_end=row_idx,
column_start=col_idx,
column_end=col_idx,
numeric_format=numeric_format,
read_only=read_only,
style=style[(r, c)],
))
col_idx += 1
row_idx += 1
sheet = sh.Sheet(
rows=len(rows),
columns=len(columns),
cells=cells,
row_headers=[str(header) for header in rows],
column_headers=[str(header) for header in columns],
)
else:
sheet = sh.sheet(rows=0, columns=0)
return sheet
def create_numeric_col(values, col_num):
return ips.Cell(value=values,
row_start=0,
row_end=len(values) - 1,
column_start=col_num,
column_end=col_num,
type='numeric',
numeric_format='0.000',
squeeze_row=False,
squeeze_column=True)
def test_cell_values():
cell = ipysheet.cell(0, 0, value=True)
assert cell.value is True
assert cell.type == 'checkbox'
cell = ipysheet.cell(0, 0, value=1.2)
assert cell.value == 1.2
assert cell.type == 'numeric'
cell = ipysheet.cell(0, 0, value=1)
assert cell.value == 1
assert cell.type == 'numeric'
cell = ipysheet.Cell(value='1.2')
assert cell.value == '1.2'
assert cell.type is None
cell = ipysheet.row(0, [True, False])
assert cell.value == [True, False]
assert cell.type == 'checkbox'
cell = ipysheet.row(0, [0, 1.2])
assert cell.value == [0, 1.2]
assert cell.type == 'numeric'
cell = ipysheet.row(0, [0, 1])
assert cell.value == [0, 1]
assert cell.type == 'numeric'
cell = ipysheet.row(0, ['a', 'b'])
assert cell.value == ['a', 'b']
assert cell.type == 'text'
cell = ipysheet.row(0, [True, 0])
assert cell.type == 'numeric'
cell = ipysheet.row(0, [True, 'bla'])
assert cell.type is None
cell = ipysheet.cell(0, 0, choice=['a', 'b'])
assert cell.type == 'dropdown'
def test_value_types_serialize(kernel):
# test scalars, list, ndarray and pandas series
# this test duplicates a bit from test_cell_range and test_row_and_column
x = np.arange(3)
y = x**2
xr = x[::-1]
x_list = x.tolist()
xr_list = xr.tolist()
matrix = np.array([x, y]).T
matrix_list = matrix.tolist()
matrix_r = matrix[::, ::-1]
matrix_list_r = matrix_r.tolist()
df = pd.DataFrame({'x': x})
df['y'] = y
assert not isinstance(df.x, np.ndarray)
cell_scalar = ipysheet.Cell()
cell_vector = ipysheet.Cell(row_start=0, row_end=2, squeeze_row=False)
cell_matrix = ipysheet.Cell(row_start=0,
row_end=2,
column_start=0,
column_end=1,
squeeze_row=False,
squeeze_column=False)
cell_scalar.comm.kernel = kernel
cell_vector.comm.kernel = kernel
cell_matrix.comm.kernel = kernel
# scalar
cell_scalar.value = 1
assert cell_scalar.value == 1
cell_scalar.value = 1.1
assert cell_scalar.value == 1.1
cell_scalar.value = True
assert cell_scalar.value is True
cell_scalar.value = 'voila'
assert cell_scalar.value == 'voila'
cell_scalar.value = np.int64(1)
assert cell_scalar.value == 1
# vector
cell_vector.value = x_list
assert cell_vector.value == x_list
cell_vector.set_state({'value': xr_list})
assert cell_vector.value == xr_list
# vector+numpy
cell_vector.value = x
assert isinstance(cell_vector.value, np.ndarray)
assert cell_vector.value.tolist() == x.tolist()
# we'd like it to stay a ndarray
cell_vector.set_state({'value': xr_list})
assert isinstance(cell_vector.value, np.ndarray)
assert cell_vector.value.tolist() == xr_list
# vector+series
cell_vector.value = df.x
assert cell_vector.value.tolist() == df.x.tolist()
assert isinstance(cell_vector.value, pd.Series)
# we'd like it to stay a series
cell_vector.set_state({'value': x_list})
assert isinstance(cell_vector.value, pd.Series)
assert cell_vector.value.tolist() == x_list
with pytest.raises(ValueError):
cell_vector.value = 1
# matrix
cell_matrix.value = matrix_list
assert cell_matrix.value == matrix_list
# matrix+numpy
cell_matrix.value = matrix
assert isinstance(cell_matrix.value, np.ndarray)
assert cell_matrix.value.tolist() == matrix_list
# we'd like it to stay a ndarray
cell_matrix.set_state({'value': matrix_list_r})
assert isinstance(cell_matrix.value, np.ndarray)
assert cell_matrix.value.tolist() == matrix_list_r
# matrix+dataframe
cell_matrix.value = df # pandas to_numpy->tolist() gives the transposed result
assert adapt_value(cell_matrix.value) == matrix_list
assert isinstance(cell_matrix.value, pd.DataFrame)
# we'd like it to stay a dataframe
cell_matrix.set_state({'value': matrix_list})
assert isinstance(cell_matrix.value, pd.DataFrame)
assert adapt_value(cell_matrix.value) == matrix_list
with pytest.raises(ValueError):
cell_matrix.value = 1
with pytest.raises(ValueError):
cell_matrix.value = x
# make sure we can still set the widgets, and they serialize
button = widgets.Button()
slider = widgets.FloatSlider()
cell_scalar.value = button
cell_vector.value = [slider, button, button]
cell_vector.set_state({
'value': [
'IPY_MODEL_' + button.model_id, 'IPY_MODEL_' + slider.model_id,
'IPY_MODEL_' + slider.model_id
]
})
assert cell_vector.value == [button, slider, slider]
# even when originally a ndarray
cell_vector.value = x
cell_vector.set_state({
'value': [
'IPY_MODEL_' + button.model_id, 'IPY_MODEL_' + button.model_id,
'IPY_MODEL_' + slider.model_id
]
})
assert cell_vector.value == [button, button, slider]
