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 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 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 _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 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 insert_rho_sliders(rho_sliders,col=0,row_start=0):
column1 = ipysheet.column(col, rho_sliders,row_start=row_start)
# for i,slide in enumerate(rho_sliders):
# cell(row_start+i,col,slide)
cells = []
for i in range(len(rho_sliders)):
cells.append(ipysheet.cell(row_start+i,col+1,rho_sliders[i].value,numeric_format='0.0'))
for c,s in zip(cells,rho_sliders):
widgets.jslink((c, "value"),(s,"value"))
return cells
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)
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 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 _update_table(cls):
cls.table.cells = [
ipysheet.column(c, cls.df_floors[column].tolist())
for c, column in enumerate(cls.df_floors)
]
def test_to_dataframe():
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])
df = ipysheet.to_dataframe(sheet)
assert np.all(df['A'].tolist() == [True, 2, None, None, None])
assert np.all(df['B'].tolist() == [None, 34, None, None, None])
assert np.all(df['C'].tolist() == [None, 543, None, None, None])
assert np.all(df['D'].tolist() == [1.2, 1.3, 1.4, 1.5, 1.6])
sheet = ipysheet.sheet(rows=4,
columns=4,
column_headers=['c0', 'c1', 'c2', 'c3'],
row_headers=['r0', 'r1', 'r2', 'r3'])
ipysheet.cell_range([
[2, 34, 543, 23],
[1, 1, 1, 1],
[2, 2, 222, 22],
[2, 0, 111, 11],
],
row_start=0,
column_start=0,
transpose=True)
df = ipysheet.to_dataframe(sheet)
assert np.all(df['c0'].tolist() == [2, 34, 543, 23])
assert np.all(df['c1'].tolist() == [1, 1, 1, 1])
assert np.all(df['c2'].tolist() == [2, 2, 222, 22])
assert np.all(df['c3'].tolist() == [2, 0, 111, 11])
sheet = ipysheet.sheet(rows=4,
columns=4,
column_headers=['t0', 't1', 't2', 't3'])
ipysheet.cell_range([
[2, 34, 543, 23],
[1, 1, 1, 1],
[2, 2, 222, 22],
[2, 0, 111, 11],
],
row_start=0,
column_start=0,
transpose=False)
df = ipysheet.to_dataframe(sheet)
assert np.all(df['t0'].tolist() == [2, 1, 2, 2])
assert np.all(df['t1'].tolist() == [34, 1, 2, 0])
assert np.all(df['t2'].tolist() == [543, 1, 222, 111])
assert np.all(df['t3'].tolist() == [23, 1, 22, 11])
sheet = ipysheet.sheet(rows=0, columns=0)
df = ipysheet.to_dataframe(sheet)
assert np.all(df == pd.DataFrame())
sheet = ipysheet.sheet(rows=4, columns=1)
ipysheet.column(0,
['2019/02/28', '2019/02/27', '2019/02/26', '2019/02/25'],
type='date')
df = ipysheet.to_dataframe(sheet)
assert [_format_date(x) for x in df['A'].tolist()
] == ['2019/02/28', '2019/02/27', '2019/02/26', '2019/02/25']
# Conduct a sieve analysis for a dried soil sample (see data in the table below) # # a) Draw the granulometric curve (cumulative mass distribution) and briefly characterise the sediment with regard to its major constituent(s). # # b) What is the coefficient of uniformity? # # In[3]: # title = ["mesh size [mm] ", "residue in the sieve [g] ", "∑Retained %", "Commulative Passed %"] Size = [6.3, 2, 0.63, 0.2, 0.063, "< 0.063 /cup"] passed = [11, 62, 288, 189, 42, 10] s2 = ips.sheet(rows=6, columns=4, row_headers=False, column_headers=title) ips.column(0, Size, row_start=0) ips.column(1, passed, row_start=0); s2 # In[4]: # Solution of problem 2 t_sample = np.sum(passed) # g, add the residue column to get total mass retained_per = passed/t_sample *100 # %, # retain percentage residue/total mass retain_per_cumsum =np.cumsum(retained_per) # get the cummulative sum of the reatined passing_cumper = 100 - retain_per_cumsum # substract 100-cummsum to get passing % - the last column #Output s3 = ips.sheet(rows=6, columns=4, row_headers=False, column_headers=title)
def _create_sheet(self):
"""Create ipysheet from table data (self._data)."""
nr = len(self._data['x'])
layout = ipy.Layout(min_width='auto',
height='auto',
border='none',
margin='0px 0px 0px 0px')
sheet = ipysheet.sheet(rows=nr,
columns=len(DistTable._headers) + 1,
row_headers=False,
column_headers=[' '] + DistTable._headers,
stretch_headers='none',
layout=layout)
stl = {'textAlign': 'center'}
gbcg = "#EEEEEE"
# other data cells
self._cells.clear()
self._cells['x'] = ipysheet.column(1,
self._data['x'][1:nr - 1],
row_start=1,
numeric_format=self.num_format)
self._cells['fix_x'] = ipysheet.column(2,
self._data['fix_x'][1:nr - 1],
row_start=1,
type='checkbox',
style=stl)
self._cells['y'] = ipysheet.column(3,
self._data['y'][1:nr - 1],
row_start=1,
numeric_format=self.num_format)
self._cells['fix_y'] = ipysheet.column(4,
self._data['fix_y'][1:nr - 1],
row_start=1,
type='checkbox',
style=stl)
# x[0] and x[-1] must be always a fixed parameter
self._cells['00'] = ipysheet.cell(0,
1,
self._data['x'][0],
type='numeric',
numeric_format=self.num_format)
ipysheet.cell(0, 2, [None], read_only=True, background_color=gbcg)
self._cells['02'] = ipysheet.cell(0,
3,
self._data['y'][0],
type='numeric',
numeric_format=self.num_format)
self._cells['03'] = ipysheet.cell(0,
4,
self._data['fix_y'][0],
type='checkbox',
style=stl,
background_color="white")
self._cells['10'] = ipysheet.cell(nr - 1,
1,
self._data['x'][nr - 1],
type='numeric',
numeric_format=self.num_format)
ipysheet.cell(nr - 1, 2, [None], read_only=True, background_color=gbcg)
self._cells['12'] = ipysheet.cell(nr - 1,
3,
self._data['y'][nr - 1],
type='numeric',
numeric_format=self.num_format)
self._cells['13'] = ipysheet.cell(nr - 1,
4,
self._data['fix_y'][nr - 1],
type='checkbox',
style=stl,
background_color="white")
# 1st column: check boxes for row selection
ipysheet.cell(0, 0, [None], read_only=True, background_color=gbcg)
self._row_select = ipysheet.column(0, (nr - 2) * [False],
row_start=1,
style=stl,
background_color=gbcg)
ipysheet.cell(nr - 1, 0, [None], read_only=True, background_color=gbcg)
self.sheet = sheet