<jupyter_start><jupyter_text>Tend to redo the workflow after modifying the bug found in build_database_01.<jupyter_code>import numpy as np 
import pandas as pd
import glob
import matplotlib.pyplot as plt

#plt.style.use('ggplot')
plt.style.use('seaborn-colorblind')
plt.rcParams['figure.dpi'] = 300
plt.rcParams['savefig.dpi'] = 300
plt.rcParams['savefig.bbox'] = 'tight'

%matplotlib inline

import datetime
date = datetime.datetime.now().strftime('%Y%m%d')<jupyter_output><empty_output><jupyter_text>Read and build spectral datasets  The original scans<jupyter_code>core_list = glob.glob('data/SO264*')
core_list.sort()
core_list
# SO264-54-2 has a section naming the section depth 10430B
# so we deal with it later
core_list.remove('data/SO264-54-2')
file_name = []
spe_all = []
depth_all = []
cps_all = []
core_all = []
s_depth_all = []

# do it core by core
for core in core_list:
    i = 0
    start_depth = 0
    
    # only read the 10kV which having better signal to the light elements
    spe_dir = glob.glob('{}/Run 1 at  10kV/*.spe'.format(core))
    
    # make sure the spe directories sorted by the depth number
    spe_dir.sort()
    
    for spe in spe_dir:
        check_depth = spe.split()[3].split('_')[-1]

        # 5 and 6 digis means in mm
        # I don't use >= 5 becasue I need to make sure I'm noticed
        # if there is 7 difits or more
        if (len(check_depth) == 5) or (len(check_depth) == 6):
            check_depth = int(check_depth)
        # 3 and 4 digit means in cm, needs to be multipled to be mm
        elif len(check_depth) >= 3:
            check_depth = int(check_depth) * 10

        # this shouldn't happen, but just check
        # it did happens...
        else:
            print('Weird digits: {}'.format(spe))
                
        # if the section changes, the start depth changes
        if start_depth != check_depth:
            # reset the number for calculate composite depth
            i = 0
            start_depth = check_depth

        file_name.append(spe.split('/')[-1])
        with open(spe, 'r') as f:
            content = []
            lines = f.readlines()
            for line in lines[49:]:
                content = np.hstack((content, line.split()))

            spe_all.append(content.astype(int))
            depth_all.append(start_depth + i)
            cps_all.append(int(lines[28]))
            core_all.append(core[5:])
            s_depth_all.append(i)
            
        # the scanning resolution is 10 mm
        i += 10
    print('core {} is done.'.format(core))<jupyter_output>core data/SO264-09-2 is done.
core data/SO264-13-2 is done.
core data/SO264-14-1 is done.
core data/SO264-15-2 is done.
core data/SO264-16-2 is done.
core data/SO264-19-2 is done.
core data/SO264-22-2 is done.
core data/SO264-24-3 is done.
core data/SO264-26-2 is done.
core data/SO264-28-2 is done.
core data/SO264-32-2 is done.
core data/SO264-34-2 is done.
core data/SO264-41-2 is done.
core data/SO264-44-2 is done.
core data/SO264-44-3 is done.
core data/SO264-45-2 is done.
core data/SO264-46-5 is done.
core data/SO264-47-2 is done.
core data/SO264-49-2 is done.
core data/SO264-51-2 is done.
core data/SO264-52-2 is done.
core data/SO264-53-2 is done.
core data/SO264-55-1 is done.
core data/SO264-56-2 is done.
core data/SO264-60-12 is done.
core data/SO264-62-2 is done.
core data/SO264-64-1 is done.
core data/SO264-66-2 is done.
core data/SO264-70-1 is done.
core data/SO264-76-1 is done.<jupyter_text>Deal with the SO264-54-2SO264-54-2 is the core excluded above and has a section naming the section depth 10430B.<jupyter_code>i = 0
start_depth = 0
# the rest list variables adopt the variables in the cores above

# only read the 10kV which having better signal to the light elements
spe_dir = glob.glob('data/SO264-54-2/Run 1 at  10kV/*.spe'.format(core))
# make sure the spe directories sorted by the depth number
spe_dir.sort()

