This week, we’ll be talking about connections, both the in-person links that create crowds and the digital ones that create, well, the Internet.

Readings

The term “the wisdom of crowds” was popularized by James Surowiecki, but it’s been around for a while. Some take issue with the idea that crowds actually have any particular wisdom; a crowd, after all is just a thrown rock away from a mob. Here’s a little tune on the subject from Nova:

Bill Thompson talks about links as the key component of “the semantic Web.” This one’s a little wonky, but at its heart is the idea that the meaning of a link comes from how it’s used rather than just where it goes.

So how do you write good link text? Start with this rule of thumb: “link text should always describe what the user will see when they click on it.” Avoid “click here“or actually posting the full URL. Try some of these strategies for writing quality links (from Harvard – fancy!) and drawing search engine hits (scroll down – the first part is more about not using “click here,” and I think you know that by this point).

You will need to post your response as a comment to this post no later than 11:59 p.m. Sunday, Sept. 8. Keep it concise and relevant, and provide some useful examples!

Personal Post

Write a blog post synthesizing (and linking) ideas from several members of your blogroll. As always, it must be relevant to your personal blog concept and must include:

At least three links (more is better) to meaningful content. This means news stories, relevant posts, and substantive material, NOT to homepages (e.g., wvu.com) or general sites (e.g., facebook.com)

This week we delve into data. You’re surrounded by it, but do you know how to use it as a blogger? As a journalist? As we discussed in our Mobility week, we’re increasingly devoted to technologies that track our movements, habits, and preferences, and these trackers produce a wealth of data.

Consider Wikileaks, arguably the game-changer in data journalism. Approached with a massive wealth of data, The Guardian compiled phenomenally complex accounts of wars in Afghanistan, Iraq, and a collection of cables (communication dispatches) from the U.S. Embassy. Not only this, they made the data itself available to readers to make their own stories out of it. Most recently, they’ve leaked data about the Democratic National Committee and its candidate, Hillary Clinton. Some have described this as a test of transparency; others have accused them of pursuing vendettas. Outside the realm of partisan politics, they’ve been criticized for their unwillingness to obscure private information such as email addresses and credit card numbers. Even fellow leaker Edward Snowden took some issue with this:

Democratizing information has never been more vital, and @Wikileaks has helped. But their hostility to even modest curation is a mistake.

When you’re working with any spreadsheet (and especially one with a few thousand entries, like our WVU crime stats), it’s helpful to know some ways to work with that data. The real power of Excel is the way it allows you sort, count, and otherwise crunch those piles of data into more manageable pieces. Here are a few we’ve gone over in class that should help you winnow your selections down to something more comprehensible: Sort, COUNT, SUM, and WEEKDAY.

Before we start: Several of these techniques require the use of functions. In Excel, any cell that uses data in other cells must start with an “=” sign. For example, if you wanted a cell to show the value of cells A1 and B1, you’d enter “=A1+B1” (without the quotation marks). Functions work like this but are more powerful and have special names. For example, if you were using the “SUM” function, you’d enter “=SUM()” – the specifics of the SUM function would be included in those parentheses, which will be explained below.

1. Sort

First, select all of your data (Command+A), then go to Data > Sort. This gives you the option to sort by any of your data columns. Make sure you click the “Header Row” box (you don’t want your headers to get moved off the top row). Depending on the version of Excel you have, you will then be able to sort by the name of the column (e.g., “Date Occurred”) or by its letter (e.g., “Column A”) in either Ascending or Descending order.

Next steps: Once you’re comfortable with basic sorting, multi-level sorting can help you really break down the data. For example, if you sort by “Location,” then by “Date,” then by “Time,” you’ll get an alphabetical listing of each recorded location grouped by date, then time (for example, you’d get chronological listings for Boreman Hall, then Braxton, and so on).

2. COUNT functions

