Ways to represent table data

This page looks at ways to represent table data.

Here are links to the topic series of representing and processing data.

Summarizing book names and word count data
Ways to represent table data (this page)

R examples:

Python examples:

JavaScript and D3 examples:

JavaScript: SVG bar chart using D3

Julia examples:

Julia: Bar chart from JSON data using StatsPlots

... more to be added ...

Here is the example data in a textual table form.

Letter	Count
a	59900
b	11302
c	14311
d	34483
e	95247
f	17852
g	13528
h	62509
i	46347
j	2321
k	4449
l	25939
m	19154
n	51311
o	54741
p	9918
q	183
r	37468
s	44630
t	72396
u	20651
v	7458
w	18376
x	376
y	14814
z	234

Obviously, we do not want to type this data into the computer. Let us look at the ways this data might be represented.

A future topic will cover various aspects as to how to obtain the data (e.g., from a web page like this) and manipulate it into one of the forms below.

Some (simple) ways of representing table data include the following.

raw text file
CSV (Comma Separated Values) file
JSON (JavaScript Object Notation) file
XML (Extensible Markup Language) file
worksheet in a spreadsheet (Microsoft Excel, etc.)
table in a database (SQL Server, MySQL, Oracle, etc.)

Some users type data in each and every time they need it. This way is not recommended nor used here.

A text file contains text, usually unstructured text. Customized code is needed for each different text form. Here is one way to represent the above text, using the colon character ":" to separate fields.

a:59900 b:11302 c:14311 d:34483 e:95247 f:17852 g:13528 h:62509 i:46347 j:2321 k:4449 l:25939 m:19154 n:51311 o:54741 p:9918 q:183 r:37468 s:44630 t:72396 u:20651 v:7458 w:18376 x:376 y:14814 z:234

More standardized way to represent text include CSV, JSON and XML.

CSV represents the data as values separated by commas. If another character is to be used, such as a colon or tab, then that needs to be taken into account.

Here is a way to represent the above table data in CSV.

a,59900 b,11302 c,14311 d,34483 e,95247 f,17852 g,13528 h,62509 i,46347 j,2321 k,4449 l,25939 m,19154 n,51311 o,54741 p,9918 q,183 r,37468 s,44630 t,72396 u,20651 v,7458 w,18376 x,376 y,14814 z,234

How does the computer know if the first line of the CSV is data or the field descriptions? It is often not clear, so the user must somehow indicate which is needed and/or the programmer must handle this.

These variations make CSV hard to work with since user interaction, or explicit programming, is often required to make decisions.

JSON is a newer and easier (than most methods) to represent and work with data. JSON was originated as a subset of the way that JavaScript programs represent and processes data, so JSON integrates easily with the JavaScript used in most web pages. Here is one possible JSON way to represent the above data.

[ ["a",59900], ["b",11302], ["c",14311], ["d",34483], ["e",95247], ["f",17852], ["g",13528], ["h",62509], ["i",46347], ["j",2321], ["k",4449], ["l",25939], ["m",19154], ["n",51311], ["o",54741], ["p",9918], ["q",183], ["r",37468], ["s",44630], ["t",72396], ["u",20651], ["v",7458], ["w",18376], ["x",376], ["y",14814], ["z",234] ]

JSON text is often all on one long line. For readability, the above JSON was pretty-printed to be more readable.

The left and right square bracket characters are used to delimit lists. The data is a list of lists where each list in the list has a name and a count.

It turns out that in many data applications involving JSON, such as SVG and D3 for visualization, it is better to store name-value pairs as a dictionary (or hash table) rather than an explicit list. Dictionaries are written in JSON using curly braces.

Here is one way to do the name-value pair approach, pretty-printed for readability.

[ {"name":"a", "value":59900}, {"name":"b", "value":11302}, {"name":"c", "value":14311}, {"name":"d", "value":34483}, {"name":"e", "value":95247}, {"name":"f", "value":17852}, {"name":"g", "value":13528}, {"name":"h", "value":62509}, {"name":"i", "value":46347}, {"name":"j", "value":2321}, {"name":"k", "value":4449}, {"name":"l", "value":25939}, {"name":"m", "value":19154}, {"name":"n", "value":51311}, {"name":"o", "value":54741}, {"name":"p", "value":9918}, {"name":"q", "value":183}, {"name":"r", "value":37468}, {"name":"s", "value":44630}, {"name":"t", "value":72396}, {"name":"u", "value":20651}, {"name":"v", "value":7458}, {"name":"w", "value":18376}, {"name":"x", "value":376}, {"name":"y", "value":14814}, {"name":"z", "value":234} ]

Instead of a list of lists, this representation is a list of dictionaries.

This may look space inefficient, but with modern fast processing and communication bandwidth, it makes managing and manipulation the data easier.

XML is an older and more involved way (than most methods) to represent and work with data.

Here is one way to represent the data as XML. Note that XML often used additional tags that are sometimes needed.

In XML, the name-value pair way is the way into which the data is naturally placed. There are many ways to represent the data as XML. Here is another common way.

Many users create and/or store data in spreadsheets, such as Microsoft Excel.

... more to be added ...

Some users create and/or store data in databases such as Microsoft SQL Server, Oracle, MySql, etc.

... more to be added ...