for spe in spe_dir:
    check_depth = spe.split()[3].split('_')[-1]
    
    # deal with this section separately
    if check_depth == '10430B':
        start_depth = 10430
        file_name.append(spe.split('/')[-1])
        with open(spe, 'r') as f:
            content = []
            lines = f.readlines()
            for line in lines[49:]:
                content = np.hstack((content, line.split()))

            spe_all.append(content.astype(int))
            # the way calculating the composite depth is different in this section
            # the depth starts from 10.0 mm so I need to - 10 to calibrate it
            # the depth is recorded as '1000.0\n'
            depth_all.append(int(lines[13][:-3]) + start_depth - 10)
            cps_all.append(int(lines[28]))
            core_all.append('SO264-54-2')
            s_depth_all.append(int(lines[13][:-3]) - 10)
            
    else:       
        # if the section changes, the start depth changes
        if start_depth != int(check_depth):
            # reset the number for calculate composite depth
            i = 0

            # the depth are all 5 digits in this core
            start_depth = int(check_depth)

        file_name.append(spe.split('/')[-1])
        with open(spe, 'r') as f:
            content = []
            lines = f.readlines()
            for line in lines[49:]:
                content = np.hstack((content, line.split()))

            spe_all.append(content.astype(int))
            depth_all.append(start_depth + i)
            cps_all.append(int(lines[28]))
            core_all.append('SO264-54-2')
            s_depth_all.append(i)

        # the scanning resolution is 10 mm
        i += 10
len(spe_all)
data_df = pd.DataFrame(spe_all, columns = [str(_) for _ in range(2048)])
data_df['cps'] = cps_all
data_df['core'] = core_all
data_df['composite_depth_mm'] = depth_all
data_df['section_depth_mm'] = s_depth_all
data_df['filename'] = file_name
data_df.shape
data_df.head()
data_df.tail()<jupyter_output><empty_output><jupyter_text>The rescan<jupyter_code>core_list = glob.glob('data/SO*/Rescan*')
core_list.sort()
core_list
file_name = []
spe_all = []
depth_all = []
cps_all = []
core_all = []
s_depth_all = []

# do it core by core
for core in core_list:
    i = 0
    start_depth = 0
    
    # only read the 10kV which having better signal to the light elements
    spe_dir = glob.glob('{}/Run 1 at  10kV/*.spe'.format(core))
    
    # make sure the spe directories sorted by the depth number
    spe_dir.sort()
    
    for spe in spe_dir:
        # for Rescan, it is different
        check_depth = spe.split()[3].split('/')[1].split('_')[1]

        # 5 and 6 digis means in mm
        # I don't use >= 5 becasue I need to make sure I'm noticed
        # if there is 7 difits or more
        if (len(check_depth) == 5) or (len(check_depth) == 6):
            check_depth = int(check_depth)
        # 3 and 4 digit means in cm, needs to be multipled to be mm
        elif len(check_depth) >= 3:
            check_depth = int(check_depth) * 10

        # this shouldn't happen, but just check
        # it did happens...
        else:
            print('Weird digits: {}'.format(spe))
                
        # if the section changes, the start depth changes
        if start_depth != check_depth:
            # reset the number for calculate composite depth
            i = 0
            start_depth = check_depth

        file_name.append(spe.split('/')[-1])
        with open(spe, 'r') as f:
            content = []
            lines = f.readlines()
            for line in lines[49:]:
                content = np.hstack((content, line.split()))

            spe_all.append(content.astype(int))
            depth_all.append(start_depth + i)
            cps_all.append(int(lines[28]))
            core_all.append(core.split('/')[1])
            s_depth_all.append(i)
            
        # the scanning resolution is 10 mm
        i += 10
    print('core {} is done.'.format(core))
