Data Analysis with ⎕CSV and ⌸¶

In this workshop we're going to learn to use ⎕CSV and ⌸ to analyse a real dataset. The dataset is adapted from this freely available dataset, and the workshop itself is adapted from a 2023 workshop by Dyalog's Rich Park.

In [1]:
⎕IO←0 ⍝ feel free to use ⎕IO←1 if you prefer
⎕PW←12345
]BOX on

Was OFF

⎕CSV and Inverted Tables¶

We'll be working with the dataset given in the CSV (comma separated values) file order_data.csv. It consists of a header row, giving titles to each column of our dataset, and rows of data values separated by commas.

Click here to download order_data.csv.

In [2]:
⍪10↑⊃⎕NGET'order_data.csv'1

┌───────────────────────────────────────────────────────────────┐
│id,timestamp,city,state,payment,category                       │
├───────────────────────────────────────────────────────────────┤
│1,2017-10-02 10:56:33,sao paulo,SP,18.12,housewares            │
├───────────────────────────────────────────────────────────────┤
│2,2018-07-24 20:41:37,barreiras,BA,141.46,perfumery            │
├───────────────────────────────────────────────────────────────┤
│3,2018-08-08 08:38:49,vianopolis,GO,179.12,auto                │
├───────────────────────────────────────────────────────────────┤
│4,2017-11-18 19:28:06,sao goncalo do amarante,RN,72.20,pet_shop│
├───────────────────────────────────────────────────────────────┤
│5,2018-02-13 21:18:39,santo andre,SP,28.62,stationery          │
├───────────────────────────────────────────────────────────────┤
│6,2017-07-09 21:57:05,congonhinhas,PR,175.26,auto              │
├───────────────────────────────────────────────────────────────┤
│7,2017-05-16 13:10:30,nilopolis,RJ,75.16,auto                  │
├───────────────────────────────────────────────────────────────┤
│8,2017-01-23 18:29:09,faxinalzinho,RS,35.95,furniture_decor    │
├───────────────────────────────────────────────────────────────┤
│9,2017-07-29 11:55:02,sorocaba,SP,161.42,office_furniture      │
└───────────────────────────────────────────────────────────────┘

Now, let's use ⎕CSV to load our data into format we can work with.

In [3]:
(orders_matrix headers)←⎕CSV 'order_data.csv' ⍬ (2 1 1 1 2 1) 1

Here we're using ⎕CSV to load our dataset into a matrix.

  • 'order_data.csv' is the path to the CSV file, but you can also provide CSV data directly here.

  • The second part of the argument (⍬) specifies the encoding of the file. My providing a ⍬, we are allowing ⎕CSV to detect the encoding.

  • 2 1 1 1 2 1 specifies the data types of the columns of our file.

    • 0 means ignore this column.
    • 1 means this column is character data.
    • 2 means this column is numeric.
    • 3, 4, or 5 mean this column is numeric, but with tolerance for certain empty and/or invalid entries.

    So in our case, the first and fifth columns (id and payment) are numeric, while the rest are character data.

  • The final 1 in the argument indicates that our file includes the header row id,timestamp,city,state,payment,category, and that this should not be interpreted as data. Instead, the header row is returned separately to us in the result.

You can also export data from the workspace to a CSV file with the dyadic form. Full documentation for ⎕CSV can be found here.

In [4]:
headers
5↑orders_matrix

┌──┬─────────┬────┬─────┬───────┬────────┐
│id│timestamp│city│state│payment│category│
└──┴─────────┴────┴─────┴───────┴────────┘

┌─┬───────────────────┬───────────────────────┬──┬──────┬──────────┐
│1│2017-10-02 10:56:33│sao paulo              │SP│18.12 │housewares│
├─┼───────────────────┼───────────────────────┼──┼──────┼──────────┤
│2│2018-07-24 20:41:37│barreiras              │BA│141.46│perfumery │
├─┼───────────────────┼───────────────────────┼──┼──────┼──────────┤
│3│2018-08-08 08:38:49│vianopolis             │GO│179.12│auto      │
├─┼───────────────────┼───────────────────────┼──┼──────┼──────────┤
│4│2017-11-18 19:28:06│sao goncalo do amarante│RN│72.2  │pet_shop  │
├─┼───────────────────┼───────────────────────┼──┼──────┼──────────┤
│5│2018-02-13 21:18:39│santo andre            │SP│28.62 │stationery│
└─┴───────────────────┴───────────────────────┴──┴──────┴──────────┘

