## Calculating Geometric Mean in NodeXL

My social networks reading group read De Choudury et al’s WWW’10 paper about inferring social networks from email (citation’s below) this week, and I was inspired by our discussion to calculate geometric means for Twitter mentions. You can read elsewhere about the public officials and social media project, but basically I have a bunch of data from Twitter including how often members of Congress mention one another. DeChoudury and colleagues point out that using a binary decision to evaluate the weight of edges (reciprocated or not) doesn’t make much sense when you’re talking about personal communication. They use an alternative weight they call “geometric mean” that I thought might be useful for Twitter mentions. Their equation for geometric mean is

ws

ij= sqrt[(wij*wji)]

Where two individuals *i *and *j* exchange messages. w*ij* is the number of messages from *i* to* j* and w*ji* is the number of messages from j to i. In my data, these are mentions. My data is already in a NodeXL workbook, and I’ve already collapsed duplicate edges to create an “Edge Weight” column. Here’s what my data look like now:

To calculate the geometric mean, I used a helper column “MergedVertices” with the formula

=[@[Vertex 1]]&[@[Vertex 2]].

Then the formula for “Geometric Mean” is:

=SQRT(SUMIF([MergedVertices],[@[Vertex 1]]&[@[Vertex 2]],[Edge Weight])*(SUMIF([MergedVertices],[@[Vertex 2]]&[@[Vertex 1]],[Edge Weight])))

That solution is courtesy of Sean Cheshire who answered my StackOverflow question. Thanks, Sean!

Citation: De Choudhury, M., Mason, W. A., Hofman, J. M., & Watts, D. J. (2010). Inferring relevant social networks from interpersonal communication. Proceedings of the 19th international conference on World wide web – WWW ’10 (p. 301). New York, New York, USA: ACM Press. Retrieved from http://dl.acm.org/citation.

### 2 thoughts on “Calculating Geometric Mean in NodeXL”

### Leave a Reply

You must be logged in to post a comment.

[…] Calculating geometric mean of two users […]

[…] couple months ago I blogged about using geometric mean instead of simple edge weight and reciprocation measures, and I put that to use recently on data from Congress’s mentioning on Twitter between March […]