A first step to being able to do analysis with up to date data is to import the data from the API. After that there needs be a framework for working efficiently with the data in Mathematica. In my opinion, the obvious technology for this is SQL. Ultimately all data in the API should be downloaded and stored locally so that it's easy to work with, however I decided to start with just data about users to exemplify.
This code will set up an HSQL database with the 100 users that have most reputation:
data = "items" /.
Import["https://api.stackexchange.com/2.1/users?pagesize=100&order=\
desc&sort=reputation&site=mathematica&filter=!9f8L71Vn(", "JSON"];
userColumns = {"accept_rate", "account_id", "answer_count", "creation_date",
"display_name", "down_vote_count", "last_access_date", "last_modified_date",
"link", "location", "profile_image", "question_count", "reputation",
"up_vote_count", "user_id", "view_count"};
Needs["DatabaseLink`"];
JDBCDrivers["HSQL(Standalone)"];
conn = OpenSQLConnection[
JDBC["HSQL(Standalone)",
ToFileName[{$UserAddOnsDirectory, "Applications", "DatabaseLink", "Custom"},
"stackexchange"]]];
SQLExecute[conn, "DROP TABLE users IF EXISTS"];
SQLExecute[conn, "DROP TABLE users_badges IF EXISTS"];
SQLCreateTable[conn, "users", {
SQLColumn["accept_rate", "DataTypeName" -> "Float"],
SQLColumn["account_id", "DataTypeName" -> "Float"],
SQLColumn["answer_count", "DataTypeName" -> "Float"],
SQLColumn["creation_date", "DataTypeName" -> "Float"],
SQLColumn["display_name", "DataTypeName" -> "Varchar",
"DataTypeLength" -> 256],
SQLColumn["down_vote_count", "DataTypeName" -> "Float"],
SQLColumn["last_access_date", "DataTypeName" -> "Float"],
SQLColumn["last_modified_date", "DataTypeName" -> "Float"],
SQLColumn["link", "DataTypeName" -> "Varchar",
"DataTypeLength" -> 256],
SQLColumn["location", "DataTypeName" -> "Varchar",
"DataTypeLength" -> 256],
SQLColumn["profile_image", "DataTypeName" -> "Varchar",
"DataTypeLength" -> 256],
SQLColumn["question_count", "DataTypeName" -> "Float"],
SQLColumn["reputation", "DataTypeName" -> "Float"],
SQLColumn["up_vote_count", "DataTypeName" -> "Float"],
SQLColumn["user_id", "DataTypeName" -> "Float"],
SQLColumn["view_count", "DataTypeName" -> "Float"]
}];
SQLCreateTable[conn, "users_badges", {
SQLColumn["user_id", "DataTypeName" -> "Float"],
SQLColumn["gold", "DataTypeName" -> "Float"],
SQLColumn["silver", "DataTypeName" -> "Float"],
SQLColumn["bronze", "DataTypeName" -> "Float"]
}];
SQLInsert[conn, "users",
userColumns, (userColumns /. data) /. "accept_rate" -> 100];
SQLInsert[conn,
"users_badges", {"user_id", "bronze", "gold", "silver"},
Flatten /@ ({"user_id", "badge_counts"} /. data) /.
Rule[type_, value_] :> value];
Reputation per up vote (view result here):
TableForm[
SQLExecute[conn,
"SELECT display_name, reputation/up_vote_count as ratio
FROM users
ORDER BY ratio ASC"],
TableHeadings -> {Range[100], {"Display name", "reputation/number of up votes"}}]
Number of silver badges to the number of bronze badges (view result here):
TableForm[
SQLExecute[conn,
"SELECT users.display_name, users_badges.silver/users_badges.bronze as ratio
FROM users
INNER JOIN users_badges
ON users.user_id = users_badges.user_id ORDER BY ratio DESC"],
TableHeadings -> {Range[100], {"Display name", "number of silver / number of bronze"}}
]
Number of answers to the number of questions (view result here):
TableForm[
SQLExecute[conn,
"SELECT display_name, answer_count/question_count as ratio
FROM users
ORDER BY ratio DESC"],
TableHeadings -> {Range[100], {"Display name", "number of answers/number of questions"}}
]
Reputation to the number of posts, that is answers and question (view result here):
TableForm[
SQLExecute[conn,
"SELECT display_name, reputation/(answer_count+question_count) AS ratio
FROM users
ORDER BY ratio DESC"],
TableHeadings -> {Range[100], {"Display name", "reputation/(number of answers+number of questions)"}}
]
Who of the top 100 is still around and who is not? (view result here):
TableForm[
Map[ (* Don't forget to adapt the next line to your time zone *)
{#[[1]], DateString@DateList[AbsoluteTime[{1970, 1, 1, 2, 0, 0}] + #[[2]]]} &,
SQLExecute[conn,
"SELECT display_name, last_access_date
FROM users
ORDER BY last_access_date DESC"]
],
TableHeadings -> {Range[100], {"Display name", "Last access date"}}
]
Most popular profile pages (view result here):
TableForm[
SQLExecute[conn,
"SELECT display_name, view_count
FROM users
ORDER BY view_count DESC"],
TableHeadings -> {Range[100], {"Display name", "View count"}}
]
It will not be possible to check activity in the chat (in this way) because that information is not part of the API.
I would ordinarily be able to do unix timestamp to datetime specification conversion in HSQL directly. I would also not have to use the datatype float for integers to floating point results from division, ordinarily, but something is preventing me from using the appropriate SQL functions, setting the appropriate database settings. I suspect the problem is in the database link.
Update: Using data about answers
Here are some additional stats based on data from the API about answers. First we start by creating a table. This table will not store the display name of users, only their IDs, so the users table that we have already defined is still necessary.
Also note that importing this data involves one API call per member, that is 100 HTTP requests, which means that it isn't instantaneous.
answers[id_] := "items" /. Import["https://api.stackexchange.com/2.1/users/" <> ToString[Round[id]] <> "/answers?pagesize=100&order=desc&sort=activity&site=mathematica&filter=!FqMlbo.zpmvyqEyG)reQTHEWoV", "JSON"];
answersColumns = {"owner", "question_id", "answer_id", "is_accepted", "up_vote_count", "down_vote_count", "creation_date"};
userIDs = Flatten@SQLExecute[conn, "SELECT user_id FROM users"];
SQLExecute[conn, "DROP TABLE answers IF EXISTS"];
SQLCreateTable[conn, "answers", {
SQLColumn["user_id", "DataTypeName" -> "Float"],
SQLColumn["question_id", "DataTypeName" -> "Float"],
SQLColumn["answer_id", "DataTypeName" -> "Float"],
SQLColumn["is_accepted", "DataTypeName" -> "Boolean"],
SQLColumn["up_vote_count", "DataTypeName" -> "Float"],
SQLColumn["down_vote_count", "DataTypeName" -> "Float"],
SQLColumn["creation_date", "DataTypeName" -> "Float"]
}];
SQLInsert[conn, "answers", answersColumns /. "owner" -> "user_id", answersColumns /. answers[#] /. {Rule["user_id", id_]} :> id] & /@ userIDs;
How many, out of the 100 most recent answers, have been accepted (view result here):
TableForm[
SQLExecute[conn,
"SELECT users.display_name, COUNT(answers.is_accepted) AS c
FROM answers
INNER JOIN users ON users.user_id = answers.user_id
WHERE answers.is_accepted = True
GROUP BY users.display_name
ORDER BY c DESC"],
TableHeadings -> {Range[100], {"Display Name", "Nr. of accepted"}}
]
The average number of "competitive answers" each answer a user has given has (view result here):
competitiveAnswers[id_] := First@First@SQLExecute[conn,
"SELECT COUNT(*)
FROM answers
WHERE question_id IN
(SELECT question_id FROM answers WHERE user_id = " <> ToString[id] <> ") AND user_id != " <> ToString[id]
]
nrOfAnswers[id_] := First@First@SQLExecute[conn, "SELECT COUNT(*) FROM answers WHERE user_id = " <> ToString[id]]
TableForm[Reverse@SortBy[MapAt[N[competitiveAnswers[#]/nrOfAnswers[#]] &,
SQLExecute[conn, "SELECT display_name, user_id FROM users"], {All,2}], Last],
TableHeadings -> {Range[100], {"Display name", "Number of competitive answers/number of answers"}}
]
Another SQL query is this:
SQLExecute[conn, "
SELECT user_id, COUNT(user_id)
FROM answers
WHERE question_id IN (SELECT question_id
FROM answers
WHERE user_id = 731)
AND user_id != 731
GROUP BY user_id"]
My user id is 731, so this query tell me that user 89 has answered four questions that I have also answered, accounting only for the 100 most recent answers I've given. It tells me that user number 1194 has answered one question that I also answered.
Communities!
Thanks to the code belisarius posted in the comments we can find out what different communities, found out using the spectral option with FindGraphCommunities, exist. The weights in the graph between two users is how many of the questions, of the most recent 100 answers user one given, has user two also answered.
r = IntegerPart@
DeleteDuplicates[
Flatten[SQLExecute[conn,
StringReplace[
" SELECT X, user_id, COUNT(user_id) FROM answers WHERE \
question_id IN (SELECT question_id FROM answers WHERE user_id = X) \
AND user_id != X GROUP BY user_id", "X" -> ToString[#]]] & /@ userIDs,
1], (#1[[1]] == #2[[2]] && #1[[2]] == #2[[1]]) &]; g =
Graph[UndirectedEdge @@@ r[[All, 1 ;; 2]],
EdgeWeight -> Flatten[r[[All, 3]]]]; rul =
Rule @@@ SQLExecute[conn,
"SELECT user_id,display_name FROM users"]; groups =
1. FindGraphCommunities[g, Method -> "Spectral"] /. rul;
CommunityGraphPlot[g, FindGraphCommunities[g, Method -> "Spectral"], PlotLegends -> Placed[Automatic, Below]]
Where the groups are given by
TableForm[
StringJoin[Riffle[#, ", "]] & /@ groups,
TableHeadings -> {Range[Length[groups]], {}}
]
View the groups here.