This is not the only format that ⎕CSV supports. We can also import the data as an inverted table, using the Invert option, which can use less memory and be more efficient to query.

By giving the option 'Invert' 1, character data is imported as matrices. By giving 'Invert' 2, character data is imported as vectors of character vectors.

In [5]:
(orders_columns   _)←⎕CSV⎕OPT('Invert' 1)⊢'order_data.csv' ⍬ (2 1 1 1 2 1) 1
(orders_columns_2 _)←⎕CSV⎕OPT('Invert' 2)⊢'order_data.csv' ⍬ (2 1 1 1 2 1) 1
⍪5↑¨orders_columns
⍪5↑¨orders_columns_2

┌───────────────────────────────────────┐
│1 2 3 4 5                              │
├───────────────────────────────────────┤
│2017-10-02 10:56:33                    │
│2018-07-24 20:41:37                    │
│2018-08-08 08:38:49                    │
│2017-11-18 19:28:06                    │
│2018-02-13 21:18:39                    │
├───────────────────────────────────────┤
│sao paulo                              │
│barreiras                              │
│vianopolis                             │
│sao goncalo do amarante                │
│santo andre                            │
├───────────────────────────────────────┤
│SP                                     │
│BA                                     │
│GO                                     │
│RN                                     │
│SP                                     │
├───────────────────────────────────────┤
│18.12 141.46 179.12 72.2 28.62         │
├───────────────────────────────────────┤
│housewares                             │
│perfumery                              │
│auto                                   │
│pet_shop                               │
│stationery                             │
└───────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│1 2 3 4 5                                                                                            │
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│┌───────────────────┬───────────────────┬───────────────────┬───────────────────┬───────────────────┐│
││2017-10-02 10:56:33│2018-07-24 20:41:37│2018-08-08 08:38:49│2017-11-18 19:28:06│2018-02-13 21:18:39││
│└───────────────────┴───────────────────┴───────────────────┴───────────────────┴───────────────────┘│
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│┌─────────┬─────────┬──────────┬───────────────────────┬───────────┐                                 │
││sao paulo│barreiras│vianopolis│sao goncalo do amarante│santo andre│                                 │
│└─────────┴─────────┴──────────┴───────────────────────┴───────────┘                                 │
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│┌──┬──┬──┬──┬──┐                                                                                     │
││SP│BA│GO│RN│SP│                                                                                     │
│└──┴──┴──┴──┴──┘                                                                                     │
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│18.12 141.46 179.12 72.2 28.62                                                                       │
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│┌──────────┬─────────┬────┬────────┬──────────┐                                                      │
││housewares│perfumery│auto│pet_shop│stationery│                                                      │
│└──────────┴─────────┴────┴────────┴──────────┘                                                      │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘

In our case, using an inverted table with matrices for character columns uses the least space (by far).

In [6]:
⍪⎕SIZE 'orders_matrix' 'orders_columns' 'orders_columns_2'

28717912
 9888840
22252568

Note: there is more to inverted tables than we will cover today. See this talk from Dyalog '18 for more information.

We're going to use the inverted table format (with character matrices) for the rest of this workshop. We could just pass around all the columns in a vector and unpack them as need be:

(col1 col2 ...)←columns

But this makes our code brittle - every time the schema of our data changes we need to change every place we unpack these columns. So instead, let's extract all our columns into variables in a namespace for ease of access.

In [7]:
orders←⎕NS ⍬
orders.(id timestamp city state payment category)←orders_columns

And now it's nice and easy to work with columns by name.

In [8]:
⌈/orders.payment ⍝ (blimey)

13664.08

Preprocessing¶

Right now, our orders namespace has a timestamp variable, with the character data for the timestamps of each purchase. To work with this more easily, we're going to need to parse this into numeric columns.

Exercise: Parse timestamp into separate year, month, day, hour, minute, and second columns. You may want to use ⎕VFI (documentation here).

SPOILERS AHEAD
In [9]:
⍝ simple solution
orders.(year month day hour minute second←↓⍉{1⊃' -:'⎕VFI⍵}⍤1⊢timestamp)

