Network Expansion on Spreedsheets

Using Spreedsheets to Compute Network Expansion

To compute network expansion numbers quickly, we can write the contact graph as a contact matrix with has a column and a row for each employee. We can then multiply the contact matrix by itself in a spreedsheet to simulate network expansion.

The contact matrix has a one in column C (Carl) and row R (Rhada) if person Carl and Rada interact and a zero otherwise. Note that column C and row R has a one (zero) then so does column R and row C. By convention, the contact matrix has a zero on the diagonal because a person cannot cannot give herself the coronavirus.

Direct Contact Matrix

Anna Basquat Carl Denise Earl Rada

Anna 0 1 0 1 0 0

Basquat 1 0 1 0 1 0

Carl 0 1 0 1 0 1

Denise 1 0 1 0 0 0

Earl 0 1 0 0 0 1

Rada 0 0 1 0 1 0

The sum of a row corresponding to each person equals the number direct contacts of that person. Multiplying the matrix by itself gives the squared direct contact matrix. This matrix contains information on the number of direct and indirect contacts as well as the number of different ways two people are connected indirectly.

Notice first that the diagonal entries equal the number of direct contacts for each person. Anna has two direct contacts and Basquat and Carl have three direct contacts. If an off-diagonal entry equals zero, then those two people are not connected through a common contact. If it is not zero, then the two people share a common contact, and, in addition, the number in the row and column equals the number of common contacts. For example, Anna and Denise share no contacts. Anna and Carl share two contacts (Baquat and Denise), while Anna and Earl share one contact (Basquat).

Squared Direct Contact Matrix

Anna Basquat Carl Denise Earl Rada

Anna 2 0 2 0 1 0

Basquat 0 3 0 2 0 1

Carl 2 0 3 0 2 0

Denise 0 2 0 2 0 0

Earl 1 0 2 0 2 1

Rada 0 1 0 0 1 2

To calculate the total number of direct contacts and contacts with one degree of separation takes two steps starting from a blank matrix. First, for each nonzero entry in the direct contact matrix, we place a D. Then for each non diagonal entry which is zero in the direct contact matrix but not zero in the squared contact matrix we write D1 to note one degree of separation. Those operations give the following Direct and One Degree Contact Matrix.

Direct and One Degtree Contact Matrix

Anna Basquat Carl Denise Earl Rada

Anna 0 D D1 D D1 0

Basquat D 0 D D1 D D1

Carl D1 D 0 D D1 D

Denise D D1 D 0 0 0

Earl D1 D D1 0 0 D

Rada 0 D1 D 0 D 0

In this example, there are 14 direct contacts (7 pairs of contacts) and 10 indirect contacts.