<jupyter_start><jupyter_text>This Notebook has the single purpose of creating a single file out of the resource found here: https://labs.acousticbrainz.org/million-song-dataset-echonest-archive/Since the Million Song Data Set was created with outdated IDs this allows to remap ids to other apis such as Spotify etc.1. Download file from link ftp://ftp.acousticbrainz.org/pub/acousticbrainz/acousticbrainz-labs/download/msdrosetta/millionsongdataset_echonest.tar.bz22. Unpack in '/data/raw/MillionSongFullSummary/mapping/' (~3.5GB)3. Run this Notebook to create 'mapping.csv' (reading in these files is very resource intensive and is going to create autosave points temporarily, which will be removed once the files are merged)4. You can now run the notebook -> Million Song Data Set Wrangling<jupyter_code>import os
import re
import h5py
import pandas as pd
from glob import glob, iglob
import json
%cd ../data/raw
output_filepath = '../interim/'
temp_dir = 'mapping_temp'
skip = 0

if not os.path.exists(output_filepath + temp_dir):
    os.mkdir(output_filepath + temp_dir)
else:
    for file in iglob(output_filepath + temp_dir + '/*.csv'):
        m = re.search('mapping_([0-9]+?).csv$', file)
        if m:
            file_nr = int(m.group(1))
            if file_nr > skip:
                skip = file_nr

filenames_all = glob('MillionSongFullSummary/mapping/**/*.json',recursive=True)

def chunks(l, n):
    """Yield successive n-sized chunks from l."""
    for i in range(0, len(l), n):
        yield l[i:i + n]

for i,filenames in enumerate(chunks(filenames_all,10000)):
    mapped_songs_dfs = []
    file_nr = i + 1
    if file_nr <= skip:
        print('skip:', file_nr)
        continue
    print('work on:', file_nr)
    for filename in filenames:
        with open(filename) as data_file:    
            data = json.load(data_file)
        mapped_song = pd.io.json.json_normalize(data=data['response']['songs'], record_path=['tracks'], 
                                      record_prefix='tracks.',
                                      meta=['artist_name','artist_id','id','title'])
        mapped_song = mapped_song.reindex(['tracks.catalog','tracks.foreign_id','tracks.id','artist_name','id','title'],axis='columns')
        mapped_song = mapped_song.loc[(mapped_song['tracks.catalog'] == 'spotify') | (mapped_song['tracks.catalog'] == 'musicbrainz'),:]
        mapped_songs_dfs.append(mapped_song)

    mapping_df = pd.concat(mapped_songs_dfs).reset_index(drop=True)
    mapping_df.to_csv(output_filepath + temp_dir + '/mapping_' + str(file_nr) + '.csv',sep='\t',index=False,encoding='utf-8')

print('done')
subfiles_mapping = [pd.read_csv(file,sep='\t') for file in iglob(output_filepath + temp_dir + '/mapping_*.csv')]

mapping_df = pd.concat(subfiles_mapping).reset_index(drop=True)
mapping_df.to_csv(output_filepath + '/mapping_summary.csv',sep='\t',index=False,encoding='utf-8')
# Remove Temp files and directory - Cleaning Up
[os.remove(file) for file in iglob(output_filepath + temp_dir + '/mapping_*.csv')]
try:
    os.remove(output_filepath + temp_dir + '/.DS_Store')
except:
    print('No .DS_Store file. You\'re all set!')
    
os.rmdir(output_filepath + temp_dir)<jupyter_output>No .DS_Store file. You're all set!
{'fraction_non_alphanumeric': 0.13189522342064716, 'fraction_numerical': 0.0064714946070878274, 'mean_word_length': 3.8088888888888888, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 6, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '359939', 'license': 'MIT', 'n_tokens_mistral': 1116, 'n_tokens_neox': 1064, 'n_words': 252, 'path': 'notebooks/Exploration - Echonest Mapping Project.ipynb', 'repo_name': 'Germoe/hit-predictor', 'size': '3245'}