⍝ fast solution - only possible with the inverted table format
zero←⎕UCS '0'
timestamp_numeric←⍉⎕UCS orders.timestamp
Extract←{10⊥timestamp_numeric[⍵;]-zero}
orders.year  ←Extract 0 1 2 3
orders.month ←Extract 5 6
orders.day   ←Extract 8 9
orders.hour  ←Extract 11 12
orders.minute←Extract 14 15
orders.second←Extract 17 18
In [10]:
Slow←{
    orders.(year month day hour minute second←↓⍉{1⊃' -:'⎕VFI⍵}⍤1⊢timestamp)
    1
}
Fast←{
    zero←⎕UCS '0'
    timestamp_numeric←⍉⎕UCS orders.timestamp
    Extract←{10⊥timestamp_numeric[⍵;]-zero}
    orders.year  ←Extract 0 1 2 3
    orders.month ←Extract 5 6
    orders.day   ←Extract 8 9
    orders.hour  ←Extract 11 12
    orders.minute←Extract 14 15
    orders.second←Extract 17 18
    1
}

]Runtime -c 'Slow ⍬' 'Fast ⍬'

                                                                  
  Slow ⍬ → 1.3E¯1 |   0% ⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕ 
  Fast ⍬ → 4.7E¯3 | -97% ⎕

Key¶

⌸ is Dyalog's solution to SQL's GROUP BY clause. It is called in the form

⍺ ⍺⍺⌸ ⍵

Essentially, this groups ⍵ by the corresponding ⍺, and applies ⍺⍺ to each group. For example:

In [11]:
numbers←3 1 4 1 5 9 2 6 5 3 5 9
odds←2|numbers
odds {⍺ ⍵}⌸ numbers

┌─┬─────────────────┐
│1│3 1 1 5 9 5 3 5 9│
├─┼─────────────────┤
│0│4 2 6            │
└─┴─────────────────┘

Time for some details. When ⍺ is not provided, ⍵ used as a default. ⍺ and ⍵ must have the same number of major cells (the same ≢). The function operand ⍺⍺ given to ⌸ is given the key (from ⍺) as left argument and the cells of ⍵ corresponding to that key as right argument. The results of ⍺⍺ applied to all keys are mixed to give the final result. The order of groups in the output is the order of the corresponding keys in ⍺.

For an example with our dataset, let's see what the average payment is for each state.

In [12]:
orders.state {(⊂⍺),(+/÷≢)⍵}⌸ orders.payment

┌──┬───────────┐
│SP│140.3866109│
├──┼───────────┤
│BA│178.0894336│
├──┼───────────┤
│GO│167.417267 │
├──┼───────────┤
│RN│207.1084402│
├──┼───────────┤
│PR│156.8674639│
├──┼───────────┤
│RJ│163.8149305│
├──┼───────────┤
│RS│158.1150865│
├──┼───────────┤
│MG│158.2128593│
├──┼───────────┤
│SC│166.0354887│
├──┼───────────┤
│RR│224.0435897│
├──┼───────────┤
│PE│190.4632381│
├──┼───────────┤
│TO│214.2737269│
├──┼───────────┤
│CE│202.896166 │
├──┼───────────┤
│DF│163.9466195│
├──┼───────────┤
│SE│208.2690991│
├──┼───────────┤
│MT│204.492849 │
├──┼───────────┤
│PB│256.0266012│
├──┼───────────┤
│PA│214.0911746│
├──┼───────────┤
│RO│233.8208403│
├──┼───────────┤
│ES│157.3739535│
├──┼───────────┤
│AP│239.33     │
├──┼───────────┤
│MS│191.1419364│
├──┼───────────┤
│MA│204.0557284│
├──┼───────────┤
│PI│217.0201493│
├──┼───────────┤
│AL│233.3682697│
├──┼───────────┤
│AC│241.6105128│
├──┼───────────┤
│AM│185.7943056│
└──┴───────────┘

Exercises:

  • Find how many purchases were made in the most popular category (by number of purchases).
  • Which category/ies are the most popular (again by number of purchases)?

SPOILERS AHEAD
In [13]:
⍝ exercise 1
⌈/{≢⍵}⌸orders.category

⍝ exercise 2
(category count)←↓⍉{⍺(≢⍵)}⌸orders.category
(count=⌈/count)/category

9166

┌───────────────────────────────────────┐
│bed_bath_table                         │
└───────────────────────────────────────┘

Special Forms¶

Some special forms of ⌸ are optimised. These are

    {⊂⍵}⌸
    {⍺⍵}⌸
     {⍺}⌸
       ⊣⌸

    {≢⍵}⌸
 {⍺(≢⍵)}⌸
  {⍺,≢⍵}⌸

   {F/⍵}⌸
   {F⌿⍵}⌸
{⍺(F/⍵)}⌸
{⍺(F⌿⍵)}⌸
 {⍺,F/⍵}⌸
 {⍺,F⌿⍵}⌸

