- And users and designers' session,
I'm Sven Kratz from FXPAL,
I'd like to introduce our first paper,
"Using and Exploring Hierarchical Data in Spreadsheets,"
by Kerry Chang and Brad Myers.
- Okay. Hello, hi, everybody,
thank you for coming.
So in this paper, we explore new ways to extend spreadsheets
to support using hierarchical data,
such as JSON and XML data.
This research is motivated by the fact
that more and more data today exists
in hierarchical data formats instead
of traditional table formats, such as CSV,
so, for example, most modern watch services
return data in JSON or XML.
So currently, to use hierarchical documents
often requires a person to write a lot of code
to parse the document, to extract desired fields,
and manipulate them to get the result.
So the spreadsheet, while being one of the most popular
tools for using data among people of all programming levels,
it's table interface is, by nature,
not good for handling data with nested hierarchies,
it makes using hierarchical data quite inefficient.
So our goal with this work is to extend spreadsheets
to bring the support of using hierarchical data
to more users and user programmers
to professional programmers, or data analysts.
So let me use an example here to explain
what are some problems of using a hierarchical document
in conventional spreadsheet, currently.
So here I have an example JSON file,
that is a list of conference papers,
so each paper here has some flat fields,
like ID, title, abstract, but also some hierarchical fields,
like the keyword fields, that has a list of keywords,
or the author field, that has a list of author objects,
each object has a name, institution, a city, and a country.
So sometimes when doing data analysis,
the structure in data can be very useful
to let people select the desired data.
For example, if I want to get all the first authors' names,
in JavaScript, I can write this JSONPath query,
to extract the data that I want.
The star signs mean all the papers,
and the zero index means the first author of each paper.
But when I flatten this data into a table,
to use in conventional spreadsheets,
the hierarchies in data would ineffably create
a lot of repetitive values.
For example, in this spreadsheet,
a paper title is repeated more than 30 times,
in order to let each of its author and keyword to pair once,
so imagine having 400 papers in this format.
It is almost impossible to quickly get
all the first authors' names.
So this spreadsheet is called a longtable,
because the hierarchies in the data are expanded vertically.
There's another way to flatten a hierarchical document
by expanding the hierarchies horizontally,
and this is called a Y-table.
In a Y-table, each table is a roll,
so there's no repetitive data.
However, as you can see now, in this spreadsheet,
there is so many columns and so many empty cells,
because the paper can have different numbers
of keywords and authors.
So the system needs to generate
columns that are the maximum length of the arrays
to show all the data.
For example, here are the maximum number
of keywords a paper can have, it's 12,
so the system creates 12 keyword columns,
in order to show all the data.
So the large number of columns and empty cells
make it very difficult to read data in a Y-table,
or do any further data analysis.
So, we want to extend the spreadsheet
to provide better interface to view hierarchical data,
and we also want to have a language to be able to let
people select the data using its structure.
Another observation we have was that
many data analysis people do it every day,
involve regrouping the data using a different field.
For example, this paper JSON file by default
comes grouped by their ID-order title,
but what if I wanted to find out, say,
which institution has the most paper?
Then I need to regroup the data using the institution field.
So in conventional spreadsheets, this is where people use
pivot tables, to perform constant groupings on data.
However, pivot table won't work
if the data has a lot of repetitive values,
or if the same type of data, like paper keywords,
are put in different columns.
So the pivot table also works very differently
from the basic spreadsheet model
of spreadsheet functions and formulas,
and it's often considered a more advanced feature.
So, to address these problems,
our work introduces several innovations
to the spreadsheet model.
First, this work contributes a new way
to visualize hierarchical data in spreadsheets,
as nested tables.
Working together with this new nested table displays,
will introduce a set of extensions
to the spreadsheet languages, sorting, filtering out
all the filling mechanics, and to enable people
to manipulate data in a spreadsheet,
using its hierarchical structure.
And to further support more types of data analysis,
we introduced new interaction techniques in the spreadsheet,
to let people group and join hierarchical objects
by arbitrary fields.
So I'll go through these contributions in details later.
But a big difference between our work,
and how conventional spreadsheets handle hierarchical data,
is that our methods work directly on hierarchical objects
without flattening them, so we tried to leverage
the structure in the data to provide more support
of using hierarchical data to the users.
So we implemented those innovations
in the spreadsheet tool called Gneiss,
so if you're interested, we have three prior publications
on Gneiss about using spreadsheets to exchange data
with web services, to program interactive data-driven
web applications, and to use streaming data.
But in this kind of paper, we focus on using spreadsheet
to help people use hierarchical data.
So, I'll first talk about our new method
to visualize hierarchical data in spreadsheets.
So this method uses the relative hierarchical relationship
among data extracted in spreadsheet columns
to show the data.
This means that the user can dynamically create
different views of the same data,
by changing their order in the spreadsheet.
So I will use a video to demonstrate the novel features.
In this video, I will explore the paper JSON file
that I just showed you, to find out
which institution has the most papers.
So if you're familiar with working with JSON data,
if you're gonna do this by writing code,
you will first have to go through the big paper array
to extract, you need paper title and institution pair,
and then you will aggregate the pairs using institutions,
to get the number of paper per institution,
and then finally you can do a sort to get the institution
that has the highest paper number,
and I'll show you how you can do all these
using spreadsheets in our system.
So this is our tools interface,
so the left pane is where the user can load a JSON document,
by either entering a web URL or load from a local file.
The right pane is the spreadsheet.
So to start using a JSON document, in our tool,
a user begins by extracting fields
that she thinks are relevant to her task,
to the spreadsheet, by selecting one of the fields
from the left pane and drag it to a spreadsheet column.
So the system will collect similar fields
for the user to fill in a column,
using the structure of the document.
So here, the user starts by extracting the paper title,
the author names and institution fields,
to the spreadsheet.
And you can see that here, the system created nested cells,
to show the author data for each paper.
In our tool, how a column is visualized
is always decided by its left column,
so the first column is always flat,
for the rest of the column, if the left column
comes from an ancestor field, the data in this column
are going to be shown in nested cells,
to be in the same row with their ancestors.
And if the left column comes from a sibling field,
like here, the author names and institutions are siblings,
the system will copy the left column structure,
and put the siblings in the same row.
And now, when I drag the paper title field
to the back to let this table start
by the author information, you can see
that the system reshapes the data.
Again, the first column is always flat,
so now the author data are put in a flat column,
but now for column C, the paper title,
the system found that its left column, author institution,
is actually from a descendant field,
so the system repeats the ancestor value,
which is the paper title, to again let the ancestor
and descendant be in the same role.
So this dynamic visualization method
allows the user to easily structure, or flatten the data,
by moving the columns using drag and drop.
The user can further regroup the data
by any field she wants, so in our system,
the column used for grouping are always put
at the beginning of the table.
So for example, my task here is to find out
which institution has the most paper,
so I will move the institution field
to the front of the table, and that causes the system
to flatten the data first by institution,
and then I can right-click on institution column,
and choose this group-by option to regroup the data.
The system will merge cells that have the same values
in column A, and put the values in B and C
into nested tables.
And so, you can see that now column B
has a lot of paper titles, repetitive paper title,
because a paper can have multiple authors
from the same institution, as shown in column C.
So I can use this group-by feature,
again to merge duplicate paper titles in column B,
and now I have a new view of the paper data,
that is grouped first by institution,
and then for each institution, all its paper,
and all the authors of that paper
that is from this institution.
And I want to show a little bit about
what's happening in the back-end.
So in the back-end, the system is actually reshaping
and restructuring the extracted JSON data
into different structure, as the user moves the column
to different location and selects grouping.
Because the back-end data is always
the structure JSON object, it makes further using the data
by its structure possible.
For example, we were able to extend
the spreadsheet language syntax,
to support selecting data, using the nested structure.
The syntax uses the column and nested row labels,
so for example, C1.1.2 here, select the cell "Nicola Dell,"
and the user can also select multiple nested values
using the parent-row label, for example,
C1.1 will select all the authors of the first paper,
and B1 will select all paper titles
from the first institutions.
So, selection that return multiple values
can be used in many conventional spreadsheet functions
that takes a list of values, such as count, sum,
and average, to calculate aggregation of the data.
So, together with the grouping feature,
this allows the user to calculate summaries of data,
by writing spreadsheet formulas
without having to learn pivot tables.
So, back in the scenario that I want to figure out
which institution has the most paper,
so here I insert a new column next
to the institution column, and I enter the formula,
count C1, into the first row, to count
all papers from the first institution,
and then I used a conventional to auto-fill
the drag and fill gesture, to fill this value
for all the institution.
And, finally, I can sort the data, to bring the institution
that has the most paper number to the top,
and now I have my answer.
So we also extend spreadsheets sorting and filtering
to work on nested hierarchical data,
so sorting and filtering on a nested column
will affect on the data in a same or deeper
hierarchical level, but will not affect
on the order of its ancestors.
So here, if I sort by column C,
the system will sort the data alphabetically,
sort the paper titles alphabetically
within each institution, but it will not change
the order of the institution.
And, finally, we introduced a way to join
multiple hierarchical objects in the spreadsheets
by common columns, so here I'll give a quick demo
to show you how people could use this feature.
For example, here I have another JSON file,
that has all the sections in the conference,
each session has a title, a room,
and a list of papers there, and their IDs,
that's going to be presented in this session.
So I already extracted the fields that I need
from both the paper and session file,
so you can see that there's a gray line between
the two tables, between column B and C,
to show that these two tables are not from the same file.
So to find out a paper's presentation time,
I have to connect the two tables together by your paper IDs,
so to do so, I selected two paper ID columns,
right-click and choose this join option to combine
the two tables.
So you can see that the two tables now become one,
and each paper now has its corresponding
session information, connected in the back.
So, our joining method preserves the structure
of one hierarchical object, and connect the other object
to the preserved object by common fields.
So this is similar to a left join or right join in SQL,
where one table gets preserved.
But different from joining two flat tables
in conventional SQL data-based tools,
our joining method operates directly on hierarchical objects
without flattening them.
So this results in a new hierarchical object
that can be manipulated, reshaped, and regrouped
as other regular hierarchical objects in our tool.
So we ran a lab study to evaluate
these new features for using hierarchical data,
we recruited both spreadsheet users
who are not professional programmers,
and professional programmers, to use our tool,
Excel, or writing JavaScript or Python code,
to complete five data-exploration tasks,
using the paper and session JSON file
that I just showed in the video.
So I don't have time to go into details of the study,
but to sum up, we found that spreadsheet users
who are not professional programmers,
and all of them had no prior experience using JSON data,
could learn to use our tool after a short tutorial,
and complete our study task almost two times faster
than spreadsheet users using Excel,
and professional programmers writing code.
So, in conclusion, we have shown in this work,
that the familiar spreadsheet can be extended
in a way that any user can understand,
to support using hierarchical data,
without having to flatten the data into a flat table.
So I think this opens some interesting future directions
for future work.
So, for example, the spreadsheet can now also output
hierarchical objects or visualization,
since the back-end data are still structured objects.
And another possible type of output
is to generate code that can manipulate the data,
as how the user manipulates them in a spreadsheet,
to let people reuse this data manipulation behavior,
in a bigger program.
And finally, this paper introduced a way to use spreadsheets
to let people select fields from hierarchical documents,
to do sorting, filtering, grouping, joining,
and aggregate data using spreadsheet functions.
So if we can build a mapping between the techniques
introduced in this work, and a data-based query language,
our tool could potentially become a console that'll help
any users use spreadsheets, to construct data-based queries,
and interact with databases.
We have a lot more details and discussion
on the system and the study, in our paper,
if you're interested.
So, thank you, I'm open for questions.
(audience clapping)
Hi!
- [Attendee] Thank you for your talk,
it was a great presentation.
- Thank you.
- [Attendee] Very short question. You teased us, almost,
you teased for five minutes with the paper counts,
so who won?
(laughs)
- Oh, it was last year's Ki Data, so, um,
CMU was the first one, but it's different this year.
He asked me who won, like which institution
had the most paper last year, and I said the answer
was CMU for Ki-15, so (laughs),
for last year's Ki, okay.
- [Attendee] Hi, Daniel Ashbrook
from the Rochester Institute of Technology.
I'm curious if you've thought about ways to use this
for taking the data and then adding extras,
so I might wanna add a new field to all of my records
in that JSON file.
- Yes, so I skipped some details about creating
new hierarchical data, using our system, it's in the paper,
so in our system, the user can select one column,
and, say, insert a new column that's in the same structure,
as this column, so this can be, for example,
if I want to manually enter all the author's emails,
then I could select the authors' names,
and then insert a new column that has the same structure,
and input the data.
Our system currently don't let people create
arbitrary structure, so the structure has to be
the structure that people extracted from the original files,
but that can be a future work, since we have shown that
the nested table representation is something that people
can understand.
- [Daniel] Thanks.
One other question I had was, how does it handle
fields that are not present in the entire file.
- Yeah, so, um...
So, okay, I'll try to explain it with an example,
so basically, if a paper has no author,
then it would just be a blank cell,
so it would be marked in gray color,
that's how the system handles this right now,
but you can see our system, it can handle the case where
different papers have different numbers of authors,
so it'd just have different numbers of nested cells
in the cell, but yeah, if there's a completely
missing field, then it would just be blank.
- [Daniel] Thank you.
- Thanks.
- [Man] Any more questions?
'Kay, well then, thank you very much, Kerry.
- Thanks.