<jupyter_start><jupyter_text>This notebook is to create the preparation information and sample information files for Qiita, as well as the mapping file for Qiime.<jupyter_code>import numpy as np
import pandas as pd<jupyter_output><empty_output><jupyter_text>create preparation_info document<jupyter_code>info = pd.read_csv('../data/Demultiplex_Sheet.txt', sep='\t')
info = info.sort_values(['#SampleID'])
info.head()
info = info.drop_duplicates(subset=['#SampleID', 'ReversePrimer'])
info.head()
info.shape[0]
n = info.shape[0]
data = {'Sample_Name': info.loc[:,'#SampleID'].str[7:13].values, # .values takes only value not index
       'Barcode': info.loc[:,'BarcodeSequence'].values,
       'LinkerPrimerSequence': info.loc[:, 'LinkerPrimerSequence'].values,
       'Description': info.loc[:,'Description'].values,
       'Experiment_Design_Description': np.repeat(
           '16S stool samples sequenced for MrOS Vitamin D study', n),
       'Library_Construction_Protocol': np.repeat('16S rRNA v4', n),
       'Linker': np.repeat('GT', n),
       'Platform': np.repeat('Illumina', n),
       'Center_Name': info.loc[:,'#SampleID'].str[14:16].values,
       'Center_Project': np.repeat('MrOS', n),
       'Instrument_Model': np.repeat('Illumina MiSeq', n)}
prep_info = pd.DataFrame(data, columns=['Sample_Name', 'Barcode', 'LinkerPrimerSequence', 
                                        'Description', 
                                        'Experiment_Design_Description',
                                        'Library_Construction_Protocol', 'Linker', 'Platform',
                                        'Center_Name', 'Center_Project', 'Instrument_Model'])
prep_info.shape
prep_info.head(10)
# fix mismatch sample name 'BI0778' should be 'BIO778' (Nora's email on 08/07/2017)
prep_info = prep_info.replace(to_replace='BIO778', value='BI0778')<jupyter_output><empty_output><jupyter_text>Create sample_info document<jupyter_code>samples_part1 = pd.read_csv('../data/VitDMetadata_update.csv', sep=',')
samples_part2 = pd.read_csv('../data/Other32metadata.csv', sep=',')
print(samples_part1.shape)
print(samples_part2.shape)
print(samples_part1.columns)
print(samples_part2.columns)
# merge two metadata files
samples = pd.concat([samples_part1, samples_part2], ignore_index=True)
samples.shape
len(pd.unique(samples.ID))
samples.head(10)
sites = samples.loc[:, 'SITE']
# reference: http://www.latlong.net/
Latitude = []
Longitude = []
sites = samples.loc[:, 'SITE']
for i in range(samples.shape[0]):
    if sites[i] == 'Birmingham':
        latitude = '33.520661'
        longitude = '-86.80249'
    elif sites[i] == 'San Diego':
        latitude = '32.715738'
        longitude = '-117.1611'
    elif sites[i] == 'Pittsburgh':
        latitude = '40.440625'
        longitude = '-79.99589'
    elif sites[i] == 'Palo Alto':
        latitude = '37.441883'
        longitude = '-122.143'
    elif sites[i] == 'Portland':
        latitude = '45.523062'
        longitude = '-122.6765'    
    elif sites[i] == 'Minneapolis':
        latitude = '44.977753'
        longitude = '-93.26501'  
    Latitude.append(latitude)
    Longitude.append(longitude)
# simple check
print(samples['SITE'][[1,90,200,300,400, 500]])
print(np.array(Latitude)[[1,90,200,300,400, 500]])
print(np.array(Longitude)[[1,90,200,300,400, 500]])
m = samples.shape[0]
required = {'Sample_Name': samples.loc[:,'ID'].values,
        'Title': np.repeat('MrOS_VitaminD', m),
        'Anonymized_Name': samples.loc[:,'ID'].values,
        'Scientific_Name': np.repeat('human gut metagenome', m),
        'Taxon_ID': np.repeat('Not applicable', m),
        #'Description': np.repeat('Not applicable', m),
        'Sample_Type': np.repeat('stool', m),
        'Geo_Loc_Name': samples.loc[:, 'SITE'].values,
        'Elevation': np.repeat('Not applicable', m),
        'Env_Biome': np.repeat('urban biome', m),
        'Env_Feature': np.repeat('human-associated habitat', m),
        'Env_Material': np.repeat('feces', m),
        'Env_Package': np.repeat('human-gut', m),
        'Latitude': Latitude,
        'Longitude': Longitude,
        'Collection_Timestamp': np.repeat('Not applicable', m),
        'DNA_Extracted': np.repeat('Not applicable', m),
        'Physical_Specimen_Location': np.repeat('Not applicable', m),
        'Physical_Specimen_Remaining': np.repeat('Not applicable', m),
        'Age': samples.loc[:,'V4AGE1'].values,
        'Age_Units': np.repeat('years', m),
        'Host_Subject_ID': np.repeat('Not applicable', m),
        'Host_Taxid': np.repeat('Not applicable', m),
        'Host_Scientific_Name': np.repeat('Homo sapiens', m),
        'Host_Common_Name': np.repeat('human', m),
        'Life_Stage': np.repeat('adult', m),
        'Sex': np.repeat('male', m),
        'Height': samples.loc[:, 'HWHGT'].values,
        'Height_Units': np.repeat('cm', m),
        'Weight': samples.loc[:, 'HWWGT'].values,
        'Weight_Units': np.repeat('kg', m),  
        'BMI': samples.loc[:, 'HWBMI'].values,
        'Body_Habitat': np.repeat('UBERON:feces', m),
        'Body_Site': np.repeat('UBERON:feces', m),
        'Body_Product': np.repeat('UBERON:feces', m)}