where

  • F is one of +, ⌈, or ⌊ when ⍵ is numeric;
  • F is one of ∧, ∨, =, ≠, + when ⍵ is Boolean.

These are senstive to exactly how you spell them!

In [14]:
numbers←10000?10000
]runtime -c '(2|numbers){+/⍵}⌸numbers' '(2|numbers){⊢+/⍵}⌸numbers'

                                                                                      
  (2|numbers){+/⍵}⌸numbers  → 1.6E¯5 |    0% ⎕⎕⎕⎕⎕⎕⎕⎕⎕                                
  (2|numbers){⊢+/⍵}⌸numbers → 7.1E¯5 | +334% ⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕⎕

Order¶

You might have noticed that ⌸ orders its output by the order they appeared in the input. Sometimes this isn't what we want, and we need to do some extra steps.

Exercise: Write a function PaymentPerState which takes

  • as left argument, a list of state codes, for instance 'GO' 'TO' 'SC',
  • as right argument, the orders table,

and returns the total payment made in each state, in the same order as the left argument. There are two ways you could go about fixing this order:

  1. sorting after the fact, or
  2. prepending some data in the correct order.

SPOILERS AHEAD
In [15]:
⍝ solution 1 - using ⍳ to get the right totals
PaymentPerState_1←{
	(state payment)←⍵.(state payment)
	(state total)  ←↓⍉state{⍺(+/⍵)}⌸payment
	total[state⍳⍺] ⍝ we are lucky that all states appear!
}

⍝ solution 2 - prepending ⍺ to get the right order
PaymentPerState_2←{
	(state payment)←⍵.(state payment)
	state  ⍪⍨←↑⍺    ⍝ prepend ⍺
	payment,⍨←0⍴⍨≢⍺ ⍝ prepend a 0 for each state in ⍺
	(≢⍺)↑state{+/⍵}⌸payment
}

'TO' 'GO' 'SC' PaymentPerState_1 orders
'TO' 'GO' 'SC' PaymentPerState_2 orders

58068.18 319766.98 579297.82

58068.18 319766.98 579297.82

In Dyalog '23 - D13: Giving Key a Vocabulary, an extension to ⌸ was proposed. This extension would allow us to give ⌸ a 'vocabulary' as left operand, instead of a function. This would control exactly the order that groups appear in the result.

In [16]:
⍝ source: https://raw.githubusercontent.com/abrudz/dyalog_vision/main/QuadEqual.aplo
QuadEqual←{ ⍝ ⌸ allowing operand to be vocabulary (and then using {⊂⍵} as internal operand)
	⍺←⊢  ⍝
	3=40 ⎕ATX'⍺⍺':⍺ ⍺⍺⌸⍵  ⍝ fn operand: current definition
	3=40 ⎕ATX'⍺':⍵ ∇⍳≢⍵   ⍝ monadic
	
	⎕IO←1
	uvoc←∪⍺⍺
	mask←(≢uvoc)≥uvoc⍳⍺  ⍝ high-rank ∊
	keys←uvoc⍪mask⌿⍺
	values←mask⌿⍵
	values⍴⍨←(≢uvoc)+@1⍴values  ⍝ append filler values while guarding against NONCE ERRORs
	values⊖⍨←-(≢uvoc)           ⍝ move them to front

	(1↓¨keys{⊂⍵}⌸values)[uvoc⍳⍺⍺]
}

This makes PaymentPerState much easier.

In [17]:
PaymentPerState_3←{
	(state payment)←⍵.(state payment)
	+/¨(↓state) (⍺ QuadEqual) payment
}
'TO' 'GO' 'SC' PaymentPerState_3 orders

58068.18 319766.98 579297.82

Multiple Keys¶

Sometimes we need to group our data by more than one than one key. In this case we really have two options:

  1. Use ⌸ multiple times - once for each column we're grouping by,
  2. Create a compound key from the individual keys.

Exercise: Write a function PaymentPerMonthByState which takes

  • as left argument, a list of state codes,
  • as right argument, the orders table,

and returns a matrix of total payments whose columns correspond to the given state codes and whose rows correspond to months in 2017. For example, 'SP' 'RJ' 'PI' 'MT' PaymentPerMonthByState orders should return

 43103.53  13139.53 1453.98  1922.78
 80348.6   33197.29 3298.4   3583.36
