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?

Improve this question
$\endgroup$
2
  • $\begingroup$ You could certainly use a relational database, but what is the goal; what do you want to know? $\endgroup$
    – Emre
    Jan 26, 2016 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, 2016 at 1:41

1 Answer 1

Reset to default
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.

Improve this answer
$\endgroup$
3
  • $\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, 2016 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, 2016 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, 2016 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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