Category Archives: Data

Private Comments via XMLIMPORT

Making shareable (Sharing with a single person or specific group but not with the world.) comments on public writing is a fairly awkward spaaaaaace right now. There are things like AnnotateIt and Awesome Screenshot and the annotations in Diigo. So I’m looking around for other free options and brain storming odd ideas and not find a whole lot and I came up with the following . . .

Note: I’m not saying this is a good idea, it may even be a bad idea but it might inspire someone to do something more interesting down the line.1 I at least found it mildly amusing.

Here’s how you might pull an author feed from WordPress into Google Spreadsheets with separate cells each paragraph (for paragraph level commenting). The idea being that you can share the Google document with just that student and do the commenting via the GSS commenting feature.

Google spreadsheets will import lots of things (xml, atom, rss). WordPress provides lots of specific feeds (author, tag, categories, combinations thereof).

So step one is to get the author feed – for example http://rampages.us/fren330/author/sheehantm/feed/. You can then use the IMPORTXML formula in GSS to import that XML and do some XPATH parsing of the pieces. In this case I used =IMPORTXML(“http://rampages.us/fren330/author/sheehantm/”,”//p”) to pull out the paragraphs. I can then share the document with just that student and comment on the paragraphs using the Insert>Comment stuff built into GSS. You can see an image of that below and below that is the embedded document in all its odd glory/shame.

Screen Shot 2014-09-17 at 3.38.45 PM


1 I think it makes a nice cheat to be able to analyze text from an RSS feed using more commonly held SS skills. There are a few other things I’m kicking around in my head as well.

Little Trick, Big Numbers

Election night crowd, Wellington, 1931

I often want to know just a bit more about numbers I see in tables. As I was looking at some thing today, I stumbled on the Wikipedia page for “List of Most Viewed YouTube Videos“. After being more than a bit amazed at the utterly staggering numbers. I wanted to know what they translated to in terms of years because the numbers were just too big.

