#!/usr/bin/env python
# coding: utf-8

# # ipysheet: Interactive spreadsheets for the Jupyter Notebook
# 
# ## https://github.com/QuantStack/ipysheet/
# 
# 
# ipysheet is a jupyter interactive widget library which provides a spreadsheet widget to the Jupyter notebook.
# 
# - MIT Licensed
# 
# **Installation:**
# 
# ```bash
# conda install -c conda-forge ipysheet
# ```

# ## Getting started
# 
# ipysheet provides ways to create a sheet with interactive cells and cell-ranges:

# In[ ]:


from ipysheet import sheet, cell

s = sheet(rows=3, columns=4)
cell1 = cell(0, 0, 'Hello')
cell2 = cell(2, 0, 'World')
cell_value = cell(2,2, 42.)
s


# In[ ]:


cell1.value = 'Hi'


# ## Create a spreadsheet from a Pandas dataframe/NumPy arrays
# 
# You can easily load your data from dataframes or arrays using the `from_dataframe` and `from_array` helper functions. It returns a spreadsheet containing cell-ranges with your data.
# 
# You can also use the `to_dataframe` and `to_array` to export your data back to Pandas/NumPy datastructures once your done interacting with it.

# In[ ]:


import numpy as np
import pandas as pd
from ipysheet import from_dataframe

number_columns = 26

dates = pd.date_range('20130101', periods=6)
df = pd.DataFrame(np.random.randn(6, number_columns), index=dates, columns=list(chr(ord('A') + i) for i in range(number_columns)))

s = from_dataframe(df)
s


# ## Link a spreadsheet to a bqplot
# 
# The same way you can `link` two widget values together, nothing stops you from linking an entire cell-range to an interactive bplot.

# In[ ]:


import numpy as np
from traitlets import link
from ipywidgets import HBox
import bqplot.pyplot as plt
from ipysheet import sheet, cell, column

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()
axes_options = {'x': {'label': 'Date', 'tick_format': '%m/%d'},
                'y': {'label': 'Price', 'tick_format': '0.0f'}}

scatt = plt.scatter(x_data, y_data, colors=['red'], stroke='black')
fig.layout.width = '70%'

s = 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'))

HBox((fig, s))


# ## Calculation decorator
# 
# The `calculation` decorator allows you to define a cell as being the result of an operation depending on other cells. It will listen for changes on the cell dependencies, and update it dynamically.

# In[ ]:


import ipywidgets as widgets
from ipysheet import calculation

s = sheet(rows=3, columns=2, column_headers=False, row_headers=False)
cell_a = cell(0, 1, 1, label_left='a')
cell_b = cell(1, 1, 2, label_left='b')
cell_sum = cell(2, 1, 3, label_left='sum', read_only=True)

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

@calculation(inputs=[cell_a, cell_b], output=cell_sum)
def calculate(a, b):
    return a + b

widgets.VBox([s, slider])


# ## Custom renderers
# 
# Custom renderers allows you to change the CSS of the cells (e.g. text color, background color) depending on the cell value. Whenever a custom renderer is created, it is then available for any cell, and you can specify which renderer a cell is using when creating it.
# 
# Here we create a `renderer_negative` function which will color the cells in orange if values are negative.

# In[ ]:


from ipysheet import renderer

def renderer_negative(value):
    return {
        'backgroundColor': 'orange' if value < 0 else ''
    }
renderer(code=renderer_negative, name='negative_transpiled');


# In[ ]:


import random
from ipysheet import cell_range

s = sheet(rows=3, columns=4)
data = [[random.randint(-10, 10) for j in range(4)] for j in range(3)]
cell_range(data, renderer='negative_transpiled')
s


# ## Embed interactive widgets in cells

# In[ ]:


from ipywidgets import FloatSlider
from ipysheet import column

s = sheet()

column1 = column(0, [FloatSlider() for _ in range(5)])
column2 = column(1, [1.] * 5)

s


# and export to a dataframe:

# In[ ]:


from ipysheet import to_dataframe

to_dataframe(s)


# In[ ]: