text_html_output_1.png
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

train_original = pd.read_csv('../input/train.csv')
structures_original = pd.read_csv('../input/structures.csv')
test_original = pd.read_csv('../input/test.csv')

tmp_merge = pd.DataFrame.merge(train_original, structures_original, how='left', left_on=['molecule_name', 'atom_index_0'], right_on=['molecule_name', 'atom_index'])
tmp_merge = tmp_merge.merge(structures_original, how='left', left_on=['molecule_name', 'atom_index_1'], right_on=['molecule_name', 'atom_index'])
tmp_merge.drop(columns=['atom_index_x', 'atom_index_y'], inplace=True)
tmp_merge.columns = ['id', 'molecule_name', 'atom_0', 'atom_1', 'type', 'scalar_coupling_constant', 'atom_nm_0', 'x_0', 'y_0', 'z_0', 'atom_nm_1', 'x_1', 'y_1', 'z_1']
train = tmp_merge[['id', 'molecule_name', 'atom_0', 'atom_1', 'type', 'atom_nm_0', 'x_0', 'y_0', 'z_0', 'atom_nm_1', 'x_1', 'y_1', 'z_1', 'scalar_coupling_constant']]
train.sort_values(by=['id', 'molecule_name'], inplace=True)
train.reset_index(inplace=True, drop=True)
tmp_merge = None

tmp_merge = pd.DataFrame.merge(test_original, structures_original, how='inner', left_on=['molecule_name', 'atom_index_0'], right_on=['molecule_name', 'atom_index'])
tmp_merge = tmp_merge.merge(structures_original, how='inner', left_on=['molecule_name', 'atom_index_1'], right_on=['molecule_name', 'atom_index'])
tmp_merge.drop(columns=['atom_index_x', 'atom_index_y'], inplace=True)
tmp_merge.columns = ['id', 'molecule_name', 'atom_0', 'atom_1', 'type', 'atom_nm_0', 'x_0', 'y_0', 'z_0', 'atom_nm_1', 'x_1', 'y_1', 'z_1']
test = tmp_merge[['id', 'molecule_name', 'atom_0', 'atom_1', 'type', 'atom_nm_0', 'x_0', 'y_0', 'z_0', 'atom_nm_1', 'x_1', 'y_1', 'z_1']]
test.sort_values(by=['id', 'molecule_name'], inplace=True)
test.reset_index(inplace=True, drop=True)
tmp_merge = None
del tmp_merge

moleculeCount = train.groupby(['molecule_name'])['atom_0'].nunique()
hydrogen = pd.DataFrame()
hydrogen['molecule_name'] = moleculeCount.index
hydrogen['hydrogenCnt'] = moleculeCount.values
moleculeCount = train[train['atom_nm_1'] == 'C'].groupby(['molecule_name'])['atom_1'].nunique()
carbon = pd.DataFrame()
carbon['molecule_name'] = moleculeCount.index
carbon['carbonCnt'] = moleculeCount.values
moleculeCount = train[train['atom_nm_1'] == 'N'].groupby(['molecule_name'])['atom_1'].nunique()
nitrogen = pd.DataFrame()
nitrogen['molecule_name'] = moleculeCount.index
nitrogen['nitrogenCnt'] = moleculeCount.values
moleculeCount = None
del moleculeCount
train = pd.DataFrame.merge(train, hydrogen, how='left', on='molecule_name')
train = train.merge(carbon, how='left', on='molecule_name')
train = train.merge(nitrogen, how='left', on='molecule_name')
train.carbonCnt.fillna(0, inplace=True)
train.nitrogenCnt.fillna(0, inplace=True)

train.head()