Array distinct formula (requires pressing Ctrl + Shift + Enter):
{=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0))}
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), "")
References
https://exceljet.net/formula/extract-unique-items-from-a-list
=INDEX(C3:E11,MATCH("Frantz",B3:B11,0),2)
References
https://exceljet.net/index-and-match
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.
=MATCH(E2,B3:B11,0)
=MATCH("pq*",B3:B11,0)
=MATCH("peach",B3:B9,0)
References
https://exceljet.net/excel-functions/excel-match-function
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.
=INDEX(A1:B5,2,2) // returns value in B2 =INDEX(A1:B5,3,1) // returns value in A3
References
https://exceljet.net/excel-functions/excel-index-function
An array formula performs an operation on multiple values instead of a single value. The final result of an array formula can be either one item or an array of items, depending on how the formula is constructed. To work correctly, many array formulas need to be entered with
control + shift + enter. When you enter a formula this way, you’ll see the formula wrapped in curly braces {} in the formula bar.
{=SUM(LEN(B2:B11))}
References
https://exceljet.net/glossary/array-formula
=SUM($C$4:C4) // D4 =SUM($C$4:C5) // D5 =SUM($C$4:C6) // D6
References
https://exceljet.net/glossary/expanding-reference
from bokeh.sampledata.glucose import data
from bokeh.plotting import figure, show, output_file
from bokeh.models import BoxAnnotation
output_file("box_annotation.html", title="box_annotation.py example")
TOOLS = "pan,wheel_zoom,box_zoom,reset,save"
#reduce data size
data = data.loc['2010-10-06':'2010-10-13']
p = figure(x_axis_type="datetime", tools=TOOLS)
p.line(data.index.to_series(), data['glucose'], line_color="gray", line_width=1, legend_label="glucose")
low_box = BoxAnnotation(top=80, fill_alpha=0.1, fill_color='red')
mid_box = BoxAnnotation(bottom=80, top=180, fill_alpha=0.1, fill_color='green')
high_box = BoxAnnotation(bottom=180, fill_alpha=0.1, fill_color='red')
p.add_layout(low_box)
p.add_layout(mid_box)
p.add_layout(high_box)
p.title.text = "Glucose Range"
p.xgrid[0].grid_line_color=None
p.ygrid[0].grid_line_alpha=0.5
p.xaxis.axis_label = 'Time'
p.yaxis.axis_label = 'Value'
show(p)
References
https://docs.bokeh.org/en/latest/docs/user_guide/annotations.html#box-annotations
from datetime import datetime as dt
import time
from bokeh.sampledata.daylight import daylight_warsaw_2013
from bokeh.plotting import figure, show, output_file
from bokeh.models import Span
output_file("span.html", title="span.py example")
p = figure(x_axis_type="datetime", y_axis_type="datetime")
p.line(daylight_warsaw_2013.Date, daylight_warsaw_2013.Sunset,
line_dash='solid', line_width=2, legend_label="Sunset")
p.line(daylight_warsaw_2013.Date, daylight_warsaw_2013.Sunrise,
line_dash='dotted', line_width=2, legend_label="Sunrise")
start_date = time.mktime(dt(2013, 3, 31, 2, 0, 0).timetuple())*1000
daylight_savings_start = Span(location=start_date,
dimension='height', line_color='green',
line_dash='dashed', line_width=3)
p.add_layout(daylight_savings_start)
end_date = time.mktime(dt(2013, 10, 27, 3, 0, 0).timetuple())*1000
daylight_savings_end = Span(location=end_date,
dimension='height', line_color='red',
line_dash='dashed', line_width=3)
p.add_layout(daylight_savings_end)
p.title.text = "2013 Sunrise and Sunset times in Warsaw"
p.yaxis.axis_label = 'Time of Day'
show(p)
References
http://docs.bokeh.org/en/latest/docs/user_guide/annotations.html#spans
https://stackoverflow.com/questions/39426976/how-to-add-horizontal-line-span-to-the-box-plot-in-bokeh
https://stackoverflow.com/questions/28797330/infinite-horizontal-line-in-bokeh
from datetime import datetime
now = datetime.now() # current date and time
year = now.strftime("%Y")
print("year:", year)
month = now.strftime("%m")
print("month:", month)
day = now.strftime("%d")
print("day:", day)
time = now.strftime("%H:%M:%S")
print("time:", time)
date_time = now.strftime("%m/%d/%Y, %H:%M:%S")
print("date and time:",date_time)
References
https://www.programiz.com/python-programming/datetime/strftime
p.toolbar.logo = None p.toolbar_location = None
References
https://stackoverflow.com/questions/32158939/python-bokeh-remove-toolbar-from-chart