Great ways to create random test data for your visualizations

Great-ways-to-create-random-test-data-for-your-viz

There are times when even after searching hours on Google, you cannot find the dataset matching what your heart desires and wish to create a custom dataset to experiment with.

Let’s get the obvious ones out of the way:

  • http://www.generatedata.com/
  • https://www.mockaroo.com/
    Both of these  are very good tools to generate data from a saved schema using curl, which can be helpful for automated testing. You can generate upto 86 columns randomly and customize the rest.
  • You can also experiment with http://www.json-generator.com/
  • Sometimes the requirement is more complex than just using random names or dates. We had our own list of values for Country(33 Values), Organization, Source and we wished to create unique combinations of these with Random Dates and Values for Customer Satisfaction
  • Just follow these simple steps to create a unique combination of a bunch of variables

Step-by-step guide

1. Generating Random Dates in Excel:

=Randbetween(Date(2012,1,1),Date(2014,12,31))

Will give a list of Random dates between 1 Jan 2012 and 31 Dec 2014

2. Select the Column, Copy and Paste as Values (123 symbol), Say in column A

3. Now that our totally random date values are ready,Column B onwards insert the unique values for different columns such as country, organization, region, measure in my example. It looks something like this snapshot below:

Test data for visualizations - diagram 1

4. Now, as per my requirement I had some unique combinations already in place with Source and Measure. A simple concatenation will aid us in two ways:
a. Maintain my unique combination of Source and Measure
b. Help me delimit these columns later when i run a macro to combine these columns.         Sorry for getting ahead of myself but i am referring to
test data for visualization - diagram 2

5. Insert a column after column B to keep column C empty or just edit the macro to increase the range. Now, time to run the macro to create combinations of the columns. I used the Macro written by Vog on http://www.mrexcel.com/forum/excel-questions/462187-cross-product-text-fields.html

Sub Xprod()Dim LRA As Long, LRB As Long, i As Long, j As Long, k As Long
LRA = Range(“A” & Rows.Count).End(xlUp).Row
LRB = Range(“B” & Rows.Count).End(xlUp).Row
For i = 1 To LRA
For j = 1 To LRB
k = k + 1
Range(“C” & k).Value = Range(“A” & i).Value & Range(“B” & j).Value
Next j
Next i
End Sub

6. You will see merged columns as below

est data for visualization - diagram 4

7. Repeat the steps by deleting first two columns and keeping column C blank to get cross-product of all values. My end result looked something like this:

est data for visualization - diagram 5

8. Let’s segregate column C to create all these unique combinations, and use a random function to create ratings:

I used the Text to Columns option in the Data Tab to segregate my data and then added a column to generate random rating values. If the data had social media mentions, i wanted them to be upto 1000 and Customer rating upto on a 0 to 10 scale
=IF(F2=”Mentions”,RAND()*1000, RAND()*10)

You can tweak any of the steps for building your customized test dataset or just use the generators. Happy testing!

This visualization is written by Nidhi Sharma, Analytics Project Manager at BRIDGEi2i

About BRIDGEi2i: BRIDGEi2i provides Business Analytics Solutions to enterprises globally, enabling them to achieve accelerated business impact harnessing the power of data. Our analytics services and technology solutions enable business managers to consume more meaningful information from big data, generate actionable insights from complex business problems and make data driven decisions across pan-enterprise processes to create sustainable business impact. To know more visit www.bridgei2i.com

Connect with us:
facebook BRIDGEi2i on twitter BRIDGEi2i on LinkedIn BRIDGEi2i on Google+ BRIDGEi2i on YouTube

The views and opinions expressed in this article are those of the author and do not necessarily reflect the official position or viewpoint of BRIDGEi2i.

 

Related Posts

Leave a comment