Randomizing and Listing Data

Bottles in a row

I collect scents. Many online fragrance stores have sampling services that let you try even very expensive scents (over $300) for only a few dollars. Over the years I’ve managed to collect about 750 scents. My problem is that I keep wearing the same ones over and over, while neglecting many hidden gems. I wanted to pick random selections from my database (which is in Apple’s Bento), and schedule them on a weekly basis, without repeating any. That involved several challenges: exporting the data into a generic data format, randomizing it, checking for duplicates in the output, and organizing it in lists by day.

The first challenge was exporting. It sounds easy because Bento will export as Excel or csv. The problem was that I wanted to use JSON and jQuery to process the data and I needed numbers for each database entry, both for the JSON structure and to use for fetching the random item. Even though databases and spreadsheets number all the rows, they don’t export those numbers with the data. I needed it to look like the code below:

{ "1": { "Scent": "Scent Name 1", "By": "Company Name 1" }, "2": { "Scent": "Scent Name 2", "By": "Company Name 2" }, [etc.]

Maybe there’s an easy way to do this I don’t know about, but here’s how I did it: I imported my Bento data into Excel, created a new first column, then used the fill option to create a new numbered column. (Instructions can be found in this short video.) After I got the numbers I exported the data as a csv file and then converted it to a JSON file. It gave me exactly what I wanted.

Once I had referenced my JSON file in my jQuery I needed to choose my random selections. The randomizing was easy. I used a target number equal to the number of items in my database:

var myNumber; function doRandom() { myNumber = Math.floor(Math.random() * 748) + 1; }

To choose a week’s worth of scents, I fetched the data that corresponded to my number 7 times:

var myNumber, myHtml=''; for(var n = 0; n < 7; n++) { myHtml+=(data[myNumber].By + data[myNumber].Scent); }

But I quickly saw a problem, which was that even in such a small output the same scent got repeated. With such a large data set, that had seemed unlikely, but there it was. I needed to make sure that didn’t happen, and the answer was to compare the generated random number to a saved array, and if it was unique, add it to the array for the next comparison, then output it to the list.

var myNumber, usedArray = []; doRandom();//generate a number if ($.inArray(myNumber,usedArray) !== -1) { doRandom();//if it's already in my array, try again } else { usedArray.push(myNumber);//if unique, save to array and use }

That gave me all unique choices. Next, I wanted to build a list from the results and assign to days of the week. I defined my days with an array, and used the modulo operator to give me the index for the array. I also added some markup around it:

var myDays=["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"]; myHtml+=("<dt>"+myDays[n % 7] + '</dt><dd><strong>'+data[myNumber].By + ':</strong> <em>'+data[myNumber].Scent + '</em></dd>');  

It went so well I decided to go with a full month, by wrapping my output in columns with weeks numbered 1-4. (I put the modulo in a variable because I was reusing it.):

var j = 1; for(var n = 0; n < 28; n++) {//4 weeks * 7 days = 28 myMod = n % 7; if (n==0) {//initial HTML wrapper and first week myHtml+="<div><h2>Week " + j +"</h2><dl>" } else if (myMod == 0) {//end the week and begin the next myHtml+="</dl></div><div><h2>Week " + (++j) +"</h2><dl>" } [Generate data result html] if (n==27) { myHtml +="</div>";//final close HTML structure }

Here is the completed code and the results list:

See the Pen GqzoQK by Connie Finkelman (@pixelslave) on CodePen.21122

I went on to expand the script to schedule the entire collection, which spanned 94 weeks. I output it on an HTML page and printed the results. I’ll probably never run the script again, but if I were reusing this often I would do things differently. I would get the collection count from the JSON file, then make and array by shuffling numbers between 1 and the collection count, then use that to draw my data results, rather than choosing a succession of random numbers and comparing them one at a time to the JSON values.

Leave a Reply