Lois & Clark Fanfic Message Boards
Previous Thread
Next Thread
Print Thread
#166541 09/26/11 01:41 AM
Joined: Sep 2003
Posts: 124
LaurenW Offline OP
Hack from Nowheresville
OP Offline
Hack from Nowheresville
Joined: Sep 2003
Posts: 124
Get your piping hot fanfic data here![/b] The fanfic database spreadsheet file is current to stories from the archive's [b]April 15, 2012 upload, and is available for you to download and search, sort and filter to your heart's content.

This post updated May 12, 2012. Since this post went up in September 2011, we've added new fields to the spreadsheet, requiring an edit on instructions below. The story word count field (column E) is new, and all other columns have shifted over.

While you can view the data online as a Google doc , you can't do much there beyond simply viewing -- and simply searching by pressing CTRL-F to bring up a search dialog. (Though if you were to upload a copy of the sheet into your own Google docs, you'd be able to do a whole lot more.)

For more powerful sorting and filtering options, you'll want to download the sheet for use in Excel or OpenOffice.

Besides adding another year's worth of stories, we've done some data cleanup. LabRat has been entering new themes, and I've split the title and author fields each in two, which will enable better alphabetical sorts on titles and author names.

The file is currently using three levels of sort -- author last name, author first name and story title -- but you can change that.

Speaking of sorting and filtering, here are some things you can do in Excel or OpenOffice -- a totally free office suite, highly recommended! Spreadsheet gurus, feel free to laugh at what follows. But if you're a spreadsheet novice, like I am, maybe you'll find it helpful. And if you have any tips to share, that'd be awesome.

Excel and OpenOffice work very much alike. I've noted where the steps are slightly different. The most annoying difference is that OpenOffice wants to ignore the freeze we put on the first row, which we're using as a header row to label what the different columns are for.

For the record I'm using Excel 2003 and OpenOffice 3.2.1. If you're using different versions and these directions aren't working out for you, try Googling on things like Excel 2007 filtering tips, Excel 2010 filtering tips, etc.

Now load that file into your spreadsheet program, because here comes the fun....

* Do some simple searching by pressing CTRL-F -- or by clicking Edit and then Find.

You know the word chocolate will show up somewhere in story titles and descriptions, don't you? Typing chocolate in the Search field and clicking the Find button is the fastest way to find it. (Minor difference noted: OpenOffice's Find feature is called Find & Replace in the Edit menu. You don't need to type anything in the Replace field.)

* Sort by story title. Here's how to do it: Click into the cell of any story title and click the A-Z sort button on your toolbar. Ta-da!

* Sort by story size, largest first. Who wrote the longest story hosted at lcfanfic.com? Try this and see:

Click into the cell of any story's filesize field, then click the Z-A sort button on your toolbar to see a list of the stories sorted by size, largest first. It's not LabRat who holds the crown anymore. Sorry, Chief! Your "Masques" is now third in length -- but still an excellent read!

* Sort by author and then by story size -- a two-level sort. If your goal is to settle in with a good, long read from your favorite author, this sort will help you find it.

Click the Data menu, then the option called Sort. In the dialog window that appears, choose the author's last name field from the top pick list, then check the Ascending radio button to the side. For the Then by field underneath, choose file size from the list, then check the Descending button.

