SQL-style analysis in Shortcuts

Shortcuts
1

Problem

I have two data sets stored as CSV files accessible by Files.app on iOS. The format of the data is such that each column is a variable, and each row is an observation. I would like to perform some SQL-style data analysis on these data sets, triggered using Shortcuts.app, such as:

  • Joining the data sets
  • Creating a “Choose from Menu” action, with the items of the menu all observations for a specific variable
  • Filtering data based on the “Choose from Menu” selection
  • Calculating simple summary statistics, such as the sum of the observations for a given variable.

Possible solutions

Utilising macOS

If I had a Mac switched on all the time - or if I wanted to purchase a MacStadium subscription - I could write scripts in SQL, R, Python, or another data analysis language. I could then access the Mac using SSH, and run a Shortcut to access the script over SSH. However, I don’t want to rely on my Macbook Pro being switched on all the time, and I’d rather not purchase a MacStadium subscription if I can help it.

Pythonista

Python is becoming more and more used for data analysis, so I was hopeful that I might be able to use Pythonista to analyse data on iOS. However, commonly used data analysis packages such as pandas and scipy are not locally available on Pythonista (e.g. https://forum.omz-software.com/topic/5624/is-pandas-supported-on-pythonista). Furthermore, Pythonista lacks proper Shortcuts parameter support, and I would need to store the CSV files in Dropbox, which I don’t want to do.

Scriptable

Unlike Pythonista, Scriptable does have Shortcuts support, although I’m not well-versed in Javascript. For example:

  • Is it possible to load data in CSV format as a two-dimensional array using Javascript in Scriptable?
  • Is it possible to use packages such as JQuery in Scriptable to query data using SQL-like language?
2

I understand that you would like to do this using Shortcuts, but for what you need you might want to look at the Carnets app (https://apps.apple.com/us/app/carnets/id1450994949) which allows the creation of Jupyter notebooks on iOS and does include pandas and other analysis packages. I don’t think Carnets supports Shortcuts at this time

Dave

2 Likes
4

Just tried using Carnets… and it deleted all the files in my Shortcuts folder in iCloud :cry:

5

What were you doing when that happened? I use Carnets and my Shortcuts folder is fine. I don’t know why Carnets would access that folder

7

I have no idea why that would happen. I can only suggest that you reach out to the Carnets developer @nholzschuch on Twitter

8

try this

https://www.icloud.com/shortcuts/f46f3c179e1f4582b0a8a100e3e5894b

it does basic parsing (without checking for quoted commas, and assuming a header row with variable names) and outputs descriptive stats for selected variables - pass it a csv file in the share sheet

10
  1. Once the file is read in Scriptable you could then display it in a table within Scriptable, or render it as HTML in a WebView, there are a few options, but yes definitely possible.
  2. I’ve seen some examples of people using 3rd-party libraries in Scriptable (e.g. here: Use moment.js in Scriptable), but it’s not exactly supported…you’d basically need to get the whole source code for the library and add it to Scriptable. I haven’t really seen jQuery used in this way though.