2
$\begingroup$

http://pastebin.com/K0eq8cyZ

I went through each season of "It's Always Sunny in Philadelphia" and determined the character groupings (D=Dennis, F=Frank, C=Charlie, M=Mac, B=Sweet Dee) for each episode. I also starred "winners" for some episodes. How best could I organize this data, in what type of database, and what data science tools would extract the most information out of it?

I was thinking of making an SQL table like so:

             (1)     (2)      (3)     (4)     (5)
Episode# | Dennis | Frank | Charlie | Mac | Sweet Dee 
008      |    5   |  3,4  |  2,4    | 2,3 |    1
010      |    5   |  3,4,6|  2,4,6  |2,3,6|    1  

...where all the values are arrays of ints. 6 represents that the character won the episode and each number represents one of the 5 characters. Thoughts?

$\endgroup$
  • $\begingroup$ You could certainly use a relational database, but what is the goal; what do you want to know? $\endgroup$ – Emre Jan 26 '16 at 1:28
  • $\begingroup$ There's no real goal other than detect patterns. For instance, which character combos produce the best episodes? Ultimately I'd have another table with episode data like imdb rating, episode writers, topic tags, etc. Also stuff contained within this dataset alone like what is each characters "affinity" to each other character calculated as a summation of each time they're paired, whether or not they won, etc This is ultimately for fun and practice. Also any ideas on the best way to visually represent this data? Something in Tableau perhaps? $\endgroup$ – Austin Capobianco Jan 26 '16 at 1:41
2
$\begingroup$

How best could I organize this data, in what type of database?

A simple relational database should do, but you could also use a "fancy" graph database if you want. One table for the users, and one for the "interactions". Each interaction would have foreign key columns for the two participants, labeled winner and loser, and the number of the episode the interaction it occurred.

Also any ideas on the best way to visually represent this data?

A graphical representation for social network analysis suggests itself. Here are some papers and a subreddit for inspiration. In your case, there is a concept of competition with clear winners/losers, so you could make your graph directed. Have the characters be the nodes, and add directed edges from the winning party to the losing party for each interaction. Collapse repeated interactions, etc. This approach would let you quickly identify overall winners and losers, as well as simply who interacts with whom.

$\endgroup$
  • $\begingroup$ Social network analysis does make sense. Without even considering winners/losers, I would tally each time there's an interaction and set those values equal to their corresponding connection in the network, right? Could I give them different weights based on whether or not it was a pair or a large group? Also because there's only 5 nodes and a hundreds of repeated interactions, how do I represent the change in time? Does that even matter? $\endgroup$ – Austin Capobianco Jan 26 '16 at 22:49
  • $\begingroup$ I don't quite understand your suggestion for a fancy graph database. Are the users the 5 characters? If so would I need to make columns for every possible combination of 5 characters? $\endgroup$ – Austin Capobianco Jan 26 '16 at 22:51
  • $\begingroup$ Yes, the users/characters are the nodes of the graph/network. I assumed interactions were in pairs, but if one person can compete with several you'll have to create an entry for every pair. Weights could be used for quantifying the magnitude of the win/loss, if applicable. If you want to analyze the evolution of graph, you can tag each edge with the episode number, then use that as an input for temporal graph analysis. Here are some leads in case you are interested in temporal analysis. $\endgroup$ – Emre Jan 26 '16 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.