Charting traffic in bangalore

Wednesday, April 19, 2006

For my upcoming session at Barcamp, I took the last four years of posts and comments stats for

bangalore and made a graph. I’ve never made a graph before and took the obvious route of Excel. This is ugly. It hurts my eyes. Can someone make it pretty?

mannu? Here’s the excel sheet, raw data (per post, monthly), and source for downloading calendar pages (sh) and parsing into CSV (py). Four posts were backdated (start here); I manually added them to the excel sheet based on the date of the first comment to each. Deleted posts—which were among the most interesting ones—are unfortunately not countable.

The community’s most active period was between May 2004 and May 2005. Comment spikes appear to correlate with post spikes. If both are redrawn to a percentage scale using daily instead of monthly stats, it should be easier to identify the volatile periods. How do I do that in Excel? What charting app should I use?

That huge spike in April 2005 (1209 comments) is clearly the work of our friends at

wearesphinx. It should be interesting to relate other spikes with specific events:

Within the community (above mentioned friends and their brethren),
Within LiveJournal (invite codes removed Dec 2003, spike from 81 to 236 comments in Jan 2004, remains above 100 henceforth), or
Elsewhere (Rajkumar dies, riots in Bangalore Apr 2006, 535 comments).

Anyone game for playing tag?

clueless_rebel — Apr 19, 2006 9:15:50 PM — # ↩

Not sure if this'll be of any help,but..
I use this site http://nces.ed.gov/nceskids/graphing/ to create graphs for my projects.
Good Luck!
bluesmoon — Apr 19, 2006 9:51:48 PM — # ↩

looks like an excellent excercise for the Yahoo! term extractor
ravi — Apr 19, 2006 11:14:51 PM — # ↩

Have you looked at Rails? They've got a graphing package that makes really sweet graphs for not much effort, from what I know.
- jd_knight — Apr 19, 2006 11:23:48 PM — # ↩
  
  yupp.. 'Gruff'
  ..and its really cool! http://nubyonrails.com/pages/gruff
- ga_woo — Apr 19, 2006 11:53:40 PM — # ↩
  
  Ruby! Ruby! Not Rails! Rails is just a web framework.
  - jd_knight — Apr 20, 2006 1:51:44 AM — # ↩
    
    well, by saying Rails, isnt the Ruby part implied :p but yes, Gruff has nothing to do RoR specifically, i agree.
    
    and yes, these demo graph pics generated using gruff are worth drooling over.. !
    http://geoffreygrosenbach.com/projects/show/5
    - ga_woo — Apr 21, 2006 1:16:33 AM — # ↩
      
      Gruff isn't even included with Rails.
sumit_mittal — Apr 20, 2006 4:22:53 AM — # ↩

has some1 sent the beautified one, or do u still need it?
- Kiran Jonnalagadda — Apr 20, 2006 7:41:01 AM — # ↩
  
  Still needed. I'm getting all sorts of other graphs.
  - sumit_mittal — Apr 21, 2006 12:28:51 AM — # ↩
    
    you just need it beautified, right?
    - Kiran Jonnalagadda — Apr 21, 2006 8:38:02 AM — # ↩
      
      Yes. mannu just sent me a nice Flash version.
mannu — Apr 21, 2006 3:13:57 AM — # ↩

See if this (SWF) works for you.
- Kiran Jonnalagadda — Apr 21, 2006 8:37:30 AM — # ↩
  
  Thanks, that looks great, but can't I use it from disk? I get an endlessly spinning clock.
  - mannu — Apr 21, 2006 1:59:22 PM — # ↩
    
    It's probably stuck trying to load "monthly.csv" from the current directory. I would've expected it to give an error at some point. Anyway, so try putting the monthly.csv file in the same directory you're running it from.
    
    You might still run into Flash Player security restrictions (can't load local file from disk) if you have a newer version of the player. If that happens, try running it from a local HTTP server.
ashwinne — Apr 23, 2006 8:46:59 PM — # ↩

Just a thought on useless posts
If we presume that useless flamebait posts have a high anon-user/reg-user comments ratio, then it should be easy to automatically generate a list of such posts from the database (and also to mark them on the graph). This also means that such posts can be subtracted from the total and we get the useful posts. But, all this holds only if my premise is true.
- Kiran Jonnalagadda — Apr 23, 2006 9:21:35 PM — # ↩
  
  Re: Just a thought on useless posts
  But that assumes the useless posts are truly useless. Could it be their entertainment value is responsible for enough people hanging around to answer the boring/useful posts?
  - ashwinne — Apr 23, 2006 9:31:02 PM — # ↩
    
    Re: Just a thought on useless posts
    I see. A balanced ecosystem.
Anonymous — Apr 24, 2006 3:38:07 PM — # ↩

Why not try zohosheet.com
Your excel sheet could be read online with zohosheet.com . Hope you would love it and if you are still interested go check out www.zoho.com