(The look of the dialog window differs a little between Excel and OpenOffice. In Excel you can actually choose the column label from the dropdown pick lists -- AUTHORLAST and FILESIZE -- because the header row stays frozen. In OpenOffice, you'll need to choose the column letter: First sort by Column I, the column for author last names, then by Column D, where file sizes are kept.)

Now you're ready to click the OK button to view your new sort.

(OK, technically this should've been a three-level sort: author last name, then by first name, then by file size. I was lazy.)

* Filtering for Next Gen fics rated G. Wracking your brain for a story to let your young nephew read? Isn't everyone? Filtering to the rescue.

Filtering lets you isolate those rows that contain a value or values you're filtering for, temporarily hiding all the other rows. Once you've filtered out a set of stories, you can carry out other actions, such as sorting. There are varying levels of complexity to filtering, but AutoFilter gives you the easiest ride.

Click the Data menu, then the option called Filter, and at right, the pop-out option called AutoFilter. Notice that each cell on the first row now sports a nifty little arrow by its column header. Click the arrow in column N, the TIMEFRAME field, and click to select Next Gen from the list of choices it gives you. Notice your spreadsheet got a whole lot shorter -- scrolling to the end doesn't take nearly as long, and lots of rows seem to be missing. Congratulations -- you've successfully filtered for Next Gen stories!

Let's go further and find those G-rated stories: Click the arrow under column M, the RATING field, and select G. (Nephew may vote for PG13, but stick to your guns -- less explaining you'll have to do to his parents.) And the story selection shrinks again to only Next Gen stories rated G.

Does nephew have a short attention span? Find the shortest stories by doing a sort -- clicking into any FILESIZE field and then clicking the A-Z button. You'll find that Marcus Rowland has written the shortest Next Gen fic that's rated G, "Owls Over Metropolis." Nephew is sure to be a Harry Potter fan, so Marcus' story will be a perfect fit. It's a cute story with a twist!

There's a gotcha here with OpenOffice: If the first row happens to be a story record that goes away after a filter has been performed, you can lose the autofilter selection arrows. To avoid the inevitable freakout (mine was short-lived), be sure to click yes if OpenOffice prompts you about using the first line as the column header.

To turn off autofiltering in Excel, simply click Data/Filter/Autofilter again, and all the hidden rows are restored. In OpenOffice, that also works -- if you can still see those little arrows. If you can't see them, you may need to back up by clicking Edit/Undo, over and over, until all rows are restored. Beating your head with your mouse does nothing to bring them back, trust me.

* Filter for all stories whose title contains the word Lois.

You can't sort for this because the word could appear anywhere in the title. Filtering to the rescue again -- but you'll need to go beyond AutoFilter for this one.

Frankly, Excel's Data/Filter/Advanced Filter option scares me. I can't figure it out and should probably Google for tips, but that's a project for another day. OpenOffice's Advanced Filtering is scary too, but thankfully OpenOffice slips in another level of filtering, called Standard, that makes perfect sense. I'll stick with that for now.

In OpenOffice, click the Data menu, then Filter, then Standard Filter. A more complex-looking dialog with a grid of choices appears, but it's easy to use. On the first row, choose Column G (which has the title) as your field name; choose the option Contains as your condition; and type in Lois as your value. (OpenOffice may try to be helpful and autofill the value field with a story title, but just delete anything beyond the word Lois.) Click OK.

Congratulations! You should now have a filtered list of 151 stories whose titles contain the word Lois.

Now what if you want a group of all stories with titles containing Lois or Ultrawoman? Back to Data/Filter/Standard Filter we go. The first row of the grid should be, as before:

Field name: Column G; Condition: Contains; Value: Lois

On the second row of the grid, use this:

Operator: OR; Field name: Column G; Condition: Contains; Value: Ultrawoman

Click OK, and now we're up to 154 stories. (Clark/Superman wins this contest, by the way: There are 231 stories whose titles contain one of those words.)

* Let's make filtering even more complicated!

For a more complex bit of filtering, make the grid look like this:

Field name: Column G; Condition: Contains; Value: Lois
Operator: AND; Field name: Column T; Condition: Contains; Value: Revelation
Operator: AND; Field name: Column A; Condition: Begins with; Value: KERTH
Operator: AND; Field name: Column K; Condition: Contains; Value: 20
Operator: AND; Field name: Column D; Condition: >=; Value: 100

What'd all that mean? We filtered for a story that: 1) has a title containing the word Lois; 2) and contains the word Revelation in any theme field; 3) and has a value in the award field beginning with KERTH, meaning it won or was nominated for a Kerth award; 4) and was written since the year 2000; 5) and is larger than or equal to 100Kb.

The sole survivor of that filter is Shayne Terry's "Dear Lois," another excellent read. And Shayne's first story in our fandom, if I remember right.

There are many, many complex filter operations you can perform.

OpenOffice gotcha: If your filter operation didn't work, check for leftover values from last time in the grid of choices. You might need to choose -none- from all the Field name rows to clear them out.

Anyway, to those still awake to read this, happy searching, sorting and filtering!

And you Excel/spreadsheet gurus, feel free to jump in here with your own suggestions and tips.

Lauren

Joined: Apr 2007
Posts: 8,934
Likes: 28
Boards Chief Administrator
Nobel Peace Prize Winner
Offline
Boards Chief Administrator
Nobel Peace Prize Winner
Joined: Apr 2007
Posts: 8,934
Likes: 28
I'm gotten a bit of sensory overload right now, so I'll keep to jawdrop cool

Michael


Join us on the #loisclark Discord server! We talk about fanfic, our favorite show, life, and more! (It’s almost like the IRC days of old again!)

I go by Michael on the Archives.
Joined: Jul 2006
Posts: 1,883
M
Merriwether
Offline
Merriwether
M
Joined: Jul 2006
Posts: 1,883
Wowzers! Thanks, Lauren!


lisa in the sky with diamonds
Joined: Feb 2010
Posts: 4,393
Likes: 1
L
Pulitzer
Offline
Pulitzer
L
Joined: Feb 2010
Posts: 4,393
Likes: 1
I'm impressed! To borrow LabRat's sentence: Thank you for all of your hard work on this one.

Joy,
Lynn

Joined: Sep 2003
Posts: 124
LaurenW Offline OP
Hack from Nowheresville
OP Offline
Hack from Nowheresville
Joined: Sep 2003
Posts: 124
Thanks, guys! I hope you're liking the data.

We've updated the spreadsheet with a new column called THEMESCOMBINED, which consolidates themes from the four individual themes column into one for easier filtering. So please ignore what I said earlier about having to run multiple filter operations to find stories by theme -- just do your filtering on Column T. smile

If you're using OpenOffice's Standard filter feature, or have figured out Excel's Advanced filter, it'd be worth downloading the spreadsheet file again for better theme searches.

If you have an iPad, be sure to visit the Google Docs spreadsheet page using it -- you get autofiltering and sorting capabilities built in. This is interesting considering that you don't get these capabilities on the sheet using Internet Explorer, Firefox or Chrome (Google's own browser!). Of course if you were to import the Excel version of the sheet into your own Google Docs account, you'd have full privileges on the sheet as the document owner, and could do sorts and autofiltering in your browser.

