How to map data?
February 2, 2019 10:40 AM Subscribe
So I've got a group of organizations. Each organization has a group of board-members. Board-members can serve on multiple organizations. How do I best save this data so that I can easily jump between viewing at the organization level and the individual level?
Bonus points if I can do it in Excel (but this seems like the reason people invented databases, so I'll take other answers, too). Triple bonus points if there's some way I can visualize the universe of organizations and their overlap in board members and the universe of board members and their overlap in organizations along these lines. I can program if necessary, but it's been a while and I'd need to re-learn a bit.
Bonus points if I can do it in Excel (but this seems like the reason people invented databases, so I'll take other answers, too). Triple bonus points if there's some way I can visualize the universe of organizations and their overlap in board members and the universe of board members and their overlap in organizations along these lines. I can program if necessary, but it's been a while and I'd need to re-learn a bit.
What about something like the data viz from the NYT about Connecting the Dots Behind the 2016 Presidential Candidates?
Node XL might get you part way there in excel.
posted by oceano at 12:46 PM on February 2, 2019 [1 favorite]
Node XL might get you part way there in excel.
posted by oceano at 12:46 PM on February 2, 2019 [1 favorite]
Start with three sheets:
1. Board Members
2. Organizations
3. Relationships
List each person and organization only once in their respective sheets, and include a unique ID for each one.
Then, list each person-organization relationship in the Relationships sheet (e.g. Column A: Member ID, Column B: Organization ID). And while you're at it, create a unique ID for each row in this Relationships sheet. For a board member who belongs to only one organization, they'd have only one row in the Relationships sheet. Board members who belong to multiple organizations will have several entries in the Relationships sheet, one for each organization they belong to.
Now, you'll be able to do all sorts of calculations in another sheet. Here are a few that might come in handy based on what you're trying to visualize: Join tables with INDEX and MATCH.
posted by reeddavid at 3:08 PM on February 2, 2019
1. Board Members
2. Organizations
3. Relationships
List each person and organization only once in their respective sheets, and include a unique ID for each one.
Then, list each person-organization relationship in the Relationships sheet (e.g. Column A: Member ID, Column B: Organization ID). And while you're at it, create a unique ID for each row in this Relationships sheet. For a board member who belongs to only one organization, they'd have only one row in the Relationships sheet. Board members who belong to multiple organizations will have several entries in the Relationships sheet, one for each organization they belong to.
Now, you'll be able to do all sorts of calculations in another sheet. Here are a few that might come in handy based on what you're trying to visualize: Join tables with INDEX and MATCH.
posted by reeddavid at 3:08 PM on February 2, 2019
I think oceano's link to NodeXL is borked, try: NodeXL: network analysis & insights as easy as pie charts.
posted by zengargoyle at 12:59 AM on February 3, 2019
posted by zengargoyle at 12:59 AM on February 3, 2019
this would be perfect for Airtable -- not a very programmer-y solution but it's simple and quick
two tables in a base: People and Orgs; you can make a linked-record column (select multiple option checked) on People to Orgs and then you have the data in both views.
posted by wikipedia brown boy detective at 5:58 AM on February 3, 2019
two tables in a base: People and Orgs; you can make a linked-record column (select multiple option checked) on People to Orgs and then you have the data in both views.
posted by wikipedia brown boy detective at 5:58 AM on February 3, 2019
This thread is closed to new comments.
Otherwise, two columns: "Board Member", "Organization". A row for each relationship between Board Member and Organization.
Uniques of "Board Member" column is all the board members.
Uniques of "Organization" column is all the organizations
Feel free to then use a PivotTable or Chart to get the list of each of the Board Members for Each Org, or the list of Orgs for each Board Member.
e.g.,
Alice, Org X
Alice, Org Y
Bob, Org Y
Bob, Org Z
Charles, Org Z
posted by miasma at 11:37 AM on February 2, 2019