Many of the networks I retrieve and analysis are two-mode affiliation networks – meaning they’re networks of people connected to share objects or communities. For instance, Senators using the #gopconference hashtag on Twitter, where I’ll have an edgelist of Senators and the hashtags they use. NodeXL, my free network analysis software of choice, doesn’t currently support two-mode data though, and one-mode projections of affiliation networks are pretty interesting anyway. So, I worked with a Freelancer to write a macro to produce one-mode projections of two-mode affiliation networks within Excel. You can find the macro as a text file at Github.
Right now, the macro accepts one kind of input – a two-column edgelist where one column is all nodes of one type (e.g. Twitter users) and the other column is all nodes of the second type (e.g. hashtags). Sticking with the Twitter hashtag example – the code produces three new spreadsheets – a two-mode matrix of users x hashtags, a one-mode matrix of user x user where the matrix cell value is the number of hashtags they share, and a one-mode edgelist with columns for user1, user2, and edgeweight or number of shared hashtags.
Creation of the third sheet fails for large networks, at least those over 1000 edges. The workaround I’m using is to disable (comment out) that part of the macro, copy the user x user matrix to UCINet, export a DL, then import that DL to NodeXL. Of course, since my goal was to avoid UCINet, this is less than ideal, but it works and let’s me keep using NodeXL as a teaching tool that all my students can afford (since it’s free).