{"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":"**This notebook is designed to improve your skills to search specific data inside a pandas DataFrame.**\n\nVery often in Data Science you need to search individual values inside a DataFrame that is not possible to do inside a join/merge function.\n\nThis notebook will help you.\n\n\n1. Searching inside column in pandas\n2. Searching inside index pandas\n3. Using numpy.where\n4. Using list(for index) and numpy(for data)\n5. Using list(for index) and list(for data)\n6. Using dictionary instead of lists and dataframes","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19"}},{"cell_type":"code","source":"#Load DataFrame\nimport pandas as pd\ndf=pd.read_csv(\"/kaggle/input/open-problems-single-cell-perturbations/adata_obs_meta.csv\")\ndf.head()","metadata":{"execution":{"iopub.status.busy":"2023-10-13T02:48:45.552925Z","iopub.execute_input":"2023-10-13T02:48:45.553490Z","iopub.status.idle":"2023-10-13T02:48:47.097984Z","shell.execute_reply.started":"2023-10-13T02:48:45.553455Z","shell.execute_reply":"2023-10-13T02:48:47.096875Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.tail()","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:03:43.524611Z","iopub.execute_input":"2023-10-13T03:03:43.525192Z","iopub.status.idle":"2023-10-13T03:03:43.543801Z","shell.execute_reply.started":"2023-10-13T03:03:43.525153Z","shell.execute_reply":"2023-10-13T03:03:43.542596Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We are going to search inside variable obs_id for the exactly same value with some different methods and see which one is the best.","metadata":{}},{"cell_type":"code","source":"%%timeit -n 100\n#First method - Filtering your dataset\n\ndf[df['obs_id']=='0002560bd38ce03e']","metadata":{"execution":{"iopub.status.busy":"2023-10-13T02:48:53.083158Z","iopub.execute_input":"2023-10-13T02:48:53.084215Z","iopub.status.idle":"2023-10-13T02:49:07.121230Z","shell.execute_reply.started":"2023-10-13T02:48:53.084160Z","shell.execute_reply":"2023-10-13T02:49:07.120074Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Not bad, but I think we can improve this.","metadata":{}},{"cell_type":"code","source":"#Set variable you need to search on index\ndf.set_index('obs_id',inplace=True)","metadata":{"execution":{"iopub.status.busy":"2023-10-13T02:49:07.123287Z","iopub.execute_input":"2023-10-13T02:49:07.124165Z","iopub.status.idle":"2023-10-13T02:49:07.130464Z","shell.execute_reply.started":"2023-10-13T02:49:07.124097Z","shell.execute_reply":"2023-10-13T02:49:07.129632Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**set_index(column)** -> changes the index of the dataframe (use .head() to see what it looks like)\n**inplace=True** means that you are reffering to the same dataframe (df) to output the value.\n\n*it's the same as df=df.set_index('obs_id')*","metadata":{}},{"cell_type":"code","source":"%%timeit -n 100\n#Second method - Searching using indexed dataframe\ndf.loc['0002560bd38ce03e']","metadata":{"execution":{"iopub.status.busy":"2023-10-13T02:49:07.131580Z","iopub.execute_input":"2023-10-13T02:49:07.132559Z","iopub.status.idle":"2023-10-13T02:49:07.240061Z","shell.execute_reply.started":"2023-10-13T02:49:07.132528Z","shell.execute_reply":"2023-10-13T02:49:07.238812Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"What a great improvement, let's ","metadata":{}},{"cell_type":"code","source":"import numpy as np\nindex=np.array(df.index)\nvalues=np.array(df.values)","metadata":{"execution":{"iopub.status.busy":"2023-10-13T02:52:18.646001Z","iopub.execute_input":"2023-10-13T02:52:18.646942Z","iopub.status.idle":"2023-10-13T02:52:18.764719Z","shell.execute_reply.started":"2023-10-13T02:52:18.646901Z","shell.execute_reply":"2023-10-13T02:52:18.763585Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%timeit\n#Third method - Using numpy where\nnp.where(index=='0002560bd38ce03e')","metadata":{"execution":{"iopub.status.busy":"2023-10-13T02:54:33.880597Z","iopub.execute_input":"2023-10-13T02:54:33.880962Z","iopub.status.idle":"2023-10-13T02:54:38.892321Z","shell.execute_reply.started":"2023-10-13T02:54:33.880933Z","shell.execute_reply":"2023-10-13T02:54:38.890911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"list_index=list(index)\nlist_values=values.tolist()","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:02:59.681623Z","iopub.execute_input":"2023-10-13T03:02:59.681950Z","iopub.status.idle":"2023-10-13T03:03:00.213126Z","shell.execute_reply.started":"2023-10-13T03:02:59.681924Z","shell.execute_reply":"2023-10-13T03:03:00.212334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"values[list_index.index('0002560bd38ce03e')]","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:02:09.023210Z","iopub.execute_input":"2023-10-13T03:02:09.023551Z","iopub.status.idle":"2023-10-13T03:02:09.030214Z","shell.execute_reply.started":"2023-10-13T03:02:09.023526Z","shell.execute_reply":"2023-10-13T03:02:09.029068Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%timeit\n#Fourth method - Index is inside list on python and the data is on numpy data\nvalues[list_index.index('0002560bd38ce03e')]","metadata":{"execution":{"iopub.status.busy":"2023-10-13T02:59:18.995566Z","iopub.execute_input":"2023-10-13T02:59:18.995891Z","iopub.status.idle":"2023-10-13T02:59:21.677095Z","shell.execute_reply.started":"2023-10-13T02:59:18.995865Z","shell.execute_reply":"2023-10-13T02:59:21.675725Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%timeit\n#Fifth method - Index is inside list on python and the data is on another list\nlist_values[list_index.index('0002560bd38ce03e')]","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:08:53.566979Z","iopub.execute_input":"2023-10-13T03:08:53.567339Z","iopub.status.idle":"2023-10-13T03:08:55.295010Z","shell.execute_reply.started":"2023-10-13T03:08:53.567312Z","shell.execute_reply":"2023-10-13T03:08:55.293896Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Is it the same value if we look for a random element in the list?","metadata":{}},{"cell_type":"code","source":"df.sample(1).index","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:05:07.743692Z","iopub.execute_input":"2023-10-13T03:05:07.744665Z","iopub.status.idle":"2023-10-13T03:05:07.757969Z","shell.execute_reply.started":"2023-10-13T03:05:07.744616Z","shell.execute_reply":"2023-10-13T03:05:07.756601Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%timeit\n#Fifth method - Index is inside list on python and the data is on another list\nlist_values[list_index.index('34dc16ad9c7050d7')]","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:08:55.970687Z","iopub.execute_input":"2023-10-13T03:08:55.971308Z","iopub.status.idle":"2023-10-13T03:09:05.162777Z","shell.execute_reply.started":"2023-10-13T03:08:55.971278Z","shell.execute_reply":"2023-10-13T03:09:05.161519Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let's review the second method with the same index","metadata":{}},{"cell_type":"code","source":"%%timeit -n 100\n#Second method - Searching using indexed dataframe\ndf.loc['34dc16ad9c7050d7']","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:10:14.343217Z","iopub.execute_input":"2023-10-13T03:10:14.344024Z","iopub.status.idle":"2023-10-13T03:10:14.405705Z","shell.execute_reply.started":"2023-10-13T03:10:14.343986Z","shell.execute_reply":"2023-10-13T03:10:14.404623Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"For the random sample the second method is beating the fifth method","metadata":{}},{"cell_type":"code","source":"dict_df=df.to_dict(orient='index')","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:12:33.000833Z","iopub.execute_input":"2023-10-13T03:12:33.001313Z","iopub.status.idle":"2023-10-13T03:12:35.291107Z","shell.execute_reply.started":"2023-10-13T03:12:33.001284Z","shell.execute_reply":"2023-10-13T03:12:35.290077Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%timeit\n#Sixth method - Using dictionary instead of lists and dataframes\ndict_df['34dc16ad9c7050d7']","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:12:45.622108Z","iopub.execute_input":"2023-10-13T03:12:45.622496Z","iopub.status.idle":"2023-10-13T03:12:50.489877Z","shell.execute_reply.started":"2023-10-13T03:12:45.622469Z","shell.execute_reply":"2023-10-13T03:12:50.488916Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%timeit\n#Comparing with get function of dictionaries does not improve\ndict_df.get('34dc16ad9c7050d7')","metadata":{"execution":{"iopub.status.busy":"2023-10-13T03:15:18.452529Z","iopub.execute_input":"2023-10-13T03:15:18.452857Z","iopub.status.idle":"2023-10-13T03:15:24.779313Z","shell.execute_reply.started":"2023-10-13T03:15:18.452831Z","shell.execute_reply":"2023-10-13T03:15:24.778110Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Conclusion**\n\nIf you convert your dataframe for a dictionary and use the index as key, you can get 1000x faster lookup/search on your dataset.\n\nThis is only useful if you have to search a lot of times to get the max optimization possible.","metadata":{}},{"cell_type":"markdown","source":"Below is all you need to use.","metadata":{}},{"cell_type":"code","source":"import pandas as pd\n\n#Load your dataset \ndf=pd.read_csv(\"/kaggle/input/open-problems-single-cell-perturbations/adata_obs_meta.csv\")\n\n#Use the column (obs_id) that you want to search\ndf.set_index('obs_id',inplace=True)\n\n#Create a dictionary of the dataframe\ndict_df=df.to_dict(orient='index')\n\n#Search\ndict_df['34dc16ad9c7050d7']","metadata":{},"execution_count":null,"outputs":[]}]}