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]