Lauren

Joined: Dec 2004
Posts: 1,702
J
Merriwether
Offline
Merriwether
J
Joined: Dec 2004
Posts: 1,702
Wow! Great work. Thanks!


Superman: I hear you've been looking for me.
Lois: All my life.
Joined: Sep 2003
Posts: 124
LaurenW Offline OP
Hack from Nowheresville
OP Offline
Hack from Nowheresville
Joined: Sep 2003
Posts: 124
Looking for a good holiday read? LabRat has been busy updating our fanfic database spreadsheet, tagging Christmas-themed stories, among other things. Want to find all 108 L&C Christmas stories? Download the latest version of the sheet and filter away. (Free OpenOffice Calc is recommended for super-easy sorting and filtering!)

How to filter?

* Load the new sheet into OpenOffice
* Click the Data menu and choose Filter/Standard Filter
* In the Filter criteria dialog, set your options like this...

Field name: Column T
Condition: Contains
Value: Christmas

... and then click OK.

That'll get you a filtered list of Christmas stories on the archive. To read one, just copy the Web address from the URL field (Column C) into your browser.

Merry Christmas and Happy Holidays!

Lauren

Joined: Dec 2005
Posts: 2,445
Kerth
Offline
Kerth
Joined: Dec 2005
Posts: 2,445
One thing I'd add to your suggestions on opening the database spreadsheet - the current version of Open Office isn't available for older Macs e.g. the G3, G4, G5 models and anything else pre the Intel chips) and some other computers with variant processors, but there is another version of the package called LibreOffice that does. It's also free and fully file-compatible with Open Office, I've just checked and it has no problems opening the database.


http://www.libreoffice.org

http://en.wikipedia.org/wiki/Libreoffice


Marcus L. Rowland
Forgotten Futures, The Scientific Romance Role Playing Game
Joined: Sep 2003
Posts: 124
LaurenW Offline OP
Hack from Nowheresville
OP Offline
Hack from Nowheresville
Joined: Sep 2003
Posts: 124
Thanks for the tip, Marcus. I've updated the database page with a link to LibreOffice.

Joined: Sep 2003
Posts: 124
LaurenW Offline OP
Hack from Nowheresville
OP Offline
Hack from Nowheresville
Joined: Sep 2003
Posts: 124
Bumping this back to the top. The fanfic database has been updated again to include all stories up to April 15, 2012.

Notable updates in the last few months:

  • Story word count field has been added (Column E)
  • New sortable upload date field (Column L)
  • Themes combined (Column T) is where you'll want to perform your filter operations when searching on story themes.


Searching/sorting/filtering examples in previous posts have been edited to reflect updated column labels.

Lauren

Joined: Apr 2011
Posts: 9,509
Nobel Peace Prize Winner
Offline
Nobel Peace Prize Winner
Joined: Apr 2011
Posts: 9,509
Wow! Thanks, Lauren, for all your hard work!


VirginiaR.
"On the long road, take small steps." -- Jor-el, "The Foundling"
---
"clearly there is a lack of understanding between those two... he speaks Lunkheadanian and she Stubbornanian" -- chelo.
Joined: May 2011
Posts: 6,142
Likes: 2
Nobel Peace Prize Winner
Offline
Nobel Peace Prize Winner
Joined: May 2011
Posts: 6,142
Likes: 2
Very cool! thumbsup Thanks for all the hard work!


Battle On,
Deadly Chakram

"Being with you is stronger than me alone." ~ Clark Kent

"One little spark of inspiration is at the heart of all creation." ~ Figment the Dragon

Joined: Feb 2010
Posts: 4,393
Likes: 1
L
Pulitzer
Offline
Pulitzer
L
Joined: Feb 2010
Posts: 4,393
Likes: 1
Fantastic. Thanks for all the time and effort you put into this, Lauren. It will prove very useful.

Joy,
Lynn


Moderated by  bakasi, JadedEvie, Toomi8 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.5