Once sorted, the next thing you’ll want to do is count the kinds of data you’re interested in. This will be your first function. There are two kinds of Count functions: “COUNT” and the more powerful “COUNT.”

COUNT: This returns how many cells in a range (e.g., from cell A1 to A100) have a value. To find this, you’d enter “=COUNT(A1:A100)” (no quotes). The formula will then tell you how many cells in that range are not blank.

COUNTIF: This more powerful version of COUNT requires a range and a condition. Let’s say you wanted to know how many cells in the A1 to A100 range are Liquor Law violations. You’d enter “=COUNTIF(A1:A100,”Liquor Law”)”. IMPORTANT: The second argument (“Liquor Law” in this case) MUST have quotes around it!

Next steps: You can do numerical conditional statements as well. For example, if cells B1 to B100 listed numbers of reports, and you wanted to count every cell with more than five arrests, you’d enter “=COUNTIF(B1:B100,”>5″)”. Once again, those quotes MUST be around “>5” and you MUST include the comma between the range (B1:B100) and the conditional statement (“>5”).

[You can also use this to count entries after a certain time – to count all Times after 8 p.m., you could enter “=COUNTIF(A1:A100,”>20:00:00″). Note, however, that this only counts from 8 to midnight, so if you wanted all overnight hours, you’d also need to do a COUNTIF for 00:00:00 to 00:06:00, then add the two counts together.]

3. SUM functions

Once you’ve done your counting, you might want to add up some cells. For example, let’s say you’ve counted Towing Assists separately for each month in cells C1 to C12. You could add these one-by-one (e.g., “=C1+C2+C3+…C12”), but it’s a lot easier to use SUM. To do this, enter “=SUM(C1:C12)”. The formula reports the sum for all cells in the range.

There’s also SUMIF, a conditional formula that works a bit like COUNTIF but requires three arguments: range, condition, and range to sum (that means two different columns). It’s a little more complicated, so use it carefully. Let’s say cells D1-D100 is Locations and cells E1-E100 is number of citations issued. If you wanted to see how many citations had been issued for Summit Hall, you’d enter “=SUMIF(D1:D100, “Summit Hall”, E1:E100)”.

[How’s that different from SUMIF? COUNTIF only counts the number of cells in your range that have values; SUMIF totals the values in those cells. For example, if your cell values were “2 2 3 4 2” COUNTIF would return “5” but SUMIF would return “13”.]

Next steps: Excel offers many similar mathematical functions of this type: AVERAGE reports the average of a range, MAX and MIN report the largest or smallest value in that range, and so on. I won’t list them all here, but if there’s a numerical function or calculation you’re looking for, there’s a good bet Excel has it.

4. WEEKDAY function

You’ll like this one. Right now our data has dates but not days of the week, something you’d probably like to know (for example, if you’re listing all Friday and Saturday crimes). If cell A1 is a date, you’d enter the formula “=WEEKDAY(A1)”. The formula would return 1-7, with 1=Sunday through 7=Saturday.

To find this for all your data is also easy. In the first cell of a blank column on your data spreadsheet, enter the formula for that row (e.g., “=WEEKDAY(A1)”). Select the cell and copy it, then use shift to select that column from its first cell to the last, then Paste; Excel will copy the formula to all cells and will automatically update it for each row. Now you can sort by day of the week as well!

That seems like enough for you to do some damage with. We’ll talk more about what you’re working on this Tuesday.

Thus far, our forays into data have dealt with simple summary data from the 2010 U.S. Census. Next week we’ll start working with data of our own collection. You’ve probably heard about the unsettling number of public beatings here at WVU this semester – it’s only been a few, but it seems like an unsettling trend. But IS it a trend? To learn more, we’ll be working with WVU Campus Police incident data.

This must be completed by the start of class on Thursday, Sept. 13. In addition, you must bring a printout of your completed Excel spreadsheet (not the master Google docs spreadsheet) with your name and assigned month on it.