140767.23  59495.67 2582.92  2702.55
130989.25  61960.3  2288.91  3912.86
188394.13  75293.52 6679.58  7560.36
185274.77  59246.08 2626.96  4788.16
197902.88  84167.86 2938.77 11235.49
212931.9   85555.98 5072.72  6939.29
231109.84 104566.94 3242.68  8101.66
239321.27 108026.61 4544.47 12828.51
391137.77 166838.56 3745.39 13144.66
301554.04 124615.01 3482    10432.55

For instance, a total of 43103.53 was spent in the state SP in January of 2017.

SPOILERS AHEAD
In [18]:
⍝ solution 1 - compound key
PaymentPerMonthByState_1←{
	(state payment year month)←⍵.(state payment year month)

	⍝ get only 2017
	mask←year=2017
	state  ⌿⍨←mask
	payment⌿⍨←mask
	month  ⌿⍨←mask

	⍝ prepend for ordering
	n←12×≢⍺
	state  ⍪⍨←↑n⍴⍺
	month  ,⍨←(≢⍺)/1+⍳12
	payment,⍨←n⍴0

	⍝ compound key
	month_state←⍉↑month (↓state)

	⍝ compute matrix
	12 (≢⍺)⍴month_state {+/⍵}⌸ payment
}

⍝ solution 2 - a faster version
PaymentPerMonthByState_2←{
	(state payment year month)←⍵.(state payment year month)

	⍝ get only 2017
	mask←2017=year
	state  ⌿⍨←mask
	payment⌿⍨←mask
	month  ⌿⍨←mask

	⍝ prepend for ordering
	n←12×≢⍺
	state  ⍪⍨←↑n⍴⍺
	month  ,⍨←(≢⍺)/1+⍳12
	payment,⍨←n⍴0

	⍝ compound key
	state←⍳⍨state             ⍝ ids idiom
	month_state←⍉↑month state ⍝ now flat

	⍝ compute matrix
	12 (≢⍺)⍴month_state {+/⍵}⌸ payment
}

⍝ solution 3 - double key
PaymentPerMonthByState_3←{
	(state payment year month)←⍵.(state payment year month)

	⍝ get only 2017
	mask←2017=year
	state  ⌿⍨←mask
	payment⌿⍨←mask
	month  ⌿⍨←mask

	month,⍨←1+⍳12
	i←month {⊂1↓⍵}⌸ (12⍴0),⍳≢payment
	↑⍺∘{
		state  ←   (↑⍺)⍪state[⍵;]
		payment←(0⍴⍨≢⍺),payment[⍵]
		(≢⍺)↑state {+/⍵}⌸ payment
	}¨i
}

'SP' 'RJ' 'PI' 'MT' PaymentPerMonthByState_1 orders
'SP' 'RJ' 'PI' 'MT' PaymentPerMonthByState_2 orders
'SP' 'RJ' 'PI' 'MT' PaymentPerMonthByState_3 orders

 43103.53  13139.53 1453.98  1922.78
 80348.6   33197.29 3298.4   3583.36
140767.23  59495.67 2582.92  2702.55
130989.25  61960.3  2288.91  3912.86
188394.13  75293.52 6679.58  7560.36
185274.77  59246.08 2626.96  4788.16
197902.88  84167.86 2938.77 11235.49
212931.9   85555.98 5072.72  6939.29
231109.84 104566.94 3242.68  8101.66
239321.27 108026.61 4544.47 12828.51
391137.77 166838.56 3745.39 13144.66
301554.04 124615.01 3482    10432.55

 43103.53  13139.53 1453.98  1922.78
 80348.6   33197.29 3298.4   3583.36
140767.23  59495.67 2582.92  2702.55
130989.25  61960.3  2288.91  3912.86
188394.13  75293.52 6679.58  7560.36
185274.77  59246.08 2626.96  4788.16
197902.88  84167.86 2938.77 11235.49
212931.9   85555.98 5072.72  6939.29
231109.84 104566.94 3242.68  8101.66
239321.27 108026.61 4544.47 12828.51
391137.77 166838.56 3745.39 13144.66
301554.04 124615.01 3482    10432.55

 43103.53  13139.53 1453.98  1922.78
 80348.6   33197.29 3298.4   3583.36
140767.23  59495.67 2582.92  2702.55
130989.25  61960.3  2288.91  3912.86
188394.13  75293.52 6679.58  7560.36
185274.77  59246.08 2626.96  4788.16
197902.88  84167.86 2938.77 11235.49
212931.9   85555.98 5072.72  6939.29
231109.84 104566.94 3242.68  8101.66
239321.27 108026.61 4544.47 12828.51
391137.77 166838.56 3745.39 13144.66
301554.04 124615.01 3482    10432.55