Quadratic 101

1# Last line writes to the spreadsheet
22 ** 6

1# An array fills down (column)
2[1,2,3]

1import pandas as pd
2
3# Imagine some raw data you got somewhere
4data = [
5  { 'source': 'Mastodon', 'likes': 81, 'dislikes': 12 },
6  { 'source': 'Twitter', 'likes': 142, 'dislikes': 41 },
7  { 'source': 'Threads', 'likes': 101, 'dislikes': 21 },
8]
9
10# Turn it into a dataframe
11df = pd.DataFrame(data)
12
13# Add a 'total' column
14df['total'] = df['likes'] + df['dislikes']
15
16# Calculate likes and dislikes as percentages & format
17# e.g. "12" becomes "12 (52%)"
18df['likes'] = df['likes'].apply(
19  lambda x: f'{x} ({x / df["total"].sum() * 100:.1f}%)'
20)
21df['dislikes'] = df['dislikes'].apply(
22  lambda x: f'{x} ({x / df["total"].sum() * 100:.1f}%)'
23)
24
25# Write to the spreadsheet
26df

1# Read a value from the sheet
2value = cell(0,0)
3
4# Add a number and write it to the sheet
5value + 5

1# Read cell values relative to the current cell's position
2one_left = rel_cell(-1, 0)
3one_right = rel_cell(1, 0)
4one_up = rel_cell(0, -1)
5one_down = rel_cell(0, 1)
6
7# Add them and write them to the sheet
8one_left + one_right + one_up + one_down

1import pandas as pd
2
3# Get the bytes from the spreadsheet
4values = cells((0,1), (0,100))
5
6# Make a dataframe for the output
7df = pd.DataFrame({
8    'Kilobytes': [],
9    'Megabytes': [],
10    'Gigabytes': []
11})
12
13# Iterate over each by and convert to KB, MB, GB
14for index, row in values.iterrows():
15    byte = int(row[0])
16    
17    # convert to kilobytes and format
18    kb = round(byte / 1024)
19    kb_str = "{:,}kB".format(kb)
20    
21    # convert to megabytes and format
22    mb = round(byte / (1024 * 1024), 1)
23    mb_str = "{:.1f}MB".format(mb)
24
25    # convert to gigabytes and format
26    gb = round(byte / (1024 * 1024 * 1024), 1)
27    gb_str = "{:.1f}GB".format(gb)
28    
29    # append to dataframe
30    df.loc[len(df.index)] = [kb_str, mb_str, gb_str]
31
32df

1A0 + A1

1SUM(A0:A7)

1IF(A0 > 5,
2  IF(A1 > 5, 
3    IF(A2 > 5,
4      AVERAGE(A0:A2),
5      'Invalid data'),
6    'Invalid data'),
7  'Invalid data'
8)

1# Import library for making network requests
2import requests
3
4# Make API call
5response = requests.get(
6  'https://api.npmjs.org/downloads/range/last-day/react'
7)
8
9# Turn to JSON
10json = response.json()
11
12# Write to the spreadsheet
13json['downloads'][0]['downloads']

1import pandas
2import requests
3
4# Read API parameters from the sheet
5package = cell(0, 3)
6timeframe = cell(2, 6)
7
8# Make API call
9response = requests.get(
10  f'https://api.npmjs.org/downloads/range/{timeframe}/{package}'
11)
12
13# Turn to JSON
14json = response.json()
15
16# Sum total downloads and write to sheet
17sum(item['downloads'] for item in json['downloads'])

1from datetime import datetime, timedelta
2import pandas
3import requests
4
5# Read API parameters from the sheet
6packages_comma_delimited = rel_cell(0, -5)
7timeframe = rel_cell(3, -2)
8
9# Scoped packages, e.g. `@slack/client`, are not yet supported in
10# bulk queries, so we make 1 API call per package
11# https://github.com/npm/registry/blob/master/docs/download-counts.md#per-version-download-counts
12packages = packages_comma_delimited.split(',')
13
14# last-day, last-week, last-month are all keywords in the API
15# but last-year is not, so we'll generate the from/to date range
16# ourselves so it matches the date range format of the API
17# YYYY-MM-DD:YYYY-MM-DD
18if timeframe == 'last-year':
19  today = datetime.today()
20  today_str = today.strftime('%Y-%m-%d')
21  one_year_ago = today - timedelta(days=365)
22  one_year_ago_str = one_year_ago.strftime('%Y-%m-%d')
23  timeframe = f'{one_year_ago_str}:{today_str}'
24
25# Define dataframe with columns
26df = pandas.DataFrame(columns=['Package', 'From', 'To', 'Downloads'])
27
28# Iterate over packages
29for package in packages:
30
31  # Make API call
32  response = requests.get(
33    f'https://api.npmjs.org/downloads/range/{timeframe}/{package}'
34  )
35
36  # Turn to JSON
37  json = response.json()
38
39  # Sum total downloads
40  print(json)
41  total_downloads = sum(
42    item['downloads'] for item in json['downloads']
43  )
44
45  # Append the dataframe
46  df.loc[len(df)] = [package, json['start'], json['end'], total_downloads]
47
48df

