text_html_output_1.png
import pandas as pd

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

train = pd.read_csv('../input/train.csv')
test = pd.read_csv('../input/test.csv')
sub = pd.read_csv('../input/sample_submission.csv')
structures = pd.read_csv('../input/structures.csv')

def map_atom_info(df, atom_idx):
    df = pd.merge(df, structures, how = 'left',
                  left_on  = ['molecule_name', f'atom_index_{atom_idx}'],
                  right_on = ['molecule_name',  'atom_index'])
    
    df = df.drop('atom_index', axis=1)
    df = df.rename(columns={'atom': f'atom_{atom_idx}',
                            'x': f'x_{atom_idx}',
                            'y': f'y_{atom_idx}',
                            'z': f'z_{atom_idx}'})
    return df

train = map_atom_info(train, 0)
train = map_atom_info(train, 1)

test = map_atom_info(test, 0)
test = map_atom_info(test, 1)

train.isnull().sum()

train['molecule_name_unique'] = train['molecule_name'].map(train.groupby(train['molecule_name'])['molecule_name'].nunique())
test['molecule_name_unique'] = test['molecule_name'].map(test.groupby(test['molecule_name'])['molecule_name'].nunique())
train['molecule_name_type'] = train['molecule_name'].map(train.groupby(train['molecule_name'])['type'].nunique())
test['molecule_name_type'] = test['molecule_name'].map(test.groupby(test['molecule_name'])['type'].nunique())
train['molecule_dist_mean'] = train['molecule_name'].map(train.groupby(train['molecule_name'])['dist'].mean())
test['molecule_dist_mean'] = test['molecule_name'].map(test.groupby(test['molecule_name'])['dist'].mean())
train['molecule_dist_sum'] = train['molecule_name'].map(train.groupby(train['molecule_name'])['dist'].sum())
test['molecule_dist_sum'] = test['molecule_name'].map(test.groupby(test['molecule_name'])['dist'].sum())
train['molecule_dist_min'] = train['molecule_name'].map(train.groupby(train['molecule_name'])['dist'].min())
test['molecule_dist_min'] = test['molecule_name'].map(test.groupby(test['molecule_name'])['dist'].min())
train['molecule_atom_count'] = train['molecule_name'].map(train.groupby(train['molecule_name'])['atom_1'].count())
test['molecule_atom_count'] = test['molecule_name'].map(test.groupby(test['molecule_name'])['atom_1'].count())
train['molecule_atom_u'] = train['molecule_name'].map(train.groupby(train['molecule_name'])['atom_1'].nunique())
test['molecule_atom_u'] = test['molecule_name'].map(test.groupby(test['molecule_name'])['atom_1'].nunique())
train['type_unique'] = train['type'].map(train.groupby(train['type'])['type'].nunique())
test['type_unique'] = test['type'].map(test.groupby(test['type'])['type'].nunique())
train['type_dist_mean'] = train['type'].map(train.groupby(train['type'])['dist'].mean())
test['type_dist_mean'] = test['type'].map(test.groupby(test['type'])['dist'].mean())
train['type_dist_sum'] = train['type'].map(train.groupby(train['type'])['dist'].sum())
test['type_dist_sum'] = test['type'].map(test.groupby(test['type'])['dist'].sum())
train['type_dist_min'] = train['type'].map(train.groupby(train['type'])['dist'].min())
test['type_dist_min'] = test['type'].map(test.groupby(test['type'])['dist'].min())
train['type_atom_count'] = train['type'].map(train.groupby(train['type'])['atom_1'].count())
test['type_atom_count'] = test['type'].map(test.groupby(test['type'])['atom_1'].count())
train['type_atom_u'] = train['type'].map(train.groupby(train['type'])['atom_1'].nunique())
test['type_atom_u'] = test['type'].map(test.groupby(test['type'])['atom_1'].nunique())

train.head()