A few of my favorite foods.. - part 2

I shared A few of my favorite foods before. This post explores more information using the same dish name data as the previous post. The question and answers are for the months of October, November, and December 2022.

Question	Answer
How many special meals over the 3 months?	52
How much did the meals cost (only when I paid hard cash)?	$1461.94
How many people or restaurants lovingly prepared the meals?	84
… How many unique people or restaurants?	64
How many of those meals were prepared by Nidhi? ❤️	19
How many dishes were there in total?	185
How many distinct dishes?	156
What is the monthly average for eating out? 💃	11

As a foodie, I had an amazing time!

How I compiled this information

In addition to publishing this blog, I also maintain a monthly journal file. Each file has a daily entry, which captures the dish name data using org-name property drawers. This is then generated into a tracking org-table, delightfully named eating-out, all within the same journal file using M-x org-update-all-dblocks.

I started out with exporting the eating-out table as a csv from my journal files. Since the data export is only for three months, I opened each file inside of Emacs, and then used the built in org-mode function org-table-export to build out 3 csv’s on my mac:

M-x RET org-table-export RET 2022-12-eating-out.csv RET RET
M-x RET org-table-export RET 2022-11-eating-out.csv RET RET
M-x RET org-table-export RET 2022-10-eating-out.csv RET RET

Then I started to play with the exported data using sqlite3:

sqlite> .mode csv
sqlite> .import 2022-12-eating-out.csv "eating-out"
sqlite> .import --skip 1 2022-11-eating-out.csv "eating-out"
sqlite> .import --skip 1 2022-10-eating-out.csv "eating-out"

sqlite> .schema "eating-out"
CREATE TABLE IF NOT EXISTS "eating-out"(
"Eating out" TEXT, "COST" TEXT, "RESTAURANT" TEXT, "DISH" TEXT);

sqlite> select count(*) from "eating-out";
52

sqlite> select sum(cost) from "eating-out";
1461.94

sqlite> with split(name, csv) as (
   ...>   select '', restaurant||',' from "eating-out"
   ...>   union all
   ...>   select substr(csv, 0, instr(csv, ',')), substr(csv, instr(csv, ',')+1)
   ...>   from split
   ...>   where csv != ''
   ...> ) select count(*) from split where name != '';
84

sqlite> with split(name, csv) as (
   ...>   select '', restaurant||',' from "eating-out" union all  select substr(csv, 0, instr(csv, ',')), substr(csv, instr(csv, ',')+1) from split where csv != '')
   ...> select count(distinct name) from split where name != '';
64

sqlite> with split(name, csv) as (
   ...>   select '', restaurant||',' from "eating-out" union all  select substr(csv, 0, instr(csv, ',')), substr(csv, instr(csv, ',')+1) from split where csv != '')
   ...> select count(*) from split where name like '%nidhi%';
19

sqlite> with split(name, csv) as (
   ...>   select '', dish||',' from "eating-out" union all  select substr(csv, 0, instr(csv, ',')), substr(csv, instr(csv, ',')+1) from split where csv != '')
   ...> select count(*) from split where name != '';
185

sqlite> with split(name, csv) as (
   ...>   select '', dish||',' from "eating-out" union all  select substr(csv, 0, instr(csv, ',')), substr(csv, instr(csv, ',')+1) from split where csv != '')
   ...> select count(distinct name) from split where name != '';
156

How I compiled this information#

How I compiled this information