Point-and-Click Graph Tools - Graph Analysis and Visualization - page 223

one for each column. You can then concatenate these to create the node list,
and if duplicate nodes exist, you can remove them.
See the NetworkLogs Excel spreadsheet in the supplemental materials for
an example.
Adjacency Matrix Visualization in Excel
Most people wouldn't think of Excel as a graph visualization environment.
However,Excelcanbehandyforvisualizingadjacencymatrices(whichwere
first discussed in Chapter 4, “Stats and Layout”). An adjacency matrix is
simply a grid where nodes are represented as the titles to rows and columns,
and links are represented as the cells in a matrix. The number in the cell
indicates a link attribute such as the weight of the link.
Using Excel's conditional formatting, you can make the values represented
by the links more visually explicit. For example, Figure 7-3 shows the time
series correlations between 20 topics. Time series correlations indicate
how two different items move together over time. In this example, topics
are based on a list of popular on-line topics from a list published by Time
magazine a few years ago. When two topics are popular at the same time,
they are highly correlated (green). Two topics that are popular at opposite
times are inversely correlated (red), and topics with no correlation are
yellow.
Simply viewing all the connections as a color-coded matrix is not sufficient
to see clusters or patterns. The only visible pattern is the green diagonal
line through the center, which simply indicates that any topic is perfectly
correlated with itself, and this is not an interesting insight.
Rearranging the data in Excel can improve your capability to see clusters. A
simple approach may be simply to sort by a particular column (and also do
the sort in the corresponding row to preserve the same order in both rows
and columns). This is a good start and shows how all the other items are
correlated to one specific topic.