<jupyter_start><jupyter_text>Comparing MP and ICSDLoad the modules and the two databases separately<jupyter_code>import os
import pandas as pd
import matplotlib.pyplot as plt 

MATERIALS_PROJECT_PKL = os.path.join("mp","materials_project.pkl")
mpdf = pd.read_pickle(MATERIALS_PROJECT_PKL)
mpdf
ICSD_AUG_PKL = os.path.join("icsd","all_icsd_cifs_augmented.pkl")
icsdf = pd.read_pickle(ICSD_AUG_PKL)
icsdf<jupyter_output><empty_output><jupyter_text>Merge the two datasets by mapping the `icsd_ids` of the MP databaseDue to multiple entries of MP mapping to multiple entries of ICSD, I could not find a "pandas" way to do the merge, hence multiple loops necessary, and significative run time.<jupyter_code>icsdf_idx = icsdf.set_index(icsdf['_database_code_ICSD'].astype(int))  ## rows are now indexed by icsd_id
mpdf_idx = mpdf.set_index(mpdf['material_id'])  ## rows are now indexed by material_id

combined_rows = []
used_icsd_ids = []
wrong_icsd_ids = []
mp_rows = mpdf_idx.to_dict('records')
for mp_row in mp_rows:
    if mp_row['icsd_ids']:
        for icsd_id in mp_row['icsd_ids']:
            try: ## sometimes the icsd_id doesn't exist
                icsd_row = icsdf_idx.loc[icsd_id].to_dict()
                used_icsd_ids.append(icsd_id)
            except KeyError as e:
                wrong_icsd_ids.append(e)
                continue
            tmp_dict = {}
            for k,v in mp_row.items():
                tmp_dict['mp_'+k] = v
            for k,v in icsd_row.items():
                tmp_dict['icsd_'+k] = v
            combined_rows.append(tmp_dict)
    else:
        tmp_dict = {}
        for k,v in mp_row.items():
            tmp_dict['mp_'+k] = v
        combined_rows.append(tmp_dict)

remaining_icsd_rows = icsdf_idx.loc[~icsdf_idx.index.isin(used_icsd_ids)].to_dict('records')
for icsd_row in remaining_icsd_rows:
    tmp_dict = {}
    for k,v in icsd_row.items():
        tmp_dict['icsd_'+k] = v
    combined_rows.append(tmp_dict)

super_df = pd.DataFrame(combined_rows)
super_df<jupyter_output><empty_output><jupyter_text>How many MP materials map into ICSD and vice-versa<jupyter_code>icsd_mask = ~super_df['icsd__database_code_ICSD'].isna()
mp_mask = ~super_df['mp_material_id'].isna()
grouped_icsd = super_df.loc[icsd_mask & mp_mask].groupby('icsd__database_code_ICSD')
grouped_mp = super_df.loc[icsd_mask & mp_mask].groupby('mp_material_id')

print(f"{len(wrong_icsd_ids)} bad icsd_ids in the icsd_ids list in MP\n")
print(f"{len(grouped_mp)} materials from MP map to ICSD entries")
print(f"with maximum {grouped_mp['mp_material_id'].agg('count').max()} ICSD entries for one MP entry\n")
print(f"{len(grouped_icsd)} materials from ICSD map to MP entries")
print(f"with maximum {grouped_icsd['mp_material_id'].agg('count').max()} MP entries for one ICSD entry")<jupyter_output>4018 bad icsd_ids in the icsd_ids list in MP

47190 materials from MP map to ICSD entries
with maximum 131 ICSD entries for one MP entry

84856 materials from ICSD map to MP entries
with maximum 4 MP entries for one ICSD entry<jupyter_text>Are the matrials in ICSD stable in MP (`e_above_hull`) ?<jupyter_code>#%% ICSD? Stable?
stability_treshold = 0.1
have_icsd = mpdf[mpdf['icsd_ids'].astype(bool)]
warnings = mpdf[mpdf['warnings'].astype(bool)]
stable_icsd = mpdf[
    (mpdf['icsd_ids'].astype(bool))
    & (mpdf["e_above_hull"] <= stability_treshold)
    & (~mpdf['warnings'].astype(bool))
]

print(f"\n{len(have_icsd)} entries have ICSD ids")
print(f"{len(warnings)} entries have warnings")
print(f"{len(stable_icsd)}/{len(have_icsd)} entries have ICSD ids, are stable, and have no warnings")