rescan_df = pd.DataFrame(spe_all, columns = [str(_) for _ in range(2048)])
rescan_df['cps'] = cps_all
rescan_df['core'] = core_all
rescan_df['composite_depth_mm'] = depth_all
rescan_df['section_depth_mm'] = s_depth_all
rescan_df['filename'] = file_name
rescan_df.shape
rescan_df.head()
rescan_df.tail()
rescan_df[(rescan_df.core == 'SO264-55-1') & (rescan_df.composite_depth_mm > 900) & (rescan_df.composite_depth_mm < 1000)]
rescan_df[rescan_df.core == 'SO264-55-1'].to_csv('results/check.csv')<jupyter_output><empty_output><jupyter_text>Merge two datasets (original and rescan)Since the section start depth of each Rescan is consistent to the original scan, I can attach the rescan data under the original data and use drop_duplicates to preserve the last element only. This way can replace the original data by the rescan data in the same section and also some overlapping scanning data by the latest data points.<jupyter_code>all_df = pd.concat([data_df, rescan_df], axis = 0, join = 'inner')
all_df.shape
len(data_df) + len(rescan_df)
all_df.head()<jupyter_output><empty_output><jupyter_text>Build composite_id<jupyter_code>all_df.composite_depth_mm.max()
composite_id = []
for core, depth in zip(all_df.core, all_df.composite_depth_mm):
    composite_id.append('{}_{:05}'.format(core, depth))
    
all_df['composite_id'] = composite_id<jupyter_output><empty_output><jupyter_text>Drop duplicates<jupyter_code>clean_df = all_df.drop_duplicates('composite_id', keep = 'last')
len(clean_df)<jupyter_output><empty_output><jupyter_text>Check those duplicates<jupyter_code>all_df.loc[all_df.composite_id.duplicated(keep = 'last'), all_df.columns[-6:]].to_csv('results/check_duplicates_1.csv')
all_df.loc[all_df.composite_id.duplicated(keep = 'last'), all_df.columns[-6:]]
np.unique(all_df.loc[all_df.composite_id.duplicated(keep = 'last'), 'core'], return_counts = True)<jupyter_output><empty_output><jupyter_text>The cores having more than 100 data pooints are weird. Let' check.<jupyter_code>core_list<jupyter_output><empty_output><jupyter_text>Let's check those cores having more than 100 data points being duplicates.SO264-09-2, SO264-28-2, SO264-51-2, SO264-53-2: rescaned SO264-15-2: there are 0048 spe and 048 spe in the original folder, I reckon it's a rescan but not mentioned.<jupyter_code>clean_df = clean_df.set_index('composite_id')
# I prefer "filename", which is consistent to Itrax's format
clean_df['filename'] = clean_df.file_name
clean_df.drop('file_name', axis = 1, inplace = True)
clean_df.head()
clean_df[clean_df.filename.duplicated()]<jupyter_output><empty_output><jupyter_text>Check nan<jupyter_code>clean_df.isnull().values.any()<jupyter_output><empty_output><jupyter_text>Build section<jupyter_code>clean_df = pd.read_csv('data/spe_dataset_20201007.csv', index_col = 0)
clean_df[clean_df.section_depth_mm == 0]
clean_df.sort_values(by = 'composite_id', axis = 0, inplace = True)
clean_df
section_all = []
# make sure the order follows the core and composite depth
clean_df.sort_values(by = 'composite_id', axis = 0, inplace = True)

for core in np.unique(clean_df.core):    
    # I assume every core scanned from 0 mm so the first section in the core is marked as section 0
    # the deeper the larger number
    section = -1
    X = clean_df[clean_df.core == core]
    for index, row in X.iterrows():
        if row.section_depth_mm == 0:
            section += 1
        section_all.append(section)
clean_df['section'] = section_all
clean_df
np.argmax(clean_df.iloc[:, :2048].mean())
clean_df.loc[(clean_df.core == 'SO264-76-1') & (clean_df.section == 0), '185'].plot()<jupyter_output>/home/users/aslee/miniconda3/lib/python3.7/site-packages/pandas/plotting/_matplotlib/core.py:1235: UserWarning: FixedFormatter should only be used together with FixedLocator
  ax.set_xticklabels(xticklabels)<jupyter_text>I don't have any idea to test if section numbers are all right yet...  Export dataset<jupyter_code>clean_df.to_csv('data/spe_dataset_{}.csv'.format(date))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11791307260698435, 'fraction_numerical': 0.03994640314881501, 'mean_word_length': 3.071599045346062, 'pattern_counts': {'":': 0, '<': 52, '<?xml version=': 0, '>': 56, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '539226', 'license': 'MIT', 'n_tokens_mistral': 4365, 'n_tokens_neox': 4012, 'n_words': 1062, 'path': 'build_database_02.ipynb', 'repo_name': 'dispink/CaCO3_NWP', 'size': '11941'}