sample_info = pd.concat([pd.DataFrame(required), samples], axis=1)
sample_info = pd.DataFrame(sample_info, columns = ['Sample_Name', 'Title', 'Anonymized_Name',
          'Scientific_Name', 'Taxon_ID', 
          #'Description', 
          'Sample_Type', 'Geo_Loc_Name', 'Elevation',
          'Env_Biome', 'Env_Feature', 'Env_Material', 'Env_Package', 'Latitude', 'Longitude',
          'Collection_Timestamp', 'DNA_Extracted', 'Physical_Specimen_Location', 'Physical_Specimen_Remaining',
          'Age', 'Age_Units', 'Host_Subject_ID', 'Host_Taxid', 'Host_Scientific_Name', 'Host_Common_Name',
          'Life_Stage', 'Sex', 'Height', 'Height_Units', 'Weight', 'Weight_Units', 'BMI', 'Body_Habitat',
          'Body_Site', 'Body_Product', 'GIERACE', 'SITE', 'TUDRAMT', 'PASCORE', 'TURSMOKE', 'DTVITD', 
          'M1ADEPR', 'M1VITMND', 'M1ANTIB', 'M1PROBI', 'OHV1D3', 'OHV24D3', 
          'OHVD3', 'OHVD2', 'OHV1D2', 'OHV1D2CT', 'OHVD2CT', 'OHVDTOT', 'OHV1DTOT', 'OHSEAS', 'VDstatus'])
sample_info.shape
sample_info.head(10)<jupyter_output><empty_output><jupyter_text>Match sample_name in two documents<jupyter_code>id_prep=list(prep_info.loc[:,'Sample_Name'].values)
id_sample=list(sample_info.loc[:,'Sample_Name'].values)
# samples with metadata yet no sequencing data
for i in id_sample:
    if i not in id_prep:
        print(i)
# samples with sequencing data yet no metadata
no_sample_info = []
for j in id_prep:
    if j not in id_sample:
        no_sample_info.append(j)
print(len(no_sample_info))
print(no_sample_info)<jupyter_output>1
['PO7100']<jupyter_text>Drop un-needed samples<jupyter_code># exclude PO7100, as it has no microbiome data (see <NAME>'s email on 08/07/2017)
prep_info = prep_info.loc[~prep_info['Sample_Name'].isin(no_sample_info)]
prep_info.shape<jupyter_output><empty_output><jupyter_text>preparation and sample information output<jupyter_code>sample_info.to_csv('../data/sample_MrOS.txt', sep= '\t', na_rep='Missing:not collected', index=False)
prep_info.to_csv('../data/prep_MrOS.txt', sep= '\t', index=False)<jupyter_output><empty_output><jupyter_text>create Qiime mapping file<jupyter_code>prep_info.head(5)
mapping = pd.merge(prep_info, sample_info, on='Sample_Name')
print(prep_info.shape)
print(sample_info.shape)
print(mapping.shape)
mapping.rename(columns={'Sample_Name': '#SampleID'}, inplace=True)
mapping.rename(columns={'Barcode': 'BarcodeSequence'}, inplace=True)
mapping.shape
mapping.head(5)
# move 'Description' to the end
cols = list(mapping)
cols.insert(mapping.shape[1], cols.pop(cols.index('Description')))
mapping = mapping.loc[:, cols]
mapping.head(5)
mapping.to_csv('../data/mapping_MrOS.txt', sep= '\t', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1673868835790544, 'fraction_numerical': 0.029105236400610066, 'mean_word_length': 4.180381830151416, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '601158', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2870, 'n_tokens_neox': 2681, 'n_words': 502, 'path': '1.1. prep_sample_info (Python).ipynb', 'repo_name': 'knightlab-analyses/vitamin-d', 'size': '7870'}