have_icsd.hist(column="e_above_hull", bins=1000)
plt.yscale('log')<jupyter_output>48485 entries have ICSD ids
544 entries have warnings
38725/48485 entries have ICSD ids, are stable, and have no warnings<jupyter_text>Comparison with external subsetThis part was intended to compare the full dataset with the one from someone else. In our case, it was used with an intern's version of MP which did not included the single-site materials and the 9 sites materials. The subset should be provided as available as `mp/subset_ids.csv`, otherwise this cell won't work.<jupyter_code>SUBSET_MPIDS_CSV = os.path.join("mp","subset_ids.csv")
subset_ids = pd.read_csv(SUBSET_MPIDS_CSV, names=["material_id"])
print(len(subset_ids), "material_ids")
subset_mpdf = mpdf[mpdf["material_id"].isin(subset_ids["material_id"])]
print(len(subset_mpdf), "materials selected")
print(len(subset_mpdf)-len(subset_ids), "missing; all subset ids were found")
print(len(mpdf)-len(subset_mpdf), "missing from subset")<jupyter_output>125042 material_ids
125042 materials selected
0 missing; all subset ids were found
1105 missing from subset<jupyter_text>Comparison with CITRINE (old version of MP) CITRINE's version of MP must be prepared otherwise this cell's won't workDownload it at https://citrination.com/datasets/188648/show_files and then process the PIFs format with    python mp/citrine_to_df.pywhich saves a DataFrame in `citrine_mp.pkl` for that dataset, and allow for the following comparison.<jupyter_code>CITRINE_MP_PKL = os.path.join("mp","citrine_mp.pkl")
citrine_df = pd.read_pickle(CITRINE_MP_PKL)
print(f"loaded {len(citrine_df)} materials")
print("\navailable columns:")
# for name in list(citrine_df.columns): print(" ",name)

mpdf_in_citrine = mpdf[mpdf["material_id"].isin(citrine_df["material_id"])]
print(f"\n{len(mpdf_in_citrine)} in mpdf/{len(citrine_df)} in citrine, {len(citrine_df)-len(mpdf_in_citrine)} ids of citrine are not in mpdf") 

citrine_in_mpdf = citrine_df[citrine_df["material_id"].isin(mpdf_in_citrine["material_id"])]
citrine_not_in_mpdf = citrine_df[~citrine_df["material_id"].isin(mpdf_in_citrine["material_id"])]

print("\ncommon columns")
for name in list(citrine_in_mpdf.columns):
    if name in list(mpdf_in_citrine.columns):
        print(" ",name)

print("\nSorting...")
citrine_in_mpdf = citrine_in_mpdf.sort_values("material_id", ignore_index=True)
mpdf_in_citrine = mpdf_in_citrine.sort_values("material_id", ignore_index=True)
print(f"{all(citrine_in_mpdf['material_id']==mpdf_in_citrine['material_id'])} : all ids are the same")
print(f"{all(citrine_in_mpdf['pretty_formula']==mpdf_in_citrine['pretty_formula'])} : all pretty_formula are the same")

print("\nICSD ids")
print(f"{len(citrine_df[citrine_df['icsd_ids'].astype(bool)])}/{len(citrine_df)} of CITRINE have ICSD ids (all of them)")
print(f"{len(mpdf[mpdf['icsd_ids'].astype(bool)])}/{len(mpdf)} of current MP have ICSD ids")
print(f"{len(citrine_df[citrine_df['icsd_ids'].astype(bool)])-len(mpdf[mpdf['icsd_ids'].astype(bool)])} have no ICSD id anymore")
print(f"Probably has to do with CITRINE being from January 2019")

for column in [
    "final_energy",
    "final_energy_per_atom",
    "volume",
    "nsites",
    "formation_energy_per_atom",
    "e_above_hull",
    "band_gap",
    "total_magnetization",
]:
    x = citrine_in_mpdf[column].rename(f"updated_{column}")
    y = mpdf_in_citrine[column].rename(f"citrine_{column}")
    plot_df = pd.concat([x, y], axis=1)
    plot_df.plot.scatter(f"citrine_{column}",f"updated_{column}")
    plt.title(f"{column}\nonly {len(citrine_in_mpdf[citrine_in_mpdf[column]==mpdf_in_citrine[column]])}/{len(citrine_in_mpdf)} are the same")
    plt.show()
    # plt.savefig(PLOTS_DIR/f"updated_citrine_{column}")<jupyter_output>loaded 48833 materials

available columns:

48650 in mpdf/48833 in citrine, 183 ids of citrine are not in mpdf

common columns
  last_updated
  material_id
  original_task_id
  icsd_ids
  pretty_formula
  final_energy
  final_energy_per_atom
  volume
  nsites
  formation_energy_per_atom
  e_above_hull
  band_gap
  total_magnetization

Sorting...
True : all ids are the same
True : all pretty_formula are the same

ICSD ids
48833/48833 of CITRINE have ICSD ids (all of them)
48485/126147 of current MP have ICSD ids
348 have no ICSD id anymore
Probably has to do with CITRINE being from January 2019
{'fraction_non_alphanumeric': 0.11684236947791164, 'fraction_numerical': 0.014683734939759037, 'mean_word_length': 5.5, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 21, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '323216', 'license': 'MIT', 'n_tokens_mistral': 3053, 'n_tokens_neox': 2881, 'n_words': 663, 'path': 'materials_datasets/example_mp_vs_icsd.ipynb', 'repo_name': 'simonverret/materials_data_api_scripts', 'size': '7968'}