{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"1-load_data.ipynb","provenance":[],"collapsed_sections":[],"authorship_tag":"ABX9TyOhSr84BF4QcOYC5nJ5cfbV"},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"markdown","source":["pip install\n"],"metadata":{"id":"hOOHI3XtmEVk"}},{"cell_type":"code","source":["# !pip uninstall pandas-profiling\n","# !pip install pandas-profiling[notebook] -U\n","# !pip install pandas-profiling -U\n","# !pip install pandas-profiling==3.1.0\n","# !pip install https://github.com/pandas-profiling/pandas-profiling/archive/master.zip \n"],"metadata":{"id":"2MIKAG9xmIIC","executionInfo":{"status":"ok","timestamp":1652337579040,"user_tz":-330,"elapsed":10,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":910,"outputs":[]},{"cell_type":"markdown","source":["Import libraries"],"metadata":{"id":"n9m9whhyjmeg"}},{"cell_type":"code","source":["# import os\n","# import pandas as pd\n","# from pandas_profiling import ProfileReport"],"metadata":{"id":"cdTCjxPDjnYF","executionInfo":{"status":"ok","timestamp":1652337580083,"user_tz":-330,"elapsed":1051,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":911,"outputs":[]},{"cell_type":"markdown","source":["## Load data\n","\n","Using Pandas, we read in the dataset into a DataFrame. \n","\n","We also print the shape of the DataFrame, which indicates the number of rows and columns in this dataset."],"metadata":{"id":"x2fdaFBKj7En"}},{"cell_type":"code","execution_count":912,"metadata":{"id":"2S7RAkBRjljH","executionInfo":{"status":"ok","timestamp":1652337581008,"user_tz":-330,"elapsed":933,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"outputs":[],"source":["# from google.colab import drive # for working in google colab\n","# drive.mount('/content/drive')"]},{"cell_type":"code","source":["PATH = os.getcwd()\n","data_path = os.path.join(PATH, '/content/drive/MyDrive/jupyter/2D/data/C2DB_full.csv')\n","\n","df = pd.read_csv(data_path)\n","print(f'Original DataFrame shape: {df.shape}')"],"metadata":{"id":"WX8DZgt9kBWX","colab":{"base_uri":"https://localhost:8080/"},"executionInfo":{"status":"ok","timestamp":1652337581010,"user_tz":-330,"elapsed":20,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}},"outputId":"ba23fcf5-a9ea-4419-e6fa-86b6447ea586"},"execution_count":913,"outputs":[{"output_type":"stream","name":"stdout","text":["Original DataFrame shape: (4034, 99)\n"]}]},{"cell_type":"code","source":["%store\n","df.columns"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"DXQyS9p4dbk6","executionInfo":{"status":"ok","timestamp":1652337581010,"user_tz":-330,"elapsed":17,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}},"outputId":"ef1b938b-1cb7-4743-b8a4-7f339559b981"},"execution_count":914,"outputs":[{"output_type":"stream","name":"stdout","text":["Stored variables and their in-db values:\n"]},{"output_type":"execute_result","data":{"text/plain":["Index(['Formula', 'Band gap', '2D plasma frequency (x)',\n","       '2D plasma frequency (y)', 'Band gap (G₀W₀)', 'Band gap (HSE06)',\n","       'Conduction band effective mass, direction 1',\n","       'Conduction band effective mass, direction 2', 'Cond. band minimum',\n","       'Conduction band minimum (G₀W₀)', 'Conduction band minimum (HSE06)',\n","       'Dir. band gap', 'Direct band gap (G₀W₀)', 'Direct band gap (HSE06)',\n","       'Dir. gap wo. soc.', 'Exc. bind. energy', 'Gap wo. soc.',\n","       'Phonon dynamic stability (low/high)', 'Thermodynamic stability level',\n","       'Energy', 'Vacuum level', 'Valence band effective mass, direction 1',\n","       'Valence band effective mass, direction 2', 'Val. band maximum',\n","       'Valence band maximum (G₀W₀)', 'Valence band maximum (HSE06)',\n","       'Work function (avg. if finite dipole)', 'First class material',\n","       'Calculator', 'Anisotropic exchange (out-of-plane)',\n","       'Area of unit-cell', 'Topology', 'Crystal type', 'DOS at ef',\n","       'DOS at ef no soc.', 'Energy above convex hull', 'Fermi level',\n","       'Heat of formation',\n","       'Magnetic anisotropy (E<sub>z</sub> - E<sub>x</sub>)',\n","       'Magnetic anisotropy (E<sub>z</sub> - E<sub>y</sub>)',\n","       'Magnetic easy axis', 'Magnetic moment', 'Magnetic state',\n","       'Material class', 'Material has inversion symmetry', 'Magnetic',\n","       'Material unique ID', 'Maximum force', 'Maximum stress',\n","       'Maximum value of S_z at magnetic sites',\n","       'Minimum eigenvalue of Hessian', 'Monolayer reported DOI',\n","       'Nearest neighbor exchange coupling', 'Charge', 'Number of atoms',\n","       'Number of nearest neighbors', 'n-spins',\n","       'Out-of-plane dipole along +z axis', 'Path to collection folder', 'PBC',\n","       'Point group', 'Unique ID', 'Related COD id', 'Related ICSD id',\n","       'Single-ion anisotropy (out-of-plane)',\n","       'Soc. total energy, x-direction', 'Soc. total energy, y-direction',\n","       'Soc. total energy, z-direction', 'Space group', 'Space group number',\n","       'Speed of sound (x)', 'Speed of sound (y)',\n","       'Static interband polarizability (x)',\n","       'Static interband polarizability (y)',\n","       'Static interband polarizability (z)',\n","       'Static lattice polarizability (x)',\n","       'Static lattice polarizability (y)',\n","       'Static lattice polarizability (z)', 'Static total polarizability (x)',\n","       'Static total polarizability (y)', 'Static total polarizability (z)',\n","       'Stiffness dynamic stability (low/high)',\n","       'Stiffness tensor, 11-component', 'Stiffness tensor, 12-component',\n","       'Stiffness tensor, 13-component', 'Stiffness tensor, 21-component',\n","       'Stiffness tensor, 22-component', 'Stiffness tensor, 23-component',\n","       'Stiffness tensor, 31-component', 'Stiffness tensor, 32-component',\n","       'Stiffness tensor, 33-component', 'Stoichiometry', 'Mass', 'Age', 'ID',\n","       'Unique identifier', 'Username', 'Vacuum level difference', 'Volume'],\n","      dtype='object')"]},"metadata":{},"execution_count":914}]},{"cell_type":"code","source":["target = 'Band gap'\n","\n","out_path = '/content/drive/MyDrive/jupyter/2D/data/C2DB_gap.csv'"],"metadata":{"id":"4wxj9gB5tsQm","executionInfo":{"status":"ok","timestamp":1652337581011,"user_tz":-330,"elapsed":15,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":915,"outputs":[]},{"cell_type":"code","source":["df = df[['Formula',target]].copy()"],"metadata":{"id":"5390TtK3kkne","executionInfo":{"status":"ok","timestamp":1652337581011,"user_tz":-330,"elapsed":15,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":916,"outputs":[]},{"cell_type":"markdown","source":["Change str to float and set others to nan [more info](https://stackoverflow.com/questions/15891038/change-column-type-in-pandas)"],"metadata":{"id":"VaxxwgIyg0c4"}},{"cell_type":"code","source":["# profile = ProfileReport(df, title=\"Pandas Profiling Report\")\n","# profile.to_notebook_iframe()\n"],"metadata":{"id":"y0Op-qEll8Yz","executionInfo":{"status":"ok","timestamp":1652337581011,"user_tz":-330,"elapsed":14,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":917,"outputs":[]},{"cell_type":"code","source":["df.dtypes"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"fiaTnVqGRnLN","executionInfo":{"status":"ok","timestamp":1652337581012,"user_tz":-330,"elapsed":15,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}},"outputId":"81e7414c-85d0-4965-b095-0879d5f709c6"},"execution_count":918,"outputs":[{"output_type":"execute_result","data":{"text/plain":["Formula     object\n","Band gap    object\n","dtype: object"]},"metadata":{},"execution_count":918}]},{"cell_type":"code","source":["df[target] = pd.to_numeric(df[target],errors='coerce')\n"],"metadata":{"id":"QOJKXT2Mf0t2","executionInfo":{"status":"ok","timestamp":1652337581675,"user_tz":-330,"elapsed":674,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":919,"outputs":[]},{"cell_type":"code","source":["# df.iloc[275]['Band gap']"],"metadata":{"id":"dodFdhtTTg5B","executionInfo":{"status":"ok","timestamp":1652337581675,"user_tz":-330,"elapsed":11,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":920,"outputs":[]},{"cell_type":"code","source":["df = df.dropna()\n","df.shape"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"3rqUve6EhHCl","executionInfo":{"status":"ok","timestamp":1652337581677,"user_tz":-330,"elapsed":13,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}},"outputId":"b9aa0ada-9c4b-49e5-b490-516804b6a1b8"},"execution_count":921,"outputs":[{"output_type":"execute_result","data":{"text/plain":["(4032, 2)"]},"metadata":{},"execution_count":921}]},{"cell_type":"code","source":["# df.describe()"],"metadata":{"id":"_4Af3PEMlSc4","executionInfo":{"status":"ok","timestamp":1652337581677,"user_tz":-330,"elapsed":11,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":922,"outputs":[]},{"cell_type":"markdown","source":["Remove duplicates by taking avarage "],"metadata":{"id":"o1kebHJNtCBt"}},{"cell_type":"code","source":["df = df.groupby('Formula').mean().reset_index()\n","# df.groupby('Formula', as_index=False).mean()\n","df.shape"],"metadata":{"id":"bTpCYOJJnCNr","executionInfo":{"status":"ok","timestamp":1652337581677,"user_tz":-330,"elapsed":11,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}},"colab":{"base_uri":"https://localhost:8080/"},"outputId":"b4303d3c-cb10-479f-f442-be3ea9b882c4"},"execution_count":923,"outputs":[{"output_type":"execute_result","data":{"text/plain":["(2812, 2)"]},"metadata":{},"execution_count":923}]},{"cell_type":"code","source":["df = df[df[target] != 0]\n","df.shape"],"metadata":{"colab":{"base_uri":"https://localhost:8080/"},"id":"efKKBLa1mNrQ","executionInfo":{"status":"ok","timestamp":1652337581677,"user_tz":-330,"elapsed":8,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}},"outputId":"b148c04b-abf8-4c22-c874-d030dfb74795"},"execution_count":924,"outputs":[{"output_type":"execute_result","data":{"text/plain":["(1214, 2)"]},"metadata":{},"execution_count":924}]},{"cell_type":"code","source":["# profile = ProfileReport(df, title=\"Pandas Profiling Report\")\n","# profile.to_notebook_iframe()\n"],"metadata":{"id":"5lL1GigQr8sP","executionInfo":{"status":"ok","timestamp":1652337581679,"user_tz":-330,"elapsed":9,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":925,"outputs":[]},{"cell_type":"markdown","source":["## Save cleaned data to csv\n","\n","Finally, after cleaning and processing the data, you can save it to disk in a cleaned state for you to use later.\n","\n","Pandas allows us to save our data as a comma separated value `.csv` file. \n"],"metadata":{"id":"zZeSgztK6liQ"}},{"cell_type":"code","source":["df.to_csv(out_path, index=False)"],"metadata":{"id":"PldpWajQ6m6O","executionInfo":{"status":"ok","timestamp":1652337581679,"user_tz":-330,"elapsed":8,"user":{"displayName":"Athul Krishna","userId":"17952052837509004747"}}},"execution_count":926,"outputs":[]}]}