Share @ LinkedIn Facebook  ipysheet, ipywidgets
ipysheet - Interactive Spreadsheet in Jupyter Notebook [Python]

Interactive Spreadsheet in Jupyter Notebook using ipysheet

Table of Contents

Introduction

Jupyter notebook has been a preferred choice for data analysis by data scientists for a long time. It lets us present whole data analysis from data cleaning, interactive visualization, widgets and notes using markdown. Jupyter notebook can be used to prepare end to end report for explaining whole data analysis along with results. It's quite common that data is kept in excel or CSV files and it can be really helpful if we can present excel-like UI in jupyter notebook. Python has a library called ipysheet which can be used to represent an excel sheet in jupyter notebook as a widget. It supports the manipulation of a cell as well as calculations. It also lets us modify the look of cells and include ipywidgets. We'll try to explain the usage of ipywidgets by giving a few examples below. As ipysheet is also another widget, it can be easily linked with other widgets and visualization libraries as well. We suggest that you go through our tutorial on ipywidgets to learn about it which will help you with this tutorial.

Installation

conda install -c conda-forge ipysheet

1. Basics

We'll start with simple sheet widget creation and then keep on building on it to explain further functionalities and options available in the library.

In [1]:
import pandas as pd
import numpy as np

import ipysheet

from ipysheet import sheet, cell

We are creating a simple sheet object which creates a sheet widget of 5 rows and 5 columns. Sheet widget created is interactive which lets us change the value of the cell as well. We can also call cell() method passing row, column and value for that cell and it'll reflect in that cell.

In [ ]:
sheet1 = sheet()

cell(0,0,10);
cell(1,1,100);
cell(2,2,1000);
cell(3,3,"Hello");

sheet1

Interactive Spreadsheet in Jupyter Notebook using ipysheet

We can access all cell which has values by calling cells attribute of sheet object. We can even set this attribute passing list of cell objects and it'll override existing cells.

In [3]:
sheet1.cells
Out[3]:
(Cell(column_end=0, column_start=0, row_end=0, row_start=0, type='numeric', value=10),
 Cell(column_end=1, column_start=1, row_end=1, row_start=1, type='numeric', value=100),
 Cell(column_end=2, column_start=2, row_end=2, row_start=2, type='numeric', value=1000),
 Cell(column_end=3, column_start=3, type='text', value='Hello'))
In [ ]:
sheet2 = sheet(3,3)

sheet2.cells = [cell(2,2,"Hello World"), cell(1,2,"100")]

sheet2

Interactive Spreadsheet in Jupyter Notebook using ipysheet

In [5]:
from ipysheet import column, row
In [ ]:
x = np.random.rand(5)
y = np.random.rand(5)

sheet3= sheet(rows=5,columns=2)

col1 = column(0,x)
col2 = column(1,y)

sheet3

Interactive Spreadsheet in Jupyter Notebook using ipysheet

In [ ]:
x = np.random.rand(5)
y = np.random.rand(5)

sheet4= sheet(rows=2,columns=5)

col1 = row(0,x)
col2 = row(1,y)

sheet4

Interactive Spreadsheet in Jupyter Notebook using ipysheet

2. Calculations

We can even create functions that will be evaluated each time the value of a cell is changed and updates a cell to which function is linked. We can almost implement the same functionality as that of the excel sheet.

In [8]:
import ipywidgets as widgets
from ipysheet import calculation

Below we have created a simple multiplication function based on three cells which sum up the first two cells and then rise to the power of the third cell.

In [ ]:
sheet5 = sheet(rows=4, columns=3)

cell_x = cell(0, 1, 1, label_left='X')
cell_y = cell(1, 1, 2, label_left='Y')
cell_z = cell(2, 1, 3, label_left='Z')
cell_sum = cell(3, 1, 3, label_left='Multiplication', read_only=True)

@calculation(inputs=[cell_x, cell_y, cell_z], output=cell_sum)
def calculate(x, y, z):
    return (x + y)**z

sheet5

Interactive Spreadsheet in Jupyter Notebook using ipysheet

Another example below explains the calculation. We have also used sliders to change the value of cells so that we can test different values. We have used ipywidgets library to create sliders.

In [ ]:
sheet6 = sheet(rows=3, columns=3)

cell_x = cell(0, 1, 2, label_left='X')
cell_y = cell(1, 1, 4, label_left='Y')
cell_sum = cell(2, 1, 3, label_left='Multiplication', read_only=True)

# create a slider linked to cell a
slider_x = widgets.FloatSlider(min=-10, max=10, description='a')
widgets.jslink((cell_x, 'value'), (slider_x, 'value'))

slider_y = widgets.FloatSlider(min=-10, max=10, description='a')
widgets.jslink((cell_y, 'value'), (slider_y, 'value'))

@calculation(inputs=[cell_x, cell_y], output=cell_sum)
def calculate(x, y):
    return x * y

widgets.VBox([sheet6, slider_x,slider_y])

Interactive Spreadsheet in Jupyter Notebook using ipysheet

3. Sheet from Dataframe/Numpy and vice-versa

We can also create a sheet widget from the pandas dataframe as well as numpy 2D array as well. We can also create any of our sheet widget to pandas dataframe and numpy array. We'll explain below with few examples.

