def test_cell_add():
sheet1 = ipysheet.sheet()
sheet2 = ipysheet.sheet()
ipysheet.cell(0, 0, value='1')
assert len(sheet1.cells) == 0
assert len(sheet2.cells) == 1
ipysheet.sheet(sheet1)
ipysheet.cell(0, 0, value='2')
ipysheet.cell(0, 1, value='2')
assert len(sheet1.cells) == 2
assert len(sheet2.cells) == 1
with ipysheet.hold_cells():
ipysheet.cell(1, 0, value='3')
ipysheet.cell(1, 1, value='4')
assert len(sheet1.cells) == 2
assert len(sheet2.cells) == 1
assert len(sheet1.cells) == 4
assert len(sheet2.cells) == 1
# nested hold cells
sheet1 = ipysheet.sheet()
with ipysheet.hold_cells():
with ipysheet.hold_cells():
ipysheet.cell(1, 0, value='3')
ipysheet.cell(1, 1, value='4')
assert len(sheet1.cells) == 0
assert len(sheet1.cells) == 0
assert len(sheet1.cells) == 2
def test_renderer():
ipysheet.sheet()
renderer = ipysheet.renderer('code', 'name')
assert renderer.code == 'code'
assert renderer.name == 'name'
def f(x):
somefunction(x)
renderer = ipysheet.renderer(f, 'name2')
assert "somefunction" in renderer.code
assert renderer.name == 'name2'
def test_calculation():
ipysheet.sheet()
a = ipysheet.cell(0, 0, value=1)
b = ipysheet.cell(0, 0, value=2)
c = ipysheet.cell(0, 0, value=0)
@ipysheet.calculation(inputs=[a, (b, 'value')], output=c)
def add(a, b): # pylint: disable=unused-variable
return a + b
assert c.value == 3
a.value = 10
assert c.value == 10 + 2
b.value = 20
assert c.value == 10 + 20
a.value = 1
b.value = 2
assert c.row_start == 0
@ipysheet.calculation(inputs=[a, b], output=(c, 'type'))
def add2(a, b): # pylint: disable=unused-variable
return 'abcdefg'[a + b]
assert c.type == 'd'
b.value = 1
assert c.type == 'c'
ipysheet.sheet()
a = ipysheet.cell(0, 0, value=1)
b = ipysheet.cell(0, 0, value=widgets.IntSlider(value=2))
c = widgets.IntSlider(max=0)
d = ipysheet.cell(0, 0, value=1)
@ipysheet.calculation(inputs=[a, (b, 'value'), (c, 'max')], output=d)
def add3(a, b, c): # pylint: disable=unused-variable
return a + b + c
assert d.value == 3
a.value = 10
assert d.value == 10 + 2
b.value.value = 20
assert d.value == 10 + 20
c.max = 30
assert d.value == 10 + 20 + 30
b.value = widgets.IntSlider(value=2)
assert d.value == 10 + 2 + 30
b.value = 20
assert d.value == 10 + 20 + 30
a.value = widgets.IntSlider(value=100)
assert d.value == 100 + 20 + 30
a.value.value = 10
assert d.value == 10 + 20 + 30
def test_cell_range():
ipysheet.sheet(rows=3, columns=4)
# [row][column]
ipysheet.cell_range([[0, 1]]) # 1 row, 2 columns
ipysheet.cell_range([[0], [2]]) # 2 rows, 1 columns
ipysheet.cell_range([[0, 1], [2, 3]]) # 2 rows, 2 columns
ipysheet.cell_range([[0, 1], [2, 3], [4, 5]]) # 3 rows, 2 columns
ipysheet.cell_range([[0, 1, 9], [2, 3, 9], [4, 5, 9]]) # 3 rows, 3 columns
ipysheet.cell_range([[0, 1, 9]], column_end=2) # 3 rows, 3 columns
ipysheet.cell_range([[0, 1, 9]], column_start=1) # 1 rows, 3 columns
with pytest.raises(ValueError):
ipysheet.cell_range([[0, 1], [2, 3], [4, 5], [6,
7]]) # 4 rows, 2 columns
with pytest.raises(ValueError):
ipysheet.cell_range([[0, 1, 2, 3, 4], [2, 3, 4, 5, 6],
[3, 4, 5, 6, 7]]) # 3 rows, 5 columns
with pytest.raises(ValueError):
ipysheet.cell_range([[0, 1, 2, 3, 4], [2], [3, 4, 5, 6,
7]]) # not well shaped
with pytest.raises(ValueError):
ipysheet.cell_range([]) # empty rows
with pytest.raises(ValueError):
ipysheet.cell_range([[], []]) # empty columns
value = [[0, 1], [2, 3], [4, 5]]
valueT = [[0, 2, 4], [1, 3, 5]] # it's transpose
assert value == transpose(valueT)
r = ipysheet.cell_range(value) # 3 rows, 2 columns
with pytest.raises(ValueError):
r.value = 1
with pytest.raises(ValueError):
r.value = [1, 2, 3]
with pytest.raises(ValueError):
r.value = [[1, 2]]
assert r.value == transpose(valueT)
rT = ipysheet.cell_range(valueT, transpose=True) # 3 rows, 2 columns
with pytest.raises(ValueError):
rT.value = 1
with pytest.raises(ValueError):
rT.value = [1, 2, 3]
with pytest.raises(ValueError):
rT.value = [[1, 2]]
rT.value = transpose(value)
assert rT.value == transpose(value)
sheet = ipysheet.sheet(rows=3, columns=4)
assert len(sheet.cells) == 0
with ipysheet.hold_cells():
ipysheet.cell_range(value)
ipysheet.cell_range(value)
assert len(sheet.cells) == 0
assert len(sheet.cells) == 2
def test_calculation():
ipysheet.sheet()
a = ipysheet.cell(0, 0, value=1)
b = ipysheet.cell(0, 0, value=2)
c = ipysheet.cell(0, 0, value=0)
@ipysheet.calculation(inputs=[a, b], output=c)
def add(a, b):
return a + b
assert c.value == 3
a.value = 10
assert c.value == 10 + 2
b.value = 20
assert c.value == 10 + 20
def build_sheet_out(self):
"""
"""
sheet = ipysheet.sheet(rows=1 + len(self.li_op_key),
columns=2,
column_headers=False,
row_headers=False)
sheet.stretch_headers = 'none'
sheet.column_width = [110, 110]
style_header = {
'backgroundColor': '#d0d3d4',
'fontWeight': 'bold',
'textAlign': 'right',
'color': 'black'
}
style_title_output = {'textAlign': 'right', 'color': 'black'}
c = ipysheet.cell(0, 0, 'qty', read_only=True)
c.style = style_header
c = ipysheet.cell(0, 1, 'value', read_only=True)
c.style = style_header
cells_out = {}
for k, [key, name] in enumerate(self.li_output_data):
c = ipysheet.cell(1 + k, 0, name, read_only=True)
c.style = style_title_output
cells_out[key] = ipysheet.cell(1 + k, 1, 0, type='numeric')
self.sheet_out = sheet
self.cells_out = cells_out
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_refs_sheet(refs_df: pd.DataFrame,
num_mzs: int = 10) -> ipysheet.sheet:
sheet = ipysheet.sheet(
key="output",
rows=len(refs_df),
columns=len(OUTPUT_COLUMNS),
column_headers=list(OUTPUT_COLUMNS.keys()),
column_resizing=False,
column_width=[3 if x == "Spectra" else 1 for x in OUTPUT_COLUMNS],
)
for i, ref_key in enumerate(OUTPUT_COLUMNS.values()):
if ref_key == "spectrum":
ipysheet.column(i,
[x.widget() for x in refs_df[ref_key].to_list()],
read_only=True)
elif ref_key == "most_intense_mzs":
ipysheet.column(
i,
[
float_list_to_str(x.mz_by_intensity()[:num_mzs])
for x in refs_df["spectrum"]
],
read_only=True,
)
elif ref_key in ["exact_mass", "precursor_mz"]:
ipysheet.column(i,
refs_df[ref_key].to_list(),
numeric_format="0.000000",
read_only=True)
else:
ipysheet.column(i, refs_df[ref_key].to_list(), read_only=True)
return sheet
def update_all_cell_values(sheet_key: str,
value_list: List[List[Union[str, float]]]) -> None:
current_sheet = ipysheet.sheet(sheet_key)
current_sheet.rows = len(value_list)
ipysheet.easy.cell_range(value_list) # this appends to cells
current_sheet.cells = (current_sheet.cells[-1],
) # so only keep the last one
def _df_to_sheet(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:
sheet = sh.from_dataframe(df)
column = df.columns.get_loc("Value")
for cell in sheet.cells:
if column not in (cell.column_start, cell.column_end):
cell.read_only = True
else:
cell.type = "numeric"
# TODO: make the number of decimals depend on the module ?
# or chosen in the ui by the user
cell.numeric_format = "0.000"
# Name, Value, Unit, Description
sheet.column_width = [150, 50, 20, 150]
else:
sheet = sh.sheet()
return sheet
def ipysheet_grid(data, indexed=True):
if isinstance(data, list):
data = pd.DataFrame(data)
drop_index = False
elif isinstance(data, dict):
data = pd.DataFrame(data)
drop_index = False
else:
drop_index = True
if isinstance(data, pd.DataFrame):
if 'index' not in data.columns and drop_index:
data = data.reset_index()
for x in data.dtypes.iteritems():
if 'date' in str(x[1]):
data[x[0]] = data[x[0]].astype(str)
elif isinstance(data, pd.Series):
data = data.reset_index()
for x in data.dtypes.iteritems():
if 'date' in str(x[1]):
data[x[0]] = data[x[0]].astype(str)
else:
raise NotImplementedError()
sheet = ipysheet.sheet(rows=len(data), columns=len(data.columns), column_headers=data.columns.astype(str).tolist())
for i, col in enumerate(data.columns):
ipysheet.column(i, data[col].values.tolist())
return sheet
def _create_sheet(self):
"""Create ipysheet from table data (self._data)."""
layout = ipy.Layout(width='auto',
height='auto',
border='none',
margin='0px 0px 0px 0px')
sheet = ipysheet.sheet(rows=3,
columns=2,
row_headers=self._data['keys'],
column_headers=['value', 'fixed'],
stretch_headers='none',
layout=layout)
stl = {'textAlign': 'center'}
vals = self._data['values']
fixes = self._data['fix']
self._cells.clear()
self._cells['values'] = ipysheet.column(0,
vals,
row_start=0,
numeric_format=self.num_format)
self._cells['fix'] = ipysheet.column(1,
fixes,
row_start=0,
type='checkbox',
style=stl)
self.sheet = sheet
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self._main_data = None
self._area = Textarea()
self._sheet = ipysheet.sheet()
self.register_to_hub(kwargs['session'].hub)
def options_input():
options_sheet = ipysheet.sheet(rows=10, columns=3, column_headers=False)
cell1 = ipysheet.cell(0, 0, 'C')
cell2 = ipysheet.cell(0, 1, 'K')
cell2 = ipysheet.cell(0, 2, 'P')
for row in range(1, options_sheet.rows):
for col in range(0, options_sheet.columns):
ipysheet.cell(row, col, value=0.0, numeric_format='0.00')
return options_sheet
def _init_table(cls):
cls.table = ipysheet.sheet(
rows=cls.df_floors.shape[0],
columns=cls.df_floors.shape[1],
column_headers=cls.df_floors.columns.to_list())
cls.table.cells = [
ipysheet.column(c, cls.df_floors[column].tolist())
for c, column in enumerate(cls.df_floors)
]
def __init__(self, data, *args, **kwargs):
super().__init__(*args, **kwargs)
self._main_data = data
self._area = Textarea()
self._sheet = ipysheet.sheet(
column_headers=[x.label for x in data.main_components])
self._add_data()
self.register_to_hub(kwargs['session'].hub)
def test_cell_add():
sheet1 = ipysheet.sheet()
sheet2 = ipysheet.sheet()
cell2a = ipysheet.cell(0, 0, value='1')
assert len(sheet1.cells) == 0
assert len(sheet2.cells) == 1
ipysheet.sheet(sheet1)
cell1a = ipysheet.cell(0, 0, value='2')
cell1b = ipysheet.cell(0, 1, value='2')
assert len(sheet1.cells) == 2
assert len(sheet2.cells) == 1
with ipysheet.hold_cells():
cell1c = ipysheet.cell(1, 0, value='3')
cell1d = ipysheet.cell(1, 1, value='4')
assert len(sheet1.cells) == 2
assert len(sheet2.cells) == 1
assert len(sheet1.cells) == 4
assert len(sheet2.cells) == 1
def test_to_array():
sheet = ipysheet.sheet(rows=5, columns=4)
ipysheet.cell(0, 0, value=True)
ipysheet.row(1, value=[2, 34, 543, 23])
ipysheet.column(3, value=[1.2, 1.3, 1.4, 1.5, 1.6])
arr = ipysheet.to_array(sheet)
expected = np.array([[True, None, None, 1.2], [2, 34, 543, 1.3],
[None, None, None, 1.4], [None, None, None, 1.5],
[None, None, None, 1.6]])
assert np.all(arr == expected)
def add_row_with_inchi(name: str, inchi: str):
input_sheet = ipysheet.sheet("input")
value_list = input_sheet.cells[0].value
new_row_values = [name, inchi] + [""] * (input_sheet.columns - 2)
for i, row in enumerate(value_list):
if row == [""] * input_sheet.columns:
value_list[i] = new_row_values
break
else:
value_list.append(new_row_values)
update_all_cell_values("input", value_list)
def create_input_sheet(inputs: List[Input],
num_rows: int,
data: Optional[List[Any]] = None) -> ipysheet.sheet:
input_sheet = ipysheet.sheet(
key="input",
rows=num_rows,
columns=len(inputs),
column_headers=[x.label for x in inputs],
column_resizing=False,
column_width=COLUMN_WIDTH,
)
ipysheet.easy.cell_range(data or [[""] * len(inputs)] * num_rows)
return input_sheet
def extract_all(layout: widgets.Box) -> None:
"""launch msms refs extraction and export"""
with get_new_log_box(layout):
if is_valid_input_sheet():
logger.info("Extracting MSMS reference spectrums....")
input_sheet = ipysheet.sheet("input")
new_refs_df = generate_msms_refs_df(input_sheet)
if new_refs_df.empty:
return
sheet = create_refs_sheet(new_refs_df)
layout.children = swap_layout(layout.children,
LayoutPosition.SHEET_OUTPUT.value,
sheet)
def is_valid_input_sheet() -> bool:
"""Validate the input sheet, logs problems"""
input_sheet = ipysheet.sheet("input")
invalid = get_invalid_cells(input_sheet, INPUTS)
for row_num, col_name in invalid:
logger.error("In row %d, invalid value for '%s'.", row_num + 1,
col_name)
if len(invalid) > 0:
logger.error(
"All inputs must pass validation before spectrum extraction")
return False
logger.info("All inputs fields for new references have passed validation.")
return True
def test_getitem():
sheet = ipysheet.sheet()
cell00 = ipysheet.cell(0, 0, value='0_0')
cell10 = ipysheet.cell(1, 0, value='1_0')
cell21 = ipysheet.cell(2, 1, value='2_1')
assert sheet[0, 0] is cell00
assert sheet[1, 0] is cell10
assert sheet[2, 1] is cell21
with pytest.raises(IndexError):
sheet[1, 1]
# TODO: what do we do with copies.. ? now we return the first values
ipysheet.cell(0, 0, value='0_0')
assert sheet[0, 0] is cell00
def test_row_and_column():
ipysheet.sheet(rows=3, columns=4)
ipysheet.row(0, [0, 1, 2, 3])
ipysheet.row(0, [0, 1, 2])
ipysheet.row(0, [0, 1, 2], column_end=2)
ipysheet.row(0, [0, 1, 2], column_start=1)
with pytest.raises(ValueError):
ipysheet.row(0, [0, 1, 2, 4, 5])
with pytest.raises(ValueError):
ipysheet.row(0, [0, 1], column_end=3)
with pytest.raises(ValueError):
ipysheet.row(0, [0, 1, 2, 4], column_start=1)
row = ipysheet.row(0, [0, 1, 2, 3])
with pytest.raises(ValueError):
row.value = [0, 1, 2]
with pytest.raises(ValueError):
row.value = 1
row.value = [0, 1, 2, 4]
assert row.value == [0, 1, 2, 4]
ipysheet.column(0, [0, 1, 2])
ipysheet.column(0, [0, 1])
ipysheet.column(0, [0, 1], row_end=1)
ipysheet.column(0, [0, 1], row_start=1)
with pytest.raises(ValueError):
ipysheet.column(0, [0, 1, 2, 3])
with pytest.raises(ValueError):
ipysheet.column(0, [0, 1], row_end=0)
with pytest.raises(ValueError):
ipysheet.column(0, [0, 1, 2, 4], row_start=1)
col = ipysheet.column(0, [0, 1, 2])
with pytest.raises(ValueError):
col.value = [0, 1]
with pytest.raises(ValueError):
col.value = 1
col.value = [0, 1, 3]
assert col.value == [0, 1, 3]
def function_to_sheet(func, name, values):
sh = ipysheet.sheet(rows=len(name), columns=len(values), row_headers=name, column_headers=False)
x = ipysheet.row(0, values, numeric_format='0.00000')
y = ipysheet.row(1, [func(v) for v in values], numeric_format='0.00000')
# x = ipysheet.row(0, values)
# y = ipysheet.row(1, [func(v) for v in values])
@ipysheet.calculation(inputs=[x], output=y, initial_calculation=True)
def calculate(a):
return [func(i) for i in a]
return sh
def save_msms_refs(existing_refs_df: pd.DataFrame, output_file_name: str,
layout: widgets.Box) -> None:
"""Create CSV file containing old and new MSMS refs"""
with get_new_log_box(layout):
if not is_valid_input_sheet():
return
new_df = generate_msms_refs_df(ipysheet.sheet("input"))
if new_df.empty:
logger.error(
"Incomplete new MSMS reference definitions. Not writing an output file."
)
return
out_df = pd.concat([existing_refs_df, new_df])
out_df.to_csv(output_file_name, sep="\t", index=False)
logger.info("New MSMS references file with %d records written to %s.",
len(out_df), output_file_name)
def test_current_sheet():
sheet1 = ipysheet.sheet()
assert sheet1 is ipysheet.current()
sheet2 = ipysheet.sheet()
assert sheet2 is ipysheet.current()
assert sheet1 is ipysheet.sheet(sheet1)
assert sheet1 is ipysheet.current()
sheet3 = ipysheet.sheet('key3')
assert sheet3 is ipysheet.current()
sheet4 = ipysheet.sheet('key4')
assert sheet4 is ipysheet.current()
assert sheet3 is ipysheet.sheet('key3')
assert sheet3 is ipysheet.current()
assert sheet4 is ipysheet.sheet('key4')
assert sheet4 is ipysheet.current()
def enter_data(X):
nval = 500
x = np.zeros(nval)
x[:] = np.nan
y = np.zeros(nval)
y[:] = np.nan
sheet= ipysheet.sheet(rows=nval,columns=2,column_headers=(('Field [mT]','Magnetization')))
sheet.layout.height = '300px'
sheet.layout.width = '600px'
col1 = column(0,x)
col2 = column(1,y)
display(sheet)
X['sheet']=sheet
return X
def search(query: str, min_mw: float, max_mw: float,
layout: widgets.Box) -> None:
with get_new_log_box(layout):
clear_search_output(layout)
results = get_synonym_matches(query)
for cur in results:
RDLogger.DisableLog("rdApp.*") # hide rdkit warnings
cur["mol"] = cheminfo.normalize_molecule(
Chem.inchi.MolFromInchi(cur["inchi"]))
cur["norm_inchi"] = Chem.inchi.MolToInchi(cur["mol"])
RDLogger.EnableLog("rdApp.*")
cur["MW"] = ExactMolWt(cur["mol"])
filtered = filter_by_mw(filter_to_norm_inchi_in_db(results), min_mw,
max_mw)
logger.debug("Found %d matches to %s.", len(filtered), query)
if not is_valid_num_results(len(filtered), query, layout):
return
final = sorted(filtered, key=lambda x: x["MW"])
logger.debug("Num mols: %d", len(final))
column_names = ["", "Name", "MW", "Structure"]
sheet = ipysheet.sheet(
rows=len(final),
columns=len(column_names),
column_headers=column_names,
column_resizing=False,
column_width=[1, 4, 2, 10],
)
buttons = [
widgets.Button(description="use",
layout=widgets.Layout(width="100%")) for x in final
]
for button in buttons:
button.on_click(
lambda current: on_use_button_clicked(current, final, layout))
ipysheet.column(0, buttons)
ipysheet.column(1, [x["name"] for x in final])
ipysheet.column(2, [ExactMolWt(x["mol"]) for x in final])
ipysheet.column(3, [cheminfo.mol_to_image(x["mol"]) for x in final])
layout.children = swap_layout(layout.children,
LayoutPosition.SEARCH_OUTPUT.value,
sheet)
def _load_table(self, page, limit):
"""
Parameters
----------
page: int
page number to view.
limit: int
number of rows to display per page.
"""
start = (page - 1) * limit
if start < 0 or start >= self._total_nb_rows:
raise ValueError(
f"Specified page number {page} and limit {limit} are not valid for result set of {self._total_nb_rows} rows."
)
end = min(start + limit, self._total_nb_rows)
number_of_rows = end - start
self._table = sheet(
rows=min(self._total_nb_rows, number_of_rows),
columns=len(self._df.columns),
column_headers=list(self._df.columns),
layout=Layout(width="auto", height="330px"),
)
with hold_cells():
for col_index, column_id in enumerate(self._df.columns):
column_data = self._df[column_id]
column_feeder = self._columns_manager.get_column_feeder(
col_index)
rows = []
for row_index in range(start, end):
rows.append(
column_feeder.get_widget(column_data.iloc[row_index]))
column(col_index, rows, row_start=0)
