{"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":"markdown","source":"# How to create a single CSV file that contains all past Kaggle Competition solution writeups.\n\n - Winners of Kaggle Competitions typically share their winning methods via solution \"writeups\". \n - This notebook demonstrates how to create a new dataset containing all past competition writeups.\n - To learn more about competition solution writeups, please refer to https://www.kaggle.com/discussions/product-feedback/373153.","metadata":{}},{"cell_type":"code","source":"# Import helpful Python packages\nimport pandas as pd\nimport datetime","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2023-07-15T17:53:38.747862Z","iopub.execute_input":"2023-07-15T17:53:38.748553Z","iopub.status.idle":"2023-07-15T17:53:38.763471Z","shell.execute_reply.started":"2023-07-15T17:53:38.748436Z","shell.execute_reply":"2023-07-15T17:53:38.761848Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Load the data\nTeams = pd.read_csv('/kaggle/input/meta-kaggle/Teams.csv',low_memory=False)\nCompetitions = pd.read_csv('/kaggle/input/meta-kaggle/Competitions.csv',low_memory=False)\nForumTopics = pd.read_csv('/kaggle/input/meta-kaggle/ForumTopics.csv',low_memory=False)\nForumMessages = pd.read_csv('/kaggle/input/meta-kaggle/ForumMessages.csv',low_memory=False)\n#for extracting tags\ncompetitionTags = pd.read_csv('/kaggle/input/meta-kaggle/CompetitionTags.csv',low_memory=False)\nTags = pd.read_csv('/kaggle/input/meta-kaggle/Tags.csv',low_memory=False)\n\n#Kaggle_writeup data\nkagg_writeups = pd.read_csv('/kaggle/input/2023-kaggle-ai-report/kaggle_writeups_20230510.csv')","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:53:38.766715Z","iopub.execute_input":"2023-07-15T17:53:38.767703Z","iopub.status.idle":"2023-07-15T17:54:27.230006Z","shell.execute_reply.started":"2023-07-15T17:53:38.767646Z","shell.execute_reply":"2023-07-15T17:54:27.228703Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Lets check kaggle writeups dataset","metadata":{}},{"cell_type":"code","source":"kagg_writeups.info()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.231731Z","iopub.execute_input":"2023-07-15T17:54:27.232097Z","iopub.status.idle":"2023-07-15T17:54:27.263140Z","shell.execute_reply.started":"2023-07-15T17:54:27.232061Z","shell.execute_reply":"2023-07-15T17:54:27.262308Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_writeups.describe()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.264701Z","iopub.execute_input":"2023-07-15T17:54:27.265347Z","iopub.status.idle":"2023-07-15T17:54:27.327541Z","shell.execute_reply.started":"2023-07-15T17:54:27.265309Z","shell.execute_reply":"2023-07-15T17:54:27.326383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_writeups.head()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.331679Z","iopub.execute_input":"2023-07-15T17:54:27.332150Z","iopub.status.idle":"2023-07-15T17:54:27.349147Z","shell.execute_reply.started":"2023-07-15T17:54:27.332106Z","shell.execute_reply":"2023-07-15T17:54:27.347891Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Filtering only Last two years competition data","metadata":{}},{"cell_type":"code","source":"kagg_writeups[\"Date of Writeup\"] = pd.to_datetime(kagg_writeups[\"Date of Writeup\"])","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.350933Z","iopub.execute_input":"2023-07-15T17:54:27.351285Z","iopub.status.idle":"2023-07-15T17:54:27.783201Z","shell.execute_reply.started":"2023-07-15T17:54:27.351253Z","shell.execute_reply":"2023-07-15T17:54:27.781934Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_2yrs_df = kagg_writeups[kagg_writeups['Date of Writeup'].dt.year >= 2021]\n","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.785376Z","iopub.execute_input":"2023-07-15T17:54:27.786177Z","iopub.status.idle":"2023-07-15T17:54:27.795907Z","shell.execute_reply.started":"2023-07-15T17:54:27.786108Z","shell.execute_reply":"2023-07-15T17:54:27.794460Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_2yrs_df.describe()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.797765Z","iopub.execute_input":"2023-07-15T17:54:27.798677Z","iopub.status.idle":"2023-07-15T17:54:27.839901Z","shell.execute_reply.started":"2023-07-15T17:54:27.798627Z","shell.execute_reply":"2023-07-15T17:54:27.838944Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_2yrs_df['Title of Competition'].nunique()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.841097Z","iopub.execute_input":"2023-07-15T17:54:27.842127Z","iopub.status.idle":"2023-07-15T17:54:27.850395Z","shell.execute_reply.started":"2023-07-15T17:54:27.842089Z","shell.execute_reply":"2023-07-15T17:54:27.848959Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Add competitionId to kagg_2yrs_df using competitions data","metadata":{}},{"cell_type":"code","source":"# Merging Competitions and CompetitionsTags columns\nCompetitions = Competitions.rename(columns={'Id': 'Id_Competitions',\n                                           'Title': 'Competition_Title',\n                                           'EnabledDate': 'Competition_Launch_Date'})\n\nkagg_2yrs_df = kagg_2yrs_df.merge(right=Competitions, how ='inner',left_on='Title of Competition',right_on='Competition_Title')\n","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.852217Z","iopub.execute_input":"2023-07-15T17:54:27.852621Z","iopub.status.idle":"2023-07-15T17:54:27.885574Z","shell.execute_reply.started":"2023-07-15T17:54:27.852586Z","shell.execute_reply":"2023-07-15T17:54:27.884230Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_2yrs_df = kagg_2yrs_df[['Competition Launch Date', 'Title of Competition', 'Competition URL',\n       'Date of Writeup', 'Title of Writeup', 'Writeup', 'Writeup URL','Id_Competitions']]","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.887103Z","iopub.execute_input":"2023-07-15T17:54:27.887534Z","iopub.status.idle":"2023-07-15T17:54:27.898399Z","shell.execute_reply.started":"2023-07-15T17:54:27.887497Z","shell.execute_reply":"2023-07-15T17:54:27.896894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_2yrs_df['Id_Competitions'].nunique()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.900550Z","iopub.execute_input":"2023-07-15T17:54:27.901009Z","iopub.status.idle":"2023-07-15T17:54:27.911612Z","shell.execute_reply.started":"2023-07-15T17:54:27.900973Z","shell.execute_reply":"2023-07-15T17:54:27.910506Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Lets see what are the extra columns we need for analysis\n\n* need medal and private leaderboard columns\n* Tags\n* subtitle(maybe)","metadata":{}},{"cell_type":"code","source":"# Merging Tags with competitions tags\n# Merging Competitions and CompetitionsTags columns\nTags = Tags.rename(columns={'Id': 'Id_Tag'})\n\ncompetitionTags = competitionTags.merge(right=Tags, how='inner', left_on='TagId', right_on='Id_Tag')","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.912787Z","iopub.execute_input":"2023-07-15T17:54:27.913152Z","iopub.status.idle":"2023-07-15T17:54:27.927799Z","shell.execute_reply.started":"2023-07-15T17:54:27.913120Z","shell.execute_reply":"2023-07-15T17:54:27.926747Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"competitionTags.tail()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.934665Z","iopub.execute_input":"2023-07-15T17:54:27.935555Z","iopub.status.idle":"2023-07-15T17:54:27.954979Z","shell.execute_reply.started":"2023-07-15T17:54:27.935499Z","shell.execute_reply":"2023-07-15T17:54:27.953972Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Lets see we got tags for all 81 unique competitions","metadata":{}},{"cell_type":"code","source":"# list of cometition Id in 2yrs data\ncomp_list = list(kagg_2yrs_df['Id_Competitions'].unique())","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.956096Z","iopub.execute_input":"2023-07-15T17:54:27.957020Z","iopub.status.idle":"2023-07-15T17:54:27.962515Z","shell.execute_reply.started":"2023-07-15T17:54:27.956982Z","shell.execute_reply":"2023-07-15T17:54:27.961234Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#checking if tags available for all 71 competions for 2yrs data\nreport_comp = competitionTags[competitionTags['CompetitionId'].isin(comp_list)]","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.964306Z","iopub.execute_input":"2023-07-15T17:54:27.965448Z","iopub.status.idle":"2023-07-15T17:54:27.979898Z","shell.execute_reply.started":"2023-07-15T17:54:27.965401Z","shell.execute_reply":"2023-07-15T17:54:27.978809Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"report_comp.tail()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:27.981266Z","iopub.execute_input":"2023-07-15T17:54:27.981956Z","iopub.status.idle":"2023-07-15T17:54:28.011172Z","shell.execute_reply.started":"2023-07-15T17:54:27.981920Z","shell.execute_reply":"2023-07-15T17:54:28.010088Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## there is only 78 competition id afer combining tags but total 71 competition for kagg report","metadata":{}},{"cell_type":"code","source":"report_comp['CompetitionId'].nunique()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.012753Z","iopub.execute_input":"2023-07-15T17:54:28.013388Z","iopub.status.idle":"2023-07-15T17:54:28.028182Z","shell.execute_reply.started":"2023-07-15T17:54:28.013351Z","shell.execute_reply":"2023-07-15T17:54:28.027111Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Find two missing comp ids","metadata":{}},{"cell_type":"code","source":"tag_available = list(report_comp['CompetitionId'].unique())\nnot_available = list(set(comp_list).difference(tag_available))\nprint(not_available)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.029721Z","iopub.execute_input":"2023-07-15T17:54:28.030392Z","iopub.status.idle":"2023-07-15T17:54:28.040617Z","shell.execute_reply.started":"2023-07-15T17:54:28.030355Z","shell.execute_reply":"2023-07-15T17:54:28.039580Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Tags is missing for competions are\n1. id-34419 Title-Kore 2022\n2. id-27783 Title-MLB Player Digital Engagement Forecasting\n3. id-24539 Title-Santa 2020 - The Candy Cane Contest","metadata":{}},{"cell_type":"code","source":"kagg_2yrs_df[kagg_2yrs_df['Id_Competitions'].isin(not_available)]['Title of Competition'].unique()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.042121Z","iopub.execute_input":"2023-07-15T17:54:28.042809Z","iopub.status.idle":"2023-07-15T17:54:28.059639Z","shell.execute_reply.started":"2023-07-15T17:54:28.042773Z","shell.execute_reply":"2023-07-15T17:54:28.058050Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Filering only topics needed for kaggle AI report","metadata":{}},{"cell_type":"code","source":"# selected tags\nTopics = [\n    'data type > text', \n    'data type > image', \n    'data type > tabular',\n    'data type > video data', \n    'technique > time series analysis',\n    'task > image-to-text'\n]\n\n# filter the competition with the selected tags\nSelectedTags = report_comp[\n    report_comp['FullPath'].isin(Topics)]\nSelectedTags.info(show_counts=True)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.061623Z","iopub.execute_input":"2023-07-15T17:54:28.062084Z","iopub.status.idle":"2023-07-15T17:54:28.083518Z","shell.execute_reply.started":"2023-07-15T17:54:28.062038Z","shell.execute_reply":"2023-07-15T17:54:28.082359Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"SelectedTags.head()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.084751Z","iopub.execute_input":"2023-07-15T17:54:28.085781Z","iopub.status.idle":"2023-07-15T17:54:28.110033Z","shell.execute_reply.started":"2023-07-15T17:54:28.085721Z","shell.execute_reply":"2023-07-15T17:54:28.108753Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"SelectedTags['CompetitionId'].nunique()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.111997Z","iopub.execute_input":"2023-07-15T17:54:28.112810Z","iopub.status.idle":"2023-07-15T17:54:28.127751Z","shell.execute_reply.started":"2023-07-15T17:54:28.112758Z","shell.execute_reply":"2023-07-15T17:54:28.126504Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"out of 71 competitions as we previously seen 3 tags not availabe for competitions and now, filtering the tags for 69 competitions from competitionTags data we get only 57 match as we given  ","metadata":{}},{"cell_type":"markdown","source":"## Extracting competition Id and Tags","metadata":{}},{"cell_type":"code","source":"SelectedTags['Name'].unique()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.129671Z","iopub.execute_input":"2023-07-15T17:54:28.130649Z","iopub.status.idle":"2023-07-15T17:54:28.142727Z","shell.execute_reply.started":"2023-07-15T17:54:28.130600Z","shell.execute_reply":"2023-07-15T17:54:28.141319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tag_analysis = SelectedTags.groupby('CompetitionId',as_index=False)['Name'].agg({\n    \n        'tags': lambda x: ', '.join(x)\n    })\n\ntag_analysis.head(20)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.144404Z","iopub.execute_input":"2023-07-15T17:54:28.145802Z","iopub.status.idle":"2023-07-15T17:54:28.168459Z","shell.execute_reply.started":"2023-07-15T17:54:28.145762Z","shell.execute_reply":"2023-07-15T17:54:28.167178Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"comp_tags = dict(zip(*tag_analysis.values.T))\n'''\ncomp_tags.update({24286: ['image,text'],\n                  30894: ['tabular, time series analysis'],\n                  40277: ['tabular,video']\n                 })\n'''\ncomp_tags","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.170300Z","iopub.execute_input":"2023-07-15T17:54:28.170712Z","iopub.status.idle":"2023-07-15T17:54:28.181986Z","shell.execute_reply.started":"2023-07-15T17:54:28.170677Z","shell.execute_reply":"2023-07-15T17:54:28.180807Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Extract the not matching topics","metadata":{}},{"cell_type":"code","source":"tag_match_comp_id =list(SelectedTags['CompetitionId'].unique())\n#see diffs\ntag_not_available = list(set(comp_list).difference(tag_match_comp_id))\nprint(len(tag_not_available))","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.183569Z","iopub.execute_input":"2023-07-15T17:54:28.183922Z","iopub.status.idle":"2023-07-15T17:54:28.196488Z","shell.execute_reply.started":"2023-07-15T17:54:28.183889Z","shell.execute_reply":"2023-07-15T17:54:28.195380Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Lets check what are the other tags provided instead","metadata":{}},{"cell_type":"code","source":"no_tag_match = report_comp[report_comp['CompetitionId'].isin(tag_not_available)]","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.197563Z","iopub.execute_input":"2023-07-15T17:54:28.198673Z","iopub.status.idle":"2023-07-15T17:54:28.210418Z","shell.execute_reply.started":"2023-07-15T17:54:28.198636Z","shell.execute_reply":"2023-07-15T17:54:28.209174Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"no_tag_match.head()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.212104Z","iopub.execute_input":"2023-07-15T17:54:28.212846Z","iopub.status.idle":"2023-07-15T17:54:28.238033Z","shell.execute_reply.started":"2023-07-15T17:54:28.212808Z","shell.execute_reply":"2023-07-15T17:54:28.236742Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"no_tag_match['CompetitionId'].nunique()\n","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.239717Z","iopub.execute_input":"2023-07-15T17:54:28.240402Z","iopub.status.idle":"2023-07-15T17:54:28.248238Z","shell.execute_reply.started":"2023-07-15T17:54:28.240366Z","shell.execute_reply":"2023-07-15T17:54:28.247004Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## We need to manually asssign tags for the 21 competitions now","metadata":{}},{"cell_type":"markdown","source":"## lets extract those 22 tagg not availble competitons from kagg writeups","metadata":{}},{"cell_type":"code","source":"No_tag_df = kagg_2yrs_df[kagg_2yrs_df['Id_Competitions'].isin(tag_not_available)]","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.249757Z","iopub.execute_input":"2023-07-15T17:54:28.250122Z","iopub.status.idle":"2023-07-15T17:54:28.263028Z","shell.execute_reply.started":"2023-07-15T17:54:28.250088Z","shell.execute_reply":"2023-07-15T17:54:28.261829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"No_tag_df.info()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.264562Z","iopub.execute_input":"2023-07-15T17:54:28.265426Z","iopub.status.idle":"2023-07-15T17:54:28.288061Z","shell.execute_reply.started":"2023-07-15T17:54:28.265387Z","shell.execute_reply":"2023-07-15T17:54:28.286759Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pd.unique(No_tag_df[['Title of Competition','Competition URL','Id_Competitions']].values.ravel('K'))","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.289587Z","iopub.execute_input":"2023-07-15T17:54:28.290258Z","iopub.status.idle":"2023-07-15T17:54:28.299937Z","shell.execute_reply.started":"2023-07-15T17:54:28.290219Z","shell.execute_reply":"2023-07-15T17:54:28.298657Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"manual_tag_dict = {\n    21669:'audio',\n    24539:'other,reinforcement learning,Simulation',\n    22559:'tabular,image' ,\n    25954:'audio',\n    23652:'tabular,image',\n    27783:'tabular',\n    23249:'image',\n    30198:'video',\n    30067:'other,reinforcement learning,Simulation',\n    31644:'tabular',\n    31254:'recommendation system',\n    33246:'audio',\n    34542:'tabular',\n    34538:'tabular',\n    34419:'other,reinforcement learning,Simulation',\n    37381:'AI ethics,AI security,puzzle',\n    38321:'text',\n    37190:'tabular',\n    37077:'tabular,image',\n    38760:'recommendation system',\n    39272:'image',\n    41879:'image',\n    46801:'tabular',\n    46105:'video'\n}","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:35:59.704829Z","iopub.execute_input":"2023-07-15T18:35:59.705312Z","iopub.status.idle":"2023-07-15T18:35:59.714740Z","shell.execute_reply.started":"2023-07-15T18:35:59.705271Z","shell.execute_reply":"2023-07-15T18:35:59.713112Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Combining Two Dicts having tags for all competitions","metadata":{}},{"cell_type":"code","source":"Tags_comp_dict = {**comp_tags,**manual_tag_dict}\nprint(f'Dictionary of Tags for all {len(Tags_comp_dict)} competitions')\nTags_comp_dict","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:36:03.211473Z","iopub.execute_input":"2023-07-15T18:36:03.211909Z","iopub.status.idle":"2023-07-15T18:36:03.226204Z","shell.execute_reply.started":"2023-07-15T18:36:03.211874Z","shell.execute_reply":"2023-07-15T18:36:03.224866Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Now combining the Tags with kaggle writeups","metadata":{}},{"cell_type":"code","source":"kagg_2yrs_df['Competition_topic'] = kagg_2yrs_df['Id_Competitions'].map(Tags_comp_dict)\nkagg_2yrs_df.head()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:36:05.083626Z","iopub.execute_input":"2023-07-15T18:36:05.084135Z","iopub.status.idle":"2023-07-15T18:36:05.108827Z","shell.execute_reply.started":"2023-07-15T18:36:05.084090Z","shell.execute_reply":"2023-07-15T18:36:05.107364Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"kagg_2yrs_df.info()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:36:06.155618Z","iopub.execute_input":"2023-07-15T18:36:06.156138Z","iopub.status.idle":"2023-07-15T18:36:06.176871Z","shell.execute_reply.started":"2023-07-15T18:36:06.156092Z","shell.execute_reply":"2023-07-15T18:36:06.175437Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Lets check whcih Topics has most competitions","metadata":{}},{"cell_type":"code","source":"kagg_2yrs_df.groupby(['Competition_topic']).Id_Competitions.nunique().sort_values(ascending=False)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:36:08.443639Z","iopub.execute_input":"2023-07-15T18:36:08.444152Z","iopub.status.idle":"2023-07-15T18:36:08.457549Z","shell.execute_reply.started":"2023-07-15T18:36:08.444108Z","shell.execute_reply":"2023-07-15T18:36:08.456226Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Save dataframe","metadata":{}},{"cell_type":"code","source":"TSA_topics =['time series analysis','tabular, time series analysis']","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:39:06.541129Z","iopub.execute_input":"2023-07-15T18:39:06.541680Z","iopub.status.idle":"2023-07-15T18:39:06.548558Z","shell.execute_reply.started":"2023-07-15T18:39:06.541635Z","shell.execute_reply":"2023-07-15T18:39:06.547143Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"TSA_competitions = kagg_2yrs_df[kagg_2yrs_df['Competition_topic'].isin(TSA_topics)]\nTSA_competitions","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:39:15.784935Z","iopub.execute_input":"2023-07-15T18:39:15.786478Z","iopub.status.idle":"2023-07-15T18:39:15.809350Z","shell.execute_reply.started":"2023-07-15T18:39:15.786415Z","shell.execute_reply":"2023-07-15T18:39:15.807931Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"TSA_competitions['Title of Competition'].unique()","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:39:27.546088Z","iopub.execute_input":"2023-07-15T18:39:27.546757Z","iopub.status.idle":"2023-07-15T18:39:27.556068Z","shell.execute_reply.started":"2023-07-15T18:39:27.546708Z","shell.execute_reply":"2023-07-15T18:39:27.554680Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Save all writeups to a single CSV file\nkagg_2yrs_df.to_csv('/kaggle/working/'+'kaggle_ai_report_data'+'.csv',index=False)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T17:54:28.469238Z","iopub.execute_input":"2023-07-15T17:54:28.469673Z","iopub.status.idle":"2023-07-15T17:54:28.794103Z","shell.execute_reply.started":"2023-07-15T17:54:28.469629Z","shell.execute_reply":"2023-07-15T17:54:28.793025Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Adding extra column for analysis","metadata":{}},{"cell_type":"code","source":"ForumMessages.head(2)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:40:47.010928Z","iopub.execute_input":"2023-07-15T18:40:47.011932Z","iopub.status.idle":"2023-07-15T18:40:47.030509Z","shell.execute_reply.started":"2023-07-15T18:40:47.011885Z","shell.execute_reply":"2023-07-15T18:40:47.028872Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Delete columns from forummessages\nForumMessages.drop(columns=['Medal', 'MedalAwardDate'],axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:41:06.709356Z","iopub.execute_input":"2023-07-15T18:41:06.709858Z","iopub.status.idle":"2023-07-15T18:41:07.265978Z","shell.execute_reply.started":"2023-07-15T18:41:06.709820Z","shell.execute_reply":"2023-07-15T18:41:07.264548Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Delete columns from Tags for duplicates\nTags.drop(columns=['ParentTagId'],axis=1, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:41:32.870513Z","iopub.execute_input":"2023-07-15T18:41:32.871006Z","iopub.status.idle":"2023-07-15T18:41:32.879479Z","shell.execute_reply.started":"2023-07-15T18:41:32.870967Z","shell.execute_reply":"2023-07-15T18:41:32.878154Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Rename a few columns\nTeams = Teams.rename(columns={'Id': 'Id_Teams'})\nForumTopics = ForumTopics.rename(columns={'Id': 'Id_ForumTopics',\n                                         'Title': 'Title of Writeup',\n                                         'CreationDate': 'Date of Writeup'})\nForumMessages = ForumMessages.rename(columns={'Id': 'Id_ForumMessages',\n                                              'Message': 'Writeup'})\n#Competitions = Competitions.rename(columns={'Id': 'Id_Competitions',\n #kagg_2yrs_df                                          #'Title': 'Title of Competition',\n                                           #'EnabledDate': 'Competition Launch Date'})\n#for extracting Tags\ncompetitionTags = competitionTags.rename(columns={'TagId': 'CompetitionTagId','CompetitionId':'CompId'})\nprint(Tags.head())\nTags = Tags.rename(columns={'Id_Tag' : 'TagId'})\nprint(Tags.head())\n\ncomp_tagdf = competitionTags.merge(right=Tags, how='inner', left_on='CompetitionTagId', right_on='TagId')\nCompetitions = Competitions.merge(right=comp_tagdf,how='inner',left_on='Id_Competitions',right_on='CompId')\n# Organize everything\n\ndf = Teams.merge(right=ForumTopics, how='inner', left_on='WriteUpForumTopicId', right_on='Id_ForumTopics')\ndf = df.merge(right=ForumMessages, how='inner', left_on='Id_ForumTopics', right_on='ForumTopicId')\ndf = df.merge(right=Competitions, how='inner', left_on='ForumId', right_on='ForumId')\ndf = df.merge(right=kagg_2yrs_df, how='inner', left_on='CompetitionId', right_on='Id_Competitions')\ndf = df[df['FirstForumMessageId'] == df['Id_ForumMessages']]\ndf = df[df['HostSegmentTitle'].isin(['Featured','Research'])]\n\n# Add in URLs\n#df['Id_Competitions'] = df['Id_Competitions'].astype(str)\n#df['Competition URL'] = 'https://www.kaggle.com/c/'+df['Id_Competitions']\n#df['Id_ForumTopics'] = df['Id_ForumTopics'].astype(str)\n#df['Writeup URL'] = 'https://www.kaggle.com/c/'+df['Id_Competitions']+'/discussion/'+df['Id_ForumTopics']","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:56:16.305565Z","iopub.execute_input":"2023-07-15T18:56:16.306065Z","iopub.status.idle":"2023-07-15T18:57:04.946106Z","shell.execute_reply.started":"2023-07-15T18:56:16.306027Z","shell.execute_reply":"2023-07-15T18:57:04.939284Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.head(2)","metadata":{"execution":{"iopub.status.busy":"2023-07-15T18:57:50.905524Z","iopub.execute_input":"2023-07-15T18:57:50.906020Z","iopub.status.idle":"2023-07-15T18:57:50.945259Z","shell.execute_reply.started":"2023-07-15T18:57:50.905982Z","shell.execute_reply":"2023-07-15T18:57:50.943739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Final cleanup\ndf = df[['Competition Launch Date',\n         'Title of Competition',\n         'Competition URL',\n         'Date of Writeup_x',\n         'Title of Writeup_x',\n         'Writeup_x',\n         'Writeup URL',\n         'PrivateLeaderboardRank',\n         'Medal',\n         'Subtitle','TagId','FullPath_x']]\n\nprint('# of entries: ',df['Writeup_x'].count())","metadata":{"execution":{"iopub.status.busy":"2023-07-15T19:00:24.269289Z","iopub.execute_input":"2023-07-15T19:00:24.269831Z","iopub.status.idle":"2023-07-15T19:00:24.428337Z","shell.execute_reply.started":"2023-07-15T19:00:24.269792Z","shell.execute_reply":"2023-07-15T19:00:24.426534Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}