In [11]:
random_data = np.random.rand(5,3)

df = pd.DataFrame(random_data, columns=["Col1", "Col2", "Col3"])
df
Out[11]:
Col1 Col2 Col3
0 0.314889 0.160544 0.100811
1 0.338310 0.505806 0.980696
2 0.723349 0.736230 0.176615
3 0.409544 0.276834 0.957930
4 0.574338 0.164495 0.475395
In [ ]:
sheet7 = ipysheet.from_dataframe(df)
sheet7

Interactive Spreadsheet in Jupyter Notebook using ipysheet

In [ ]:
random_data = np.random.rand(4,2)

ipysheet.from_array(random_data)

Interactive Spreadsheet in Jupyter Notebook using ipysheet

In [14]:
ipysheet.to_array(sheet7)
Out[14]:
array([[0.3148889 , 0.16054444, 0.10081092],
       [0.3383101 , 0.50580647, 0.98069576],
       [0.72334947, 0.73623011, 0.17661513],
       [0.40954414, 0.27683385, 0.95793035],
       [0.57433785, 0.16449507, 0.47539469]])
In [15]:
ipysheet.to_dataframe(sheet7)
Out[15]:
Col1 Col2 Col3
0 0.314889 0.160544 0.100811
1 0.338310 0.505806 0.980696
2 0.723349 0.736230 0.176615
3 0.409544 0.276834 0.957930
4 0.574338 0.164495 0.475395

4. Integrating Widgets into Sheet widget

We can also integrate other ipywidgets widgets into ipysheet cells. We'll explain it with a simple example where we include sliders into sheet cells.

In [ ]:
sheet8 = sheet(2,2)

slider_1 = widgets.FloatSlider()
slider_2 = widgets.FloatSlider()

column1 = column(0, [slider_1, slider_2])

cell_1 = cell(0,1,1)
cell_2 = cell(1,1,5)

widgets.jslink((cell_1, "value"),(slider_1,"value"))
widgets.jslink((cell_2, "value"),(slider_2,"value"))

sheet8

Interactive Spreadsheet in Jupyter Notebook using ipysheet

In [ ]:
sheet9 = sheet(rows=6,columns=2)

prog_1 = widgets.IntProgress()
prog_2 = widgets.IntProgress()
prog_3 = widgets.IntProgress()
prog_4 = widgets.IntProgress()
prog_5 = widgets.IntProgress()
prog_6 = widgets.IntProgress()

column1 = column(0, [prog_1, prog_2, prog_3, prog_4, prog_5, prog_6])

cell11 = cell(0,1,10)
cell12 = cell(1,1,20)
cell13 = cell(2,1,30)
cell14 = cell(3,1,40)
cell15 = cell(4,1,50)
cell16 = cell(5,1,60)

widgets.jslink((cell11, "value"),(prog_1,"value"))
widgets.jslink((cell12, "value"),(prog_2,"value"))
widgets.jslink((cell13, "value"),(prog_3,"value"))
widgets.jslink((cell14, "value"),(prog_4,"value"))
widgets.jslink((cell15, "value"),(prog_5,"value"))
widgets.jslink((cell16, "value"),(prog_6,"value"))

sheet9

Interactive Spreadsheet in Jupyter Notebook using ipysheet

5. Linking Sheet to Plot

We can also link sheets to plots. We have given below example of using the bqplot library which is also based on widgets where individual components of graphs are widgets hence linking becomes easy.

In [ ]:
import numpy as np
from traitlets import link
import bqplot.pyplot as plt

size = 18
scale = 100.
np.random.seed(0)
x_data = np.arange(size)
y_data = np.cumsum(np.random.randn(size)  * scale)

fig = plt.figure()
fig.layout.width = '60%'

scatt = plt.scatter(x_data, y_data, colors=['red'], stroke='black')


sheet10 = sheet(rows=size, columns=2)

x_column = column(0, x_data)
y_column = column(1, y_data)

link((scatt, 'x'), (x_column, 'value'))
link((scatt, 'y'), (y_column, 'value'))

widgets.HBox((fig, sheet10))

Interactive Spreadsheet in Jupyter Notebook using ipysheet

6. Sheet Styling

We can highlight cell, change it's color, font style, font size, etc using various attributes available in cell(), row(), column() methods. We'll be exploring various parameter values. We'll also try various CSS values by passing them as dict to style parameter.

In [ ]:
sheet11 = sheet(rows=8,columns=3)

cell(1,1,"Hello", color="red", background_color="yellow", font_style="times new roman", font_weight="bold")
cell(2,1,"World", color="black", background_color="red", font_style="times new roman")
cell(1,1,"Hello", color="red", background_color="yellow", font_style="times new roman", font_weight="bold")
cell(1,0,True)
cell(1,2,type="date")

row(0,value=[1,2,3], type="numeric",background_color="green")

row(3,value=[1,2,3], type="numeric",style={"background":"tomato"})

row(4,value=[5,6,7], type="numeric",style={"background":"lawngreen", "font-size":"2rem", "font-family":"aerial"})


sheet11

Interactive Spreadsheet in Jupyter Notebook using ipysheet


Sunny Solanki  Sunny Solanki