1import plotly.express as px
2import pandas as pd
3
4# Create the chart
5fig = px.line(
6  pd.DataFrame({
7    'Years': [2020, 2021, 2022, 2023],
8    'Values': [3, 5, 12, 24]
9  }),
10  x='Years',
11  y='Values',
12  title='Simple line chart'
13)
14
15# Display the chart
16fig.show()

1import plotly.express as px
2
3# Replace this dataframe with your data
4df = px.data.gapminder().query("country=='Canada'")
5
6# Create your chart type, for more chart types:
7# https://plotly.com/python/
8fig = px.line(
9    df,
10    x="year",
11    y="lifeExp",
12    title='Life expectancy in Canada'
13)
14
15# Customize the chart
16fig.add_scatter(
17    x = [fig.data[0].x[-1]],
18    y = [fig.data[0].y[-1]],
19    mode = 'markers + text',
20    marker = {'color':'orange', 'size':10},
21    showlegend = False,
22    text = [fig.data[0].y[-1]],
23    textposition='middle left'
24)
25fig.update_layout(
26    plot_bgcolor="White",
27    width=400,
28    height=280,
29    margin=dict(l=40, r=40, b=40, t=40),
30    yaxis=dict(
31        title=''
32    ),
33    xaxis=dict(
34        title='',
35        showgrid=False,
36        linecolor='rgb(204, 204, 204)',
37        linewidth=2,
38        ticks='outside',
39        tickfont=dict(
40            family='Arial',
41            size=12,
42            color='rgb(82, 82, 82)',
43        ),
44    ),
45)
46
47# Display the chart
48fig.show()

1import urllib.parse
2import plotly.graph_objects as go
3import pandas as pd
4from datetime import datetime
5import requests
6
7# Ticker dates
8ticker = 'AAPL'
9start_date = '2023-01-01'
10end_date = '2023-12-31'
11
12# API parameters
13params = {
14  'adjusted': True,
15  'sort': 'asc',
16  'limit': 120,
17  'apiKey': '<you-api-key-here>'
18}
19query_string = urllib.parse.urlencode(params)
20
21# Query the Polygon API 
22x = requests.get(
23  f'https://api.polygon.io/v2/aggs/ticker/{ticker}/range/1/day/{start_date}/{end_date}?{query_string}'
24)
25
26# Stick json response into DataFrame
27x = x.json()['results']
28df = pd.DataFrame.from_dict(x)
29
30# Convert dates 
31df['t']=(pd.to_datetime(df['t'],unit='ms')) 
32
33# Create chart
34fig = go.Figure(data=[
35  go.Candlestick(x=df['t'],
36  open=df['o'],
37  high=df['h'],
38  low=df['l'],
39  close=df['c'])
40])
41
42# Customize chart appearance
43fig.update_layout(
44  plot_bgcolor="White",
45  margin=dict(l=15, t=50),
46  xaxis_rangeslider_visible=False,
47  width=400, 
48  height=280,
49  title=dict(
50    text=f'AAPL price ({start_date} to {end_date})',
51    font=dict(
52      size=13,
53    )
54  ),
55  xaxis=dict(
56    showline=True,
57    showgrid=False,
58    showticklabels=True,
59    linecolor='rgb(204, 204, 204)',
60    linewidth=2,
61    ticks='outside',
62    tickfont=dict(
63      family='Arial',
64      size=11,
65      color='rgb(82, 82, 82)',
66    ),
67  ),
68  yaxis=dict(
69    tickprefix="$",
70    showticklabels=True,
71  ),
72)
73
74# Display on the spreadsheet
75fig.show()