{"cells":[{"metadata":{},"cell_type":"markdown","source":"**Merging data**\n\nAim of this this kernel to merge all available data into single dataframe.\nData from all files are merged to single file for train and test.\nFinal **train.csv** and **test.csv** are available under output.\nThese files can be directly used further feature engineering.\n\n>  **File_name-------------------------------Data_frequency-------------------Data_coverage**\n> 1. potential_energy.csv----------------------per molecule---------------------Only train data\n> 1. dipole_moments.csv------------------------per modecule---------------------Only train data\n> 1. mulliken_charges.csv----------------------per atom-------------------------Only train data\n> 1. magnetic_shielding_tensors.csv------------per atom-------------------------Only train data\n> 1. structures.csv----------------------------per atom-------------------------both train + test data\n> 1. scalar_coupling_contributions.csv---------per atom pair--------------------Only train data\n> 1. train.csv---------------------------------per atom pair--------------------\n> 1. test.csv----------------------------------per atom pair--------------------"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = pd.read_csv('../input/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"scalar_coupling_contributions = pd.read_csv('../input/scalar_coupling_contributions.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = pd.concat([train_dataset, scalar_coupling_contributions[['fc', 'sd', 'pso', 'dso']]], axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"structures = pd.read_csv('../input/structures.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset_0 = train_dataset.merge(structures, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_1'])\ntrain_dataset = train_dataset_0.merge(structures, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_0'], \n                                     suffixes=('_atom_1', '_atom_0'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset.drop(['atom_index_atom_0',  'atom_index_atom_1'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_arranged_columns = ['id', 'molecule_name', 'type',\n           'atom_index_0', 'atom_atom_0', 'x_atom_0', 'y_atom_0', 'z_atom_0', \n           'atom_index_1', 'atom_atom_1', 'x_atom_1', 'y_atom_1', 'z_atom_1',\n           'fc', 'sd', 'pso', 'dso', 'scalar_coupling_constant']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = train_dataset[train_arranged_columns]\ntrain_dataset.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_dataset = pd.read_csv('../input/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_dataset_0 = test_dataset.merge(structures, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_1'])\ntest_dataset = test_dataset_0.merge(structures, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_0'], \n                                     suffixes=('_atom_1', '_atom_0'))\n\ntest_dataset.drop(['atom_index_atom_0',  'atom_index_atom_1'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_arranged_columns = ['id', 'molecule_name', 'type',\n           'atom_index_0', 'atom_atom_0', 'x_atom_0', 'y_atom_0', 'z_atom_0', \n           'atom_index_1', 'atom_atom_1', 'x_atom_1', 'y_atom_1', 'z_atom_1']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_dataset = test_dataset[test_arranged_columns]\ntest_dataset.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"magnetic_shielding_tensors = pd.read_csv('../input/magnetic_shielding_tensors.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset_0 = train_dataset.merge(magnetic_shielding_tensors, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_1'])\ntrain_dataset = train_dataset_0.merge(magnetic_shielding_tensors, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_0'], \n                                     suffixes=('_atom_1', '_atom_0'))\ntrain_dataset.drop(['atom_index_atom_0',  'atom_index_atom_1'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_arranged_columns = ['id', 'molecule_name', 'type', \n                          'atom_index_0', 'atom_atom_0', 'x_atom_0', 'y_atom_0', 'z_atom_0',  \n                          'XX_atom_0', 'YX_atom_0', 'ZX_atom_0', 'XY_atom_0', 'YY_atom_0', 'ZY_atom_0', 'XZ_atom_0', 'YZ_atom_0', 'ZZ_atom_0',\n                          'atom_index_1', 'atom_atom_1', 'x_atom_1', 'y_atom_1', 'z_atom_1', \n                          'XX_atom_1', 'YX_atom_1', 'ZX_atom_1', 'XY_atom_1', 'YY_atom_1', 'ZY_atom_1', 'XZ_atom_1', 'YZ_atom_1', 'ZZ_atom_1',\n                          'fc', 'sd', 'pso', 'dso', 'scalar_coupling_constant']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = train_dataset[train_arranged_columns]\ntrain_dataset.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mulliken_charges = pd.read_csv('../input/mulliken_charges.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset_0 = train_dataset.merge(mulliken_charges, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_1'])\ntrain_dataset = train_dataset_0.merge(mulliken_charges, \n                                    right_on=['molecule_name', 'atom_index'], \n                                    left_on=['molecule_name', 'atom_index_0'], \n                                     suffixes=('_atom_1', '_atom_0'))\ntrain_dataset.drop(['atom_index_atom_0',  'atom_index_atom_1'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_arranged_columns = ['id', 'molecule_name', 'type', \n                          'atom_index_0', 'atom_atom_0', 'x_atom_0', 'y_atom_0', 'z_atom_0',  \n                          'XX_atom_0', 'YX_atom_0', 'ZX_atom_0', 'XY_atom_0', 'YY_atom_0', 'ZY_atom_0', 'XZ_atom_0', 'YZ_atom_0', 'ZZ_atom_0',\n                          'mulliken_charge_atom_0',\n                          'atom_index_1', 'atom_atom_1', 'x_atom_1', 'y_atom_1', 'z_atom_1', \n                          'XX_atom_1', 'YX_atom_1', 'ZX_atom_1', 'XY_atom_1', 'YY_atom_1', 'ZY_atom_1', 'XZ_atom_1', 'YZ_atom_1', 'ZZ_atom_1',\n                          'mulliken_charge_atom_1',\n                          'fc', 'sd', 'pso', 'dso', 'scalar_coupling_constant']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = train_dataset[train_arranged_columns]\ntrain_dataset.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dipole_moments = pd.read_csv('../input/dipole_moments.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = train_dataset.merge(dipole_moments, \n                                    right_on='molecule_name', \n                                    left_on='molecule_name')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_arranged_columns = ['id', 'molecule_name', 'type', 'X', 'Y', 'Z',\n                          'atom_index_0', 'atom_atom_0', 'x_atom_0', 'y_atom_0', 'z_atom_0',  \n                          'XX_atom_0', 'YX_atom_0', 'ZX_atom_0', 'XY_atom_0', 'YY_atom_0', 'ZY_atom_0', 'XZ_atom_0', 'YZ_atom_0', 'ZZ_atom_0',\n                          'mulliken_charge_atom_0',\n                          'atom_index_1', 'atom_atom_1', 'x_atom_1', 'y_atom_1', 'z_atom_1', \n                          'XX_atom_1', 'YX_atom_1', 'ZX_atom_1', 'XY_atom_1', 'YY_atom_1', 'ZY_atom_1', 'XZ_atom_1', 'YZ_atom_1', 'ZZ_atom_1',\n                          'mulliken_charge_atom_1',\n                          'fc', 'sd', 'pso', 'dso', 'scalar_coupling_constant']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = train_dataset[train_arranged_columns]\ntrain_dataset.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"potential_energy = pd.read_csv('../input/potential_energy.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = train_dataset.merge(potential_energy, \n                                    right_on='molecule_name', \n                                    left_on='molecule_name')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_arranged_columns = ['id', 'molecule_name', 'type', 'X', 'Y', 'Z', 'potential_energy',\n                          'atom_index_0', 'atom_atom_0', 'x_atom_0', 'y_atom_0', 'z_atom_0',  \n                          'XX_atom_0', 'YX_atom_0', 'ZX_atom_0', 'XY_atom_0', 'YY_atom_0', 'ZY_atom_0', 'XZ_atom_0', 'YZ_atom_0', 'ZZ_atom_0',\n                          'mulliken_charge_atom_0',\n                          'atom_index_1', 'atom_atom_1', 'x_atom_1', 'y_atom_1', 'z_atom_1', \n                          'XX_atom_1', 'YX_atom_1', 'ZX_atom_1', 'XY_atom_1', 'YY_atom_1', 'ZY_atom_1', 'XZ_atom_1', 'YZ_atom_1', 'ZZ_atom_1',\n                          'mulliken_charge_atom_1',\n                          'fc', 'sd', 'pso', 'dso', 'scalar_coupling_constant']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset = train_dataset[train_arranged_columns]\ntrain_dataset.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dataset.to_csv('train.csv', index=False)\ntest_dataset.to_csv('test.csv', index=False)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.4","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}