I remembered that Google Spreadsheets will let you pull in a table from a website with no fuss. All I needed to do was put =IMPORTHTML(“http://en.wikipedia.org/wiki/List_of_most_viewed_YouTube_videos”,”table”,1) in the first cell on the spreadsheet and viola the table is transcluded. I can now add a few more calculations to figure out the import stuff – like how many years worth of time have been spent watching Gangnam Style (16,274.24 years for the record1). You can go mess around with the data here.


1 Assuming I didn’t screw something up.

Citation Workflow – Diigo/Pinterest to Google SS

Talking to Bud the other day he mentioned that generating the citation page for his digital stories was something of a pain. I’ve thought about it a bit since then and decided to try to simplify a workflow for this.

Odd thing I learned – – CHAR(10) is the official way to get line breaks in Google Spreadsheet formulas.

Flickr to Diigo to Google Spreadsheets

Initially, I looked at the Flickr galleries because that’s the option that Bud normally uses. I saw that the gallery was in a standard HTML list format and I had some hope. Google spreadsheets lets you pull lists and tables like these in via the IMPORTHTML function. Martin Hawksey has some good instructions and examples over here. So that failed but I could import just about every other list on the page.

So, I decided doing this through Diigo would make pretty decent sense for a number of people.

Assuming you choose a unique tag for the images you plan to use- this example just uses “flickr”, I’d suggest something story/movie specific. So the basic Diigo URL you’d get is https://www.diigo.com/user/bionicteaching/flickr. Trying to make this really easy for people, I set up the first page to allow you to paste that URL in and our friendly formulas transform it into https://www.diigo.com/rss/user/bionicteaching/flickr.

The example linked here reformats the RSS feed into something like what’s below. Making it really easy to cut/paste into credits or publish as a webpage and link to in your video description. With minor effort you could make it even prettier or assume a different format. If it interests you, click here and choose FILE>MAKE A COPY to have one of your very own.

"KONICA MINOLTA DIGITAL CAMERA | Flickr - Photo Sharing!
By user: edbatista

KONICA MINOLTA DIGITAL CAMERA

Retrieved on Sun, 30 Mar 2014 00:31:25 +0000"

Pinterest to Google Spreadsheets

I did get to thinking that Diigo is not the most visual of bookmarking options and wondered if I might be able to do something similar using Pinterest. Turns out, only sort of.

You can get the RSS feed in no problem. You just add RSS to the end of the board URL and you’ve got a feed. It loads fine into the spreadsheet . . . but it doesn’t hold much info.

You can get the date/time of pinning, the description, and the URL to the pit itself (which does site the source and provide a link back to the original- but none of that is in the RSS feed).

I’m not overly impressed. I may revisit later with a little more effort and something beyond a vanilla Google spreadsheet. In any case, if you want a copy here it is.

Text Acrobatics in Google Spreadsheets

I’ve established here enough times I’m not a programmer so I have to find ways to get things done until I learn more.

I found this gigantic list of edtech related conferences compiled by Dr. Wright thanks to Stephen Downes’ feed. It’s in Word1 for a variety of reasonable reasons and I can’t fault anyone who puts in this kind of time and energy and puts the result out there for free. It does make it harder to manipulate but it is very consistent which opens up doors which might otherwise be closed. It does cut/paste into a Google Spreadsheet pretty well.

The key to things like this is finding ways to break them into pieces. It is really algebra and variables but a more entertaining version. You can chop pieces of the block up and then chop up those parsed out pieces.

For convenience’s sake we’ll use cell A2 as the housing for the unparsed information.

December 1-2, 2013 International Conference on Advanced Education Technology and Management Science (AETMS), Hong Kong, China. http://www.aetms.org/

The first thing I did was scan for consistencies that I might use as chop points. The date is always first and in most cases it’s one word (the month) followed by the dates and a comma.

That allows me to do things like =FIND(” “,A2). That will give me the number of the first space- in this case 9 (written in cell B2). Now no matter what the first month is I can pull it out as a separate piece by putting in a formula like =LEFT(A2,B2). That gives me the first X letter of whatever is in cell A2.

I can also get fancy and find the second space. Something like =FIND(” “,A2,B2+1). That will start us at one character beyond the first space and will find the next space in the sequence.

Other useful tricks include =LEN(A2) which will tell you how long the text is (148 in this case and written in cell C2). Now if I =FIND(“http”,A2), I’ll get 128 (written in cell D2). So now by subtracting (148-128) we know the URL is 20 characters long and since it’s at the end of the line we can get it by =RIGHT(A2,(C2-D2)). If it wasn’t at the start/end of a line, you can opt to use =MID and play with those variables.

In any case, I have little hope this makes sense and slightly less hope anyone would struggle through it but the spreadsheet is here and while you can’t edit it you can see the formulas and comment. I have done some erroneous/useless parsing that needs to be pulled out but that’s what weekends are for.

The reason, of course, that I did this was to pull the information in Exhibit. The cramped version is below and the(seems to break things so it’s now gone) full version is over here. The example has December through March. I may do the rest at some point.


1 Although I believe a Word table or Excel might have all the advantages and allow for easier transitions to other formats.

Calendar as Unifier

I touched on this with a previous zombie pictures post. Essentially, metadata is awesome because it lets people find your stuff and it helps your stuff find its audience. Metadata is also absent more often than not because people don’t like to type in lots of tags and they especially don’t like to do it on phones.

#vcu #day1

You see elements of this metadata addition becoming automatic- simple things like Instagram (or maybe IFTT) auto-tagging my images with instagram and (in my case) iPhone (like the image above). I’ve also seen auto-tagging of image filters and with exif data you get all sorts of interesting automated metadata details but they tend to be mechanical rather than social. IFTT, FeedWordPress, and others allow you to do some low level of automatic metadata association.

What keeps coming back to me is that it would be relatively simple to enable people to associate calendars and specific calendar events with online media publishing workflows. This would add the socially relevant automated metadata so the audience could find the media. The end goal being audience rather than metadata.). This would work particularly well at institutions which have centralized calendars or in the case of Udell’s Elm City aggregated calendars. Take VCU’s calendar of events as an example. It has time, location, and categorical elements already. You could add elements to the event template or just leave it as is.

Sequentially, you’d pre-associate your calendar(s) with your media account of choice. You’d upload a piece of media. The system would look at the time stamp and/or GPS data from the media and attempt to connect that information to your calendar(s). Those calendar events would have associated metadata elements which you could opt to associate with your media.

It seems like you then use calendars as indices to media elements which would be an interesting reverse exploration. While it wouldn’t be as automated it would also seem relatively simple to add a WordPress plugin that ties into your calendar and allows you to associate blog posts with calendar events for much the same purpose. It’s a little more manual given blog posts aren’t as synchronous in most cases but it still seems valuable.