<jupyter_start><jupyter_text>Sanitizing DataOften data is messy. In our case we have two problems with the data. - values are missing - not in a format easy to do machine learning on. Machine Learning algorithms and in general all statistics algorithms like arrays.<jupyter_code>import json
from collections import defaultdict
import re

import pandas as pd
import numpy as np
import requests

def convert_dict_to_pandas_frame(data, dict_frames):
    data_columns = defaultdict(list)
    for d in data.values():
        for key in dict_frames:
            subkey = d
            subkeys = dict_frames[key].split('.')
            for k in subkeys:
                if re.match(r'\d+', k) and subkey is not None:
                    index = int(k)
                    if index >= len(subkey):
                        subkey = None
                    else:
                        subkey = subkey[index]
                else:
                    if subkey is not None:
                        subkey = subkey.get(k)
            data_columns[key].append(subkey)
    df = pd.DataFrame.from_dict(data_columns)
    return df.set_index('material_id')
## data will not be available locally so we need to download it
# data = json.load(open('../data/mpdata.json'))
data = requests.get('https://gitlab.com/costrouc/mse-machinelearning-notebooks/raw/master/data/mpdata.json').json()
print('Number of materials', len(data))<jupyter_output>Number of materials 6928<jupyter_text>If we look at one of the materials we can see that the data is not in the best format.<jupyter_code>first_key = next(iter(data))
data[first_key]<jupyter_output><empty_output><jupyter_text>Always aim to convert your data to a tabular form first<jupyter_code>key_map = {
    'material_id': 'material_id',
    'energy': 'energy',
    'volume': 'volume',
    'nsites': 'nsites',
    'energy_per_atom': 'energy_per_atom',
    'pretty_formula': 'pretty_formula',
    'spacegroup': 'spacegroup.number',
    'band_gap': 'band_gap',
    'density': 'density',
    'total_magnetization': 'total_magnetization',
    # Elacticity
    'poisson_ratio': 'elasticity.poisson_ratio',
    'bulk_modulus_voigt': 'elasticity.K_Voigt',
    'bulk_modulus_reuss': 'elasticity.K_Reuss',
    'bulk_modulus_vrh': 'elasticity.K_VRH',
    'shear_modulus_voigt': 'elasticity.G_Voigt',
    'shear_modulus_vrh': 'elasticity.G_VRH'
}

df = convert_dict_to_pandas_frame(data, key_map)
df.info()
df.sample(5)
# write the data to a csv file
df.to_csv('../data/mpdata.csv')<jupyter_output><empty_output><jupyter_text>Lets Explore the DataExploring the data is important.<jupyter_code># how many materials are we looking at?
len(df)
# look at most common spacegroups
df['spacegroup'].value_counts()[:25]
# band gaps
print('how many are metals?', len(df[df['band_gap'] == 0.0]), '/', len(df))
print('material with highest band gap', df['band_gap'].max(), df.loc[df['band_gap'].idxmax()]['pretty_formula'])

condition = (df['band_gap'] > 0.0) & (df['band_gap'] < 10)
df[condition]['band_gap'].plot.hist(bins=100)
# cohesive energies
df['energy'].plot.hist(bins=100)
conditions = (df['shear_modulus_vrh'] > 0) & (df['shear_modulus_vrh'] < 500) & \
             (df['bulk_modulus_vrh'] > 0) & (df['bulk_modulus_vrh'] < 500) & \
             (df['poisson_ratio'] > 0) & (df['poisson_ratio'] < 0.5)
# shear modulus vrh of metals vs nonmetals

is_metal = df['band_gap'] == 0.0
is_not_metal = df['band_gap'] > 0.0
bins = 10**np.linspace(0, 2.7, 25) # 10^2.7 ~ 500
ax = df[conditions & is_not_metal]['shear_modulus_vrh'].plot.hist(bins=bins, alpha=0.5, density=True)
ax = df[conditions & is_metal]['shear_modulus_vrh'].plot.hist(ax=ax, bins=bins, color='red', alpha=0.5, density=True)
ax.set_xscale('log')
bins = 10**np.linspace(0, 2.7, 25) # 10^2.7 ~ 500
ax = df[conditions]['shear_modulus_vrh'].plot.hist(bins=bins)
ax.set_xscale('log')
bins = 10**np.linspace(0, 2.7, 25)
ax = df[conditions]['bulk_modulus_vrh'].plot.hist(bins=bins)
ax.set_xscale('log')
ax = df[conditions].plot(kind='scatter', x='shear_modulus_vrh', y='bulk_modulus_vrh', c='poisson_ratio')
ax.set_xscale('log')
ax.set_yscale('log')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1455772475295252, 'fraction_numerical': 0.017594601108700893, 'mean_word_length': 3.9760191846522783, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 22, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '401964', 'license': 'MIT', 'n_tokens_mistral': 1562, 'n_tokens_neox': 1458, 'n_words': 322, 'path': 'notebooks/2-explore-sanitize-data.ipynb', 'repo_name': 'costrouc/mse-machinelearning-notebooks', 'size': '4149'}