{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"!pip install duckdb","metadata":{"_kg_hide-output":true,"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:08:42.987840Z","iopub.execute_input":"2021-06-12T11:08:42.988380Z","iopub.status.idle":"2021-06-12T11:08:53.034439Z","shell.execute_reply.started":"2021-06-12T11:08:42.988294Z","shell.execute_reply":"2021-06-12T11:08:53.033571Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\nimport matplotlib.pyplot as plt\nimport matplotlib as mpl\nimport numpy as np\nimport seaborn as sns\nimport datetime\nimport duckdb\nimport warnings","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:42:58.497317Z","iopub.execute_input":"2021-06-12T11:42:58.497695Z","iopub.status.idle":"2021-06-12T11:42:58.502291Z","shell.execute_reply.started":"2021-06-12T11:42:58.497665Z","shell.execute_reply":"2021-06-12T11:42:58.501662Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%config InlineBackend.figure_format = 'svg'\nsns.set_context(\"notebook\", font_scale=1)\nplt.rcParams.update({\n    'figure.figsize': (10, 4),\n    'axes.facecolor': 'white',\n    'figure.facecolor': 'white'\n})\npd.options.display.max_columns = 1000\npd.options.display.max_rows = 1000\nwarnings.filterwarnings(\"ignore\")","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:42:59.194320Z","iopub.execute_input":"2021-06-12T11:42:59.194765Z","iopub.status.idle":"2021-06-12T11:42:59.209023Z","shell.execute_reply.started":"2021-06-12T11:42:59.194729Z","shell.execute_reply":"2021-06-12T11:42:59.207409Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# load data\n\ndf_competitions = pd.read_csv('/kaggle/input/meta-kaggle/Competitions.csv')\ndf_teams = pd.read_csv('/kaggle/input/meta-kaggle/Teams.csv')\ndf_users = pd.read_csv('/kaggle/input/meta-kaggle/Users.csv')\ndf_team_memberships = pd.read_csv('/kaggle/input/meta-kaggle/TeamMemberships.csv', parse_dates=['RequestDate'])\ndf_submissions = pd.read_csv('/kaggle/input/meta-kaggle/Submissions.csv', parse_dates=['SubmissionDate', 'ScoreDate'])\ndf_forums = pd.read_csv('/kaggle/input/meta-kaggle/Forums.csv')\ndf_forum_topics = pd.read_csv('/kaggle/input/meta-kaggle/ForumTopics.csv')\ndf_forum_messages = pd.read_csv('/kaggle/input/meta-kaggle/ForumMessages.csv', parse_dates=['PostDate'])\ndf_forum_message_votes = pd.read_csv('/kaggle/input/meta-kaggle/ForumMessageVotes.csv')","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:08:54.033389Z","iopub.execute_input":"2021-06-12T11:08:54.033735Z","iopub.status.idle":"2021-06-12T11:12:05.306589Z","shell.execute_reply.started":"2021-06-12T11:08:54.033680Z","shell.execute_reply":"2021-06-12T11:12:05.304827Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# data preprocessing\n\ndf_competitions = df_competitions.query('Slug == \"bms-molecular-translation\"')\ncompetition_id = df_competitions.iloc[0].Id\ndf_teams = df_teams.query('CompetitionId == @competition_id and PublicLeaderboardRank == PublicLeaderboardRank')\ndf_team_memberships = df_team_memberships.query('TeamId in @df_teams.Id')\ndf_users = df_users.query('Id in @df_team_memberships.UserId')\ndf_submissions = df_submissions.query('TeamId in @df_teams.Id')\ndf_forums = df_forums.query('Id in @df_competitions.ForumId')\ndf_forum_topics = df_forum_topics.query('ForumId in @df_forums.Id')\ndf_forum_messages = df_forum_messages.query('ForumTopicId in @df_forum_topics.Id')\ndf_forum_message_votes = df_forum_message_votes.query('ForumMessageId in @df_forum_messages.Id')\ndf_medals = duckdb.query(\"\"\"select coalesce(cast(medal as int), -1) as medal, min(PrivateScoreFullPrecision) min_medal_score, max(PrivateScoreFullPrecision) as max_medal_score\n    from df_teams t\n    join df_submissions s on t.PrivateLeaderboardSubmissionId = s.Id\n    group by medal\n    order by medal\"\"\").to_df()\ndf_medals['color'] = ['lightskyblue', 'gold', 'silver', 'brown']","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:22:24.419867Z","iopub.execute_input":"2021-06-12T11:22:24.420142Z","iopub.status.idle":"2021-06-12T11:22:24.465179Z","shell.execute_reply.started":"2021-06-12T11:22:24.420117Z","shell.execute_reply":"2021-06-12T11:22:24.464252Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print('Total Submissions:', len(df_submissions))\nprint('Total Users:', len(df_users))\nprint('Total Teams:', len(df_teams))\nprint('Total Forum Topics:', len(df_forum_topics))\nprint('Total Forum Messages:', len(df_forum_messages))","metadata":{"_kg_hide-input":true,"_kg_hide-output":false,"execution":{"iopub.status.busy":"2021-06-12T11:31:13.613772Z","iopub.execute_input":"2021-06-12T11:31:13.614131Z","iopub.status.idle":"2021-06-12T11:31:13.624927Z","shell.execute_reply.started":"2021-06-12T11:31:13.614102Z","shell.execute_reply":"2021-06-12T11:31:13.622752Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"select PrivateScoreFullPrecision as score, color\nfrom df_teams t\njoin df_submissions s on t.PrivateLeaderboardSubmissionId = s.Id\njoin df_medals m on s.PrivateScoreFullPrecision between m.min_medal_score and m.max_medal_score\norder by score\n\"\"\").to_df()\nplt.figure(figsize=(6, 8))\nsns.barplot(x=df.score, y=df.index, orient='h', dodge=False, palette=df.color)\nplt.ylim(884, -10)\nplt.xlim(0, 10)\nplt.gca().xaxis.set_major_locator(mpl.ticker.IndexLocator(1, 0))\nplt.gca().yaxis.set_major_locator(mpl.ticker.MultipleLocator(50));\nplt.gca().xaxis.grid(color='lightgrey', linestyle='--', linewidth=1)\nplt.gca().set_axisbelow(True)\nplt.xlabel('Levenshtein Distance')\nplt.title('Private Leaderboard (All)')\nplt.ylabel('Rank');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:16.413389Z","iopub.execute_input":"2021-06-12T11:31:16.413737Z","iopub.status.idle":"2021-06-12T11:31:21.473175Z","shell.execute_reply.started":"2021-06-12T11:31:16.413695Z","shell.execute_reply":"2021-06-12T11:31:21.472003Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%config InlineBackend.figure_format = 'svg'\ndf = duckdb.query(\"\"\"select PrivateScoreFullPrecision as score, color\nfrom df_teams t\njoin df_submissions s on t.PrivateLeaderboardSubmissionId = s.Id\njoin df_medals m on s.PrivateScoreFullPrecision between m.min_medal_score and m.max_medal_score\norder by score\nlimit 100\n\"\"\").to_df()\nplt.figure(figsize=(6, 8))\nsns.barplot(x=df.score, y=df.index, orient='h', dodge=False, palette=df.color)\nplt.ylim(100, -1)\n# plt.xlim(0, 10)\nplt.gca().xaxis.set_major_locator(mpl.ticker.IndexLocator(1, 0))\nplt.gca().yaxis.set_major_locator(mpl.ticker.MultipleLocator(50));\nplt.gca().xaxis.grid(color='lightgrey', linestyle='--', linewidth=1)\nplt.gca().set_axisbelow(True)\nplt.xlabel('Levenshtein Distance')\nplt.title('Private Leaderboard (Top 100)')\nplt.ylabel('Rank');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:21.474543Z","iopub.execute_input":"2021-06-12T11:31:21.474840Z","iopub.status.idle":"2021-06-12T11:31:22.006387Z","shell.execute_reply.started":"2021-06-12T11:31:21.474811Z","shell.execute_reply":"2021-06-12T11:31:22.004844Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"select SubmissionDate as date, count(*) as count \nfrom df_submissions \ngroup by SubmissionDate \norder by Date\"\"\").to_df()\nsns.barplot(data=df, x='date', y='count');\nplt.gca().set_xticklabels(labels=df.date.dt.strftime('%b %d'))\nplt.gca().xaxis.set_major_locator(mpl.dates.WeekdayLocator(interval=2))\nplt.title('Submissions per day')\nplt.xlabel('Date')\nplt.ylabel('Count');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:22.014238Z","iopub.execute_input":"2021-06-12T11:31:22.014548Z","iopub.status.idle":"2021-06-12T11:31:22.626721Z","shell.execute_reply.started":"2021-06-12T11:31:22.014515Z","shell.execute_reply":"2021-06-12T11:31:22.625687Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"select SubmissionDate as date, count(distinct TeamId) as count\nfrom df_submissions \ngroup by SubmissionDate \norder by Date\"\"\").to_df()\nsns.barplot(data=df, x='date', y='count');\nplt.gca().set_xticklabels(labels=df.date.dt.strftime('%b %d'))\nplt.gca().xaxis.set_major_locator(mpl.dates.WeekdayLocator(interval=2))\nplt.title('Teams submitted per day')\nplt.xlabel('Date')\nplt.ylabel('Count');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:22.628104Z","iopub.execute_input":"2021-06-12T11:31:22.628367Z","iopub.status.idle":"2021-06-12T11:31:23.211000Z","shell.execute_reply.started":"2021-06-12T11:31:22.628335Z","shell.execute_reply":"2021-06-12T11:31:23.209404Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"\nwith t1 as (select SubmissionDate as date, min(PrivateScoreFullPrecision) over(order by SubmissionDate) as score \nfrom df_submissions)\nselect distinct * from t1\norder by date\n\"\"\").to_df()\nsns.lineplot(data=df, x='date', y='score')\nplt.axhline(y=df_medals.iloc[1].max_medal_score, color=df_medals.iloc[1].color, linestyle='--', lw=1);\nplt.axhline(y=df_medals.iloc[2].max_medal_score, color=df_medals.iloc[2].color, linestyle='--', lw=1);\nplt.axhline(y=df_medals.iloc[3].max_medal_score, color=df_medals.iloc[3].color, linestyle='--', lw=1);\nplt.ylim(0, 3)\nplt.gca().xaxis.set_major_locator(mpl.dates.WeekdayLocator(interval=2));\nplt.gca().xaxis.set_major_formatter(mpl.dates.DateFormatter('%b %d'))\nplt.xlabel('Date')\nplt.ylabel('Score')\nplt.title('Best score over time');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:23.212361Z","iopub.execute_input":"2021-06-12T11:31:23.212601Z","iopub.status.idle":"2021-06-12T11:31:23.447351Z","shell.execute_reply.started":"2021-06-12T11:31:23.212576Z","shell.execute_reply":"2021-06-12T11:31:23.445734Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"\nselect SubmissionDate as date, PrivateScoreFullPrecision as score, color\nfrom df_submissions  s\njoin df_medals m on s.PrivateScoreFullPrecision between m.min_medal_score and m.max_medal_score\n\"\"\").to_df()\nsns.scatterplot(data=df, x='date', y='score', hue=df.color, palette=df_medals.color.tolist(), hue_order=['lightskyblue', 'gold', 'silver', 'brown'], s=5, alpha=0.5, edgecolor=None)\nplt.gca().xaxis.set_major_locator(mpl.dates.WeekdayLocator(interval=2))\nplt.xlim(datetime.date(2021, 3, 15), datetime.date(2021, 6, 7))\nplt.ylim(0, 5)\nplt.legend([],[], frameon=False)\nplt.xlabel('Date')\nplt.ylabel('Levenshtein Distance')\nplt.title('Submission scores over time');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:55:58.739620Z","iopub.execute_input":"2021-06-12T11:55:58.740041Z","iopub.status.idle":"2021-06-12T11:56:00.215465Z","shell.execute_reply.started":"2021-06-12T11:55:58.740005Z","shell.execute_reply":"2021-06-12T11:56:00.214472Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"\nselect cast(PrivateLeaderboardRank as int) || '. ' || TeamName as team, PrivateScoreFullPrecision as score, color\nfrom df_submissions s\njoin df_medals m on s.PrivateScoreFullPrecision between m.min_medal_score and m.max_medal_score\njoin df_teams t on s.TeamId = t.Id\nwhere PrivateLeaderboardRank <= 50\norder by PrivateLeaderboardRank\n\"\"\").to_df()\nplt.figure(figsize=(8, 10))\nsns.stripplot(data=df, x='score', y='team', hue=df.color, palette=df_medals.color.tolist(), hue_order=['lightskyblue', 'gold', 'silver', 'brown'], s=5, edgecolor=None, alpha=0.5)\nplt.xlim(0, 5)\nplt.gca().tick_params(axis='both', which='major', labelsize=8)\nplt.legend([],[], frameon=False)\nplt.xlabel('Levenshtein distance')\nplt.title('Score distribution by team');","metadata":{"execution":{"iopub.status.busy":"2021-06-12T11:54:44.262021Z","iopub.execute_input":"2021-06-12T11:54:44.262350Z","iopub.status.idle":"2021-06-12T11:55:01.996203Z","shell.execute_reply.started":"2021-06-12T11:54:44.262321Z","shell.execute_reply":"2021-06-12T11:55:01.994914Z"},"_kg_hide-input":true,"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"\nselect PerformanceTier, count(*) as count from df_users\ngroup by PerformanceTier\norder by PerformanceTier\n\"\"\").to_df()\ndf['PerformanceTier'] = df['PerformanceTier'].map({0: 'Novice', 1: 'Contributor', 2: 'Expert', 3: 'Master', 4: 'Grandmaster'})\ndf = df.set_index('PerformanceTier')\n\ndf['count'].plot.pie(colors=['#55CD97', '#21BEFF', '#96508E', '#F76629', '#DEAD24'], autopct=lambda p: f'{p*df[\"count\"].sum() / 100 :.0f}');\nplt.ylabel('');\nplt.title('Participants by level');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T12:11:05.401881Z","iopub.execute_input":"2021-06-12T12:11:05.402396Z","iopub.status.idle":"2021-06-12T12:11:05.524701Z","shell.execute_reply.started":"2021-06-12T12:11:05.402366Z","shell.execute_reply":"2021-06-12T12:11:05.523398Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"\nselect UserId as user_id, count(*) over (partition by TeamId) > 1 as is_in_team from df_team_memberships\n\"\"\").to_df()\ndf.is_in_team.map({True: 'In team', False: 'Solo'}).value_counts().plot.pie(autopct=lambda p: f'{p*len(df) / 100 :.0f} users');\nplt.title('Solo or in team')\nplt.ylabel('');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:26.058996Z","iopub.execute_input":"2021-06-12T11:31:26.059467Z","iopub.status.idle":"2021-06-12T11:31:26.179453Z","shell.execute_reply.started":"2021-06-12T11:31:26.059431Z","shell.execute_reply":"2021-06-12T11:31:26.178364Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"\nwith t1 as (select RequestDate as date, row_number() over (partition by TeamId order by RequestDate) as rn from df_team_memberships)\nselect date, sum(cast(rn > 1 as int)) as count from t1\ngroup by date\norder by date\n\"\"\").to_df()\nsns.barplot(data=df, x='date', y='count');\nplt.gca().set_xticklabels(labels=df.date.dt.strftime('%b %d'))\nplt.gca().xaxis.set_major_locator(mpl.dates.WeekdayLocator(interval=2));\nplt.xlabel('Date')\nplt.ylabel('Count')\nplt.title('Team merges per day');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:26.180677Z","iopub.execute_input":"2021-06-12T11:31:26.180926Z","iopub.status.idle":"2021-06-12T11:31:26.690443Z","shell.execute_reply.started":"2021-06-12T11:31:26.180901Z","shell.execute_reply":"2021-06-12T11:31:26.689854Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"select date_trunc('day', PostDate) as date, count(*) as count \nfrom df_forum_messages\ngroup by date \norder by date\"\"\").to_df()\nsns.barplot(data=df, x='date', y='count');\nplt.gca().set_xticklabels(labels=df.date.dt.strftime('%b %d'))\nplt.gca().xaxis.set_major_locator(mpl.dates.WeekdayLocator(interval=2))\nplt.title('Forum messages per day')\nplt.xlabel('Date')\nplt.ylabel('Count');","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:26.691327Z","iopub.execute_input":"2021-06-12T11:31:26.691615Z","iopub.status.idle":"2021-06-12T11:31:27.263770Z","shell.execute_reply.started":"2021-06-12T11:31:26.691592Z","shell.execute_reply":"2021-06-12T11:31:27.262801Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = duckdb.query(\"\"\"\nwith votes_received as (\nselect ToUserId as user_id, count(*) as count\nfrom df_forum_message_votes\ngroup by ToUserId\n), votes_given as (\nselect FromUserId as user_id, count(*) as count\nfrom df_forum_message_votes\ngroup by FromUserId\n),\nmessage_stats as (\nselect u.Id as user_id, u.DisplayName, count(*) as count, \nsum(cast(medal = 1 as int) ) as gold_count,\nsum(cast(medal = 2 as int) ) as silver_count,\nsum(cast(medal = 3 as int) ) as bronze_count\nfrom df_forum_messages m\njoin df_users u on m.PostUserId = u.Id\ngroup by u.Id, u.DisplayName\n)\nselect DisplayName as \"User\", cast(ms.count as int) as \"Messages Posted\", \n    cast(coalesce(vr.count, 0) as int) as \"Votes Received\", cast(coalesce(vg.count, 0) as int) as \"Votes Given\",\n    cast(coalesce(ms.gold_count, 0) as int) as \"Gold\",\n    cast(coalesce(ms.silver_count, 0) as int) as \"Silver\",\n    cast(coalesce(ms.bronze_count, 0) as int) as \"Bronze\"\nfrom message_stats ms\nleft join votes_received vr on vr.user_id = ms.user_id\nleft join votes_given vg on vg.user_id = ms.user_id\norder by \"Votes Received\" desc\n\"\"\").to_df()\ndf.head(50).style.bar().set_caption('Forum statistics').set_table_styles([{\n    'selector': 'caption',\n    'props': [\n        ('font-size', '20pt')\n    ]\n}])","metadata":{"_kg_hide-input":true,"execution":{"iopub.status.busy":"2021-06-12T11:31:27.264806Z","iopub.execute_input":"2021-06-12T11:31:27.265103Z","iopub.status.idle":"2021-06-12T11:31:27.343522Z","shell.execute_reply.started":"2021-06-12T11:31:27.265078Z","shell.execute_reply":"2021-06-12T11:31:27.342568Z"},"trusted":true},"execution_count":null,"outputs":[]}]}