notebook.community

Edit and run 





In [1]:



    


import sys, os
sys.path.insert(0,'/global/homes/b/bpb/metatlas/' )
sys.path.insert(1,'/global/project/projectdirs/metatlas/anaconda/lib/python2.7/site-packages' )
# sys.path.insert(1,'/global/project/projectdirs/metatlas/projects/metatlas' )

from metatlas.helpers import dill2plots as dp

from metatlas.helpers import metatlas_get_data_helper_fun as ma_data
from metatlas import metatlas_objects as metob
from metatlas.helpers import rt_corrector as rt_corrector
from ipywidgets import interact, interactive, fixed

import ipywidgets as widgets
from IPython.display import display

import json
import pandas as pd
pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)


%matplotlib notebook



    


















    






('Metatlas live in ', '/global/homes/b/bpb/metatlas/metatlas')
you're running on 7830b144958f at 172.17.0.3 
height has been deprecated.


















In [15]:



    


cids = metob.retrieve('Compounds',inchi_key = 'YGSDEFSMJLZEOE-UHFFFAOYSA-N')



    











In [16]:



    


for c in cids:
    print c.name, c.username



    


















    






Salicylate///Bismuth Subsalicylate///Salicylate-sodium///Q4499058///SALICYLATE///Salicylic acid///salicylate///Sodium salicylate///Trolamine salicylate///salicylic acid///Magnesium salicylate///Trisalicylate-choline bpb

















In [2]:



    


cids = metob.retrieve('CompoundIdentifications',username='*')



    











In [3]:



    


to_export = []
frag_spectra = []
frag_file = []
for c in cids:
    if c.frag_references:
        frag_spectra.append([(m.mz, m.intensity) for m in c.frag_references[0].mz_intensities])
        to_export.append(c)
df = metob.to_dataframe(to_export)
df['fragmentation_spectra'] = pd.Series(frag_spectra)



    











In [4]:



    


df.head()



    


















    
Out[4]:










  
    

      
      compound
      creation_time
      description
      frag_references
      identification_grade
      intensity_references
      last_modified
      mz_references
      name
      rt_references
      unique_id
      username
      fragmentation_spectra
    

  
  
    

      0
      [u'1975b25843974aa59bf40ab31c9c1dd2']
      2016-06-22 17:38:46
      No description
      [u'794ca716103842f8b9fcca530796f20a']
      None
      []
      2016-06-22 17:38:51
      [u'2e31392ff6964a5daff2f599fa261ce0']
      uridine
      [u'2e9fc66669094ab296f5aa761ea54f5e']
      0c317d403512429398a9fd4b8dc39209
      bpb
      [(50.6455, 1739.9), (55.0192, 2034.74), (55.11...
    

    

      1
      [u'32b6b4e0b26140b5990ce37a5bbed6df']
      2016-06-22 17:38:31
      No description
      [u'b9f41ab592d04fc594956efd33e79ba2']
      None
      []
      2016-06-22 17:38:51
      [u'770548129e2341f38fb26be6835c7e11']
      inosine
      [u'980eda3f864d46559c23d7841a51dcf1']
      0e38c27310f24260a1cc7eba8e9c511d
      bpb
      [(52.2652, 3482.39), (53.7242, 3295.81), (57.0...
    

    

      2
      [u'3a72f4d1704c419c8466710fe6b8eb04']
      2016-06-22 17:38:04
      No description
      [u'530570f2eb514d559cad1c11727e19dd']
      None
      []
      2016-06-22 17:38:51
      [u'46ad307654dc4004b8ed524196afcc71']
      arginine
      [u'c5b8e49ebdab477db6b77b6e912e1112']
      19777f8c2012449a86631f28ffdead94
      bpb
      [(60.0568, 2757480.0), (69.6344, 16756.1), (70...
    

    

      3
      [u'f6d49e7aa4b44b42beea61c7bac1d933']
      2016-06-22 17:37:35
      No description
      [u'0d099fe4d7bc4176b17441dda13d1bc6']
      None
      []
      2016-06-22 17:38:51
      [u'7d121317ca984972848680f944717239']
      valine
      [u'b92fe3653ae14b4e9fc01be0fbfbdb11']
      205c13b7a3f4424a91dbfdba7c1940fb
      bpb
      [(53.1711, 2246.35), (53.2227, 2249.81), (53.8...
    

    

      4
      [u'38bc6bd85fd94d0991270ad5faf250b7']
      2016-06-22 17:37:39
      No description
      [u'cc3ec8c6210c4004b78dfbd6c3def72d']
      None
      []
      2016-06-22 17:38:51
      [u'92209a343c564e559e91a5bf71dc579f']
      abscisic acid (peak 1)
      [u'8bde7acd55c64df0b466bacd3876fd6a']
      207ea881a9304fa3bfe67130e6f76592
      bpb
      [(50.8347, 1522.24), (65.757, 1756.25), (73.02...
    

  




















In [5]:



    


import ast
def serialize_metatlas_reference_ids(row,object_name):
    c = ast.literal_eval(row[object_name])
    if c:
        c = c[0]
    else:
        c = ''
    return c

df['unique_id_Compounds'] = df.apply(serialize_metatlas_reference_ids,axis=1,object_name = 'compound')
df['unique_id_FragmentationReferences'] = df.apply(serialize_metatlas_reference_ids,axis=1,object_name = 'frag_references')
df['unique_id_MzReferences'] = df.apply(serialize_metatlas_reference_ids,axis=1,object_name = 'mz_references')
df['unique_id_RtReferences'] = df.apply(serialize_metatlas_reference_ids,axis=1,object_name = 'rt_references')
df.drop(['compound','frag_references','mz_references','rt_references','intensity_references','unique_id','description','identification_grade'], axis=1, inplace=True)
df.head()



    


















    
Out[5]:










  
    

      
      creation_time
      last_modified
      name
      username
      fragmentation_spectra
      unique_id_Compounds
      unique_id_FragmentationReferences
      unique_id_MzReferences
      unique_id_RtReferences
    

  
  
    

      0
      2016-06-22 17:38:46
      2016-06-22 17:38:51
      uridine
      bpb
      [(50.6455, 1739.9), (55.0192, 2034.74), (55.11...
      1975b25843974aa59bf40ab31c9c1dd2
      794ca716103842f8b9fcca530796f20a
      2e31392ff6964a5daff2f599fa261ce0
      2e9fc66669094ab296f5aa761ea54f5e
    

    

      1
      2016-06-22 17:38:31
      2016-06-22 17:38:51
      inosine
      bpb
      [(52.2652, 3482.39), (53.7242, 3295.81), (57.0...
      32b6b4e0b26140b5990ce37a5bbed6df
      b9f41ab592d04fc594956efd33e79ba2
      770548129e2341f38fb26be6835c7e11
      980eda3f864d46559c23d7841a51dcf1
    

    

      2
      2016-06-22 17:38:04
      2016-06-22 17:38:51
      arginine
      bpb
      [(60.0568, 2757480.0), (69.6344, 16756.1), (70...
      3a72f4d1704c419c8466710fe6b8eb04
      530570f2eb514d559cad1c11727e19dd
      46ad307654dc4004b8ed524196afcc71
      c5b8e49ebdab477db6b77b6e912e1112
    

    

      3
      2016-06-22 17:37:35
      2016-06-22 17:38:51
      valine
      bpb
      [(53.1711, 2246.35), (53.2227, 2249.81), (53.8...
      f6d49e7aa4b44b42beea61c7bac1d933
      0d099fe4d7bc4176b17441dda13d1bc6
      7d121317ca984972848680f944717239
      b92fe3653ae14b4e9fc01be0fbfbdb11
    

    

      4
      2016-06-22 17:37:39
      2016-06-22 17:38:51
      abscisic acid (peak 1)
      bpb
      [(50.8347, 1522.24), (65.757, 1756.25), (73.02...
      38bc6bd85fd94d0991270ad5faf250b7
      cc3ec8c6210c4004b78dfbd6c3def72d
      92209a343c564e559e91a5bf71dc579f
      8bde7acd55c64df0b466bacd3876fd6a
    

  




















In [6]:



    


my_tables = ['unique_id_Compounds','unique_id_FragmentationReferences','unique_id_MzReferences','unique_id_RtReferences']
table_holder = {}
for tab in my_tables:
    print tab
    obj_list = []
    for u_val in df[tab].unique():
        obj_retrieve = metob.retrieve(tab.split('_')[-1],unique_id = u_val,username = '*')
        if obj_retrieve:
            obj_list.append(obj_retrieve[0])
#         else:
#             print tab,u_val,'not found'
    table_holder[tab] = metob.to_dataframe(obj_list)
    table_holder[tab].rename(columns={'unique_id': tab}, inplace=True)
    print table_holder[tab].shape



    


















    






unique_id_Compounds
(321, 33)
unique_id_FragmentationReferences
(391, 14)
unique_id_MzReferences
(336, 16)
unique_id_RtReferences
(336, 13)

















In [7]:



    


temp_df = pd.merge(left = df,
                   right = table_holder[my_tables[0]],
                   left_on='unique_id_Compounds',
                   right_on='unique_id_Compounds',
                   suffixes = ('_CompoundIdentifications','_Compounds'),
                   how='left')
# temp_df.drop_duplicates(inplace=True)

for tab in my_tables[1:]:
    print tab
    temp_df = pd.merge(left = temp_df,
                       right = table_holder[tab],
                       left_on=tab,
                       right_on=tab,
                       suffixes = ('_CompoundIdentifications','_'+tab.split('_')[-1]),
                       how='left')
#     temp_df.drop_duplicates(inplace=True)
temp_df.rename(columns={'lcms_run_CompoundIdentifications': 'lcms_run_FragmentationReferences'}, inplace=True)
temp_df.rename(columns={'lcms_run': 'lcms_run_RtReferences'}, inplace=True)

print df.shape,temp_df.shape
temp_df.head()



    


















    






unique_id_FragmentationReferences
unique_id_MzReferences
unique_id_RtReferences
(391, 9) (391, 81)










    
Out[7]:










  
    

      
      creation_time_CompoundIdentifications
      last_modified_CompoundIdentifications
      name_CompoundIdentifications
      username_CompoundIdentifications
      fragmentation_spectra
      unique_id_Compounds
      unique_id_FragmentationReferences
      unique_id_MzReferences
      unique_id_RtReferences
      chebi_id
      chebi_url
      creation_time_Compounds
      description_CompoundIdentifications
      formula
      hmdb_id
      hmdb_url
      img_abc_id
      inchi
      inchi_key
      iupac_name
      kegg_id
      kegg_url
      last_modified_Compounds
      lipidmaps_id
      lipidmaps_url
      metacyc_id
      mono_isotopic_molecular_weight
      name_Compounds
      neutralized_2d_inchi
      neutralized_2d_inchi_key
      neutralized_inchi
      neutralized_inchi_key
      num_free_radicals
      number_components
      permanent_charge
      pubchem_compound_id
      pubchem_url
      source
      synonyms
      username_Compounds
      wikipedia_url
      collision_energy
      creation_time_CompoundIdentifications
      description_FragmentationReferences
      enabled_CompoundIdentifications
      last_modified_CompoundIdentifications
      lcms_run_FragmentationReferences
      mz_intensities
      name_CompoundIdentifications
      polarity
      precursor_mz
      ref_type_CompoundIdentifications
      technique
      username_CompoundIdentifications
      adduct
      creation_time_MzReferences
      description_CompoundIdentifications
      detected_polarity
      enabled_MzReferences
      last_modified_MzReferences
      lcms_run_MzReferences
      modification
      mz
      mz_tolerance
      mz_tolerance_units
      name_MzReferences
      observed_formula
      ref_type_MzReferences
      username_MzReferences
      creation_time
      description_RtReferences
      enabled
      last_modified
      lcms_run_RtReferences
      name
      ref_type
      rt_max
      rt_min
      rt_peak
      rt_units
      username
    

  
  
    

      0
      2016-06-22 17:38:46
      2016-06-22 17:38:51
      uridine
      bpb
      [(50.6455, 1739.9), (55.0192, 2034.74), (55.11...
      1975b25843974aa59bf40ab31c9c1dd2
      794ca716103842f8b9fcca530796f20a
      2e31392ff6964a5daff2f599fa261ce0
      2e9fc66669094ab296f5aa761ea54f5e
      CHEBI:16704
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:00:42
      A ribonucleoside composed of a molecule of ura...
      C9H12N2O6
      HMDB00296
      http://www.hmdb.ca/metabolites/HMDB00296
      
      InChI=1S/C9H12N2O6/c12-3-4-6(14)7(15)8(17-4)11...
      DRTQHJPVMGBUCF-XVFCMESISA-N
      
      C00299
      http://www.genome.jp/dbget-bin/www_bget?C00299
      2016-06-18 01:00:42
      
      
      URIDINE
      244.069536
      uridine
      InChI=1S/C9H12N2O6/c12-3-4-6(14)7(15)8(17-4)11...
      DRTQHJPVMGBUCF-UHFFFAOYSA-N
      InChI=1S/C9H12N2O6/c12-3-4-6(14)7(15)8(17-4)11...
      DRTQHJPVMGBUCF-XVFCMESISA-N
      0
      1
      0
      6029
      http://pubchem.ncbi.nlm.nih.gov/compound/6029
      chebi///wikidata///metacyc///hmdb
      uridine///Uridin///58-96-8///1-beta-D-Ribofura...
      bpb
      
      0.0
      2016-06-22 17:38:47
      No description
      True
      2016-06-22 17:38:51
      bf1e2add32ec483aaf1e5fcdf449a033
      [u'8dc2b871f99b40c5a988946842f6eb9c', u'b649cd...
      Untitled
      positive
      245.077
      
      cid
      bpb
      
      2016-06-22 17:38:47
      No description
      positive
      True
      2016-06-22 17:38:51
      bf1e2add32ec483aaf1e5fcdf449a033
      
      245.076800
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:38:47
      No description
      True
      2016-06-22 17:38:51
      bf1e2add32ec483aaf1e5fcdf449a033
      Untitled
      
      5.35
      4.75
      5.05
      min
      bpb
    

    

      1
      2016-06-22 17:38:31
      2016-06-22 17:38:51
      inosine
      bpb
      [(52.2652, 3482.39), (53.7242, 3295.81), (57.0...
      32b6b4e0b26140b5990ce37a5bbed6df
      b9f41ab592d04fc594956efd33e79ba2
      770548129e2341f38fb26be6835c7e11
      980eda3f864d46559c23d7841a51dcf1
      CHEBI:17596
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:19:41
      A purine nucleoside in which hypoxanthine is a...
      C10H12N4O5
      HMDB00195
      http://www.hmdb.ca/metabolites/HMDB00195
      
      InChI=1S/C10H12N4O5/c15-1-4-6(16)7(17)10(19-4)...
      UGQMRVRMYYASKQ-KQYNXXCUSA-N
      
      C00294
      http://www.genome.jp/dbget-bin/www_bget?C00294
      2016-06-18 01:19:41
      
      
      INOSINE
      268.080769
      inosine
      InChI=1S/C10H12N4O5/c15-1-4-6(16)7(17)10(19-4)...
      UGQMRVRMYYASKQ-UHFFFAOYSA-N
      InChI=1S/C10H12N4O5/c15-1-4-6(16)7(17)10(19-4)...
      UGQMRVRMYYASKQ-KQYNXXCUSA-N
      0
      1
      0
      6021
      http://pubchem.ncbi.nlm.nih.gov/compound/6021
      chebi///wikidata///metacyc///hmdb
      inosine///58-63-9///Hypoxanthosine///Atorel///...
      bpb
      
      0.0
      2016-06-22 17:38:32
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      [u'3071f0912d4d42a99c059de0a74892eb', u'194c99...
      Untitled
      positive
      269.088
      
      cid
      bpb
      
      2016-06-22 17:38:32
      No description
      positive
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      
      269.088056
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:38:32
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      Untitled
      
      7.14
      6.54
      6.84
      min
      bpb
    

    

      2
      2016-06-22 17:38:04
      2016-06-22 17:38:51
      arginine
      bpb
      [(60.0568, 2757480.0), (69.6344, 16756.1), (70...
      3a72f4d1704c419c8466710fe6b8eb04
      530570f2eb514d559cad1c11727e19dd
      46ad307654dc4004b8ed524196afcc71
      c5b8e49ebdab477db6b77b6e912e1112
      CHEBI:32690///CHEBI:32689///CHEBI:16467///CHEB...
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:12:49
      The D-enantiomer of argininium(1+).///An L-alp...
      C6H14N4O2
      HMDB00517
      http://www.hmdb.ca/metabolites/HMDB00517
      65521
      InChI=1S/C6H14N4O2/c7-4(5(11)12)2-1-3-10-6(8)9...
      ODKSFYDXXFIFQN-BYPYZUCNSA-N
      
      C00062
      http://www.genome.jp/dbget-bin/www_bget?C00062
      2016-06-18 01:12:49
      
      
      ARG
      174.111676
      L-arginine
      InChI=1S/C6H14N4O2/c7-4(5(11)12)2-1-3-10-6(8)9...
      ODKSFYDXXFIFQN-UHFFFAOYSA-N
      InChI=1S/C6H14N4O2/c7-4(5(11)12)2-1-3-10-6(8)9...
      ODKSFYDXXFIFQN-BYPYZUCNSA-N
      0
      1
      0
      6322
      http://pubchem.ncbi.nlm.nih.gov/compound/6322
      metacyc///img///msmls///wikidata///hmdb///chebi
      L-arginine///arginine///74-79-3///L-(+)-Argini...
      bpb
      
      0.0
      2016-06-22 17:38:05
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      [u'5a8959125b4b4e0482d48b88cee87788', u'3e9dbe...
      Untitled
      positive
      175.119
      
      cid
      bpb
      
      2016-06-22 17:38:05
      No description
      positive
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      
      175.118955
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:38:05
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      Untitled
      
      23.89
      20.89
      22.39
      min
      bpb
    

    

      3
      2016-06-22 17:37:35
      2016-06-22 17:38:51
      valine
      bpb
      [(53.1711, 2246.35), (53.2227, 2249.81), (53.8...
      f6d49e7aa4b44b42beea61c7bac1d933
      0d099fe4d7bc4176b17441dda13d1bc6
      7d121317ca984972848680f944717239
      b92fe3653ae14b4e9fc01be0fbfbdb11
      CHEBI:16414///CHEBI:32856///CHEBI:74338///CHEB...
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:08:48
      A D-alpha-amino acid zwitterion that is D-vali...
      C5H11NO2
      HMDB00883
      http://www.hmdb.ca/metabolites/HMDB00883
      
      InChI=1S/C5H11NO2/c1-3(2)4(6)5(7)8/h3-4H,6H2,1...
      KZSNJWFQEVHDMF-BYPYZUCNSA-N
      
      C00183
      http://www.genome.jp/dbget-bin/www_bget?C00183
      2016-06-18 01:08:48
      
      
      VAL
      117.078979
      L-valine
      InChI=1S/C5H11NO2/c1-3(2)4(6)5(7)8/h3-4H,6H2,1...
      KZSNJWFQEVHDMF-UHFFFAOYSA-N
      InChI=1S/C5H11NO2/c1-3(2)4(6)5(7)8/h3-4H,6H2,1...
      KZSNJWFQEVHDMF-BYPYZUCNSA-N
      0
      1
      0
      6287
      http://pubchem.ncbi.nlm.nih.gov/compound/6287
      chebi///wikidata///metacyc///hmdb
      L-valine///valine///72-18-4///(S)-Valine///(S)...
      bpb
      
      0.0
      2016-06-22 17:37:36
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      [u'e4bff2d52a984b47a585f884511b7d67', u'068042...
      Untitled
      positive
      118.086
      
      cid
      bpb
      
      2016-06-22 17:37:36
      No description
      positive
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      
      118.086255
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:37:36
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      Untitled
      
      9.27
      8.67
      8.97
      min
      bpb
    

    

      4
      2016-06-22 17:37:39
      2016-06-22 17:38:51
      abscisic acid (peak 1)
      bpb
      [(50.8347, 1522.24), (65.757, 1756.25), (73.02...
      38bc6bd85fd94d0991270ad5faf250b7
      cc3ec8c6210c4004b78dfbd6c3def72d
      92209a343c564e559e91a5bf71dc579f
      8bde7acd55c64df0b466bacd3876fd6a
      CHEBI:18743///CHEBI:62421
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:06:47
      A 2-trans-abscisate obtained by removal of a p...
      C15H20O4
      HMDB35140
      http://www.hmdb.ca/metabolites/HMDB35140
      
      InChI=1S/C15H20O4/c1-10(7-13(17)18)5-6-15(19)1...
      JLIDBLDQVAYHNE-IBPUIESWSA-N
      
      
      
      2016-06-18 01:06:47
      LMPR0103050001
      http://www.lipidmaps.org/data/LMSDRecord.php?L...
      CPD-7731
      264.136159
      2-trans-abscisic acid
      InChI=1S/C15H20O4/c1-10(7-13(17)18)5-6-15(19)1...
      JLIDBLDQVAYHNE-UHFFFAOYSA-N
      InChI=1S/C15H20O4/c1-10(7-13(17)18)5-6-15(19)1...
      JLIDBLDQVAYHNE-IBPUIESWSA-N
      0
      1
      0
      5702609
      http://pubchem.ncbi.nlm.nih.gov/compound/5702609
      chebi///lipidmaps///metacyc///hmdb
      2-trans-abscisic acid///(2E,4E)-5-[(1S)-1-hydr...
      bpb
      
      0.0
      2016-06-22 17:37:40
      No description
      True
      2016-06-22 17:38:51
      bae92b589f544f68a915ef95b92ca71f
      [u'9f4c58b9135d4a10a9564a41172e4474', u'b12e6e...
      Untitled
      positive
      265.143
      
      cid
      bpb
      
      2016-06-22 17:37:40
      No description
      positive
      True
      2016-06-22 17:38:51
      bae92b589f544f68a915ef95b92ca71f
      
      265.143500
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:37:40
      No description
      True
      2016-06-22 17:38:51
      bae92b589f544f68a915ef95b92ca71f
      Untitled
      
      1.58
      0.98
      1.28
      min
      bpb
    

  




















In [8]:



    


[c for c in temp_df.columns if 'lcms' in c]



    


















    
Out[8]:








['lcms_run_FragmentationReferences',
 'lcms_run_MzReferences',
 'lcms_run_RtReferences']

















In [9]:



    


#Get name, experiment, and method
for i,row in temp_df.iterrows():
    for file_ref in [c for c in temp_df.columns if 'lcms' in c]:
    #     name,experiment,mzml_file
        if not pd.isnull(row[file_ref]):
            f = metob.retrieve('Lcmsruns',unique_id = row[file_ref], username = '*')
            if f:
                temp_df.loc[i,file_ref.split('_')[-1]+'_name'] = f[0].name
                temp_df.loc[i,file_ref.split('_')[-1]+'_experiment'] = f[0].experiment
                temp_df.loc[i,file_ref.split('_')[-1]+'_mzml_file'] = f[0].mzml_file
                temp_df.loc[i,file_ref.split('_')[-1]+'_method'] = f[0].method
                
    #             files['experiment'].append(f[0].experiment)
    #             files['mzml_file'].append(f[0].mzml_file)
    #         else:
    #             files['name'].append('')
    #             files['experiment'].append('')
    #             files['mzml_file'].append('')
temp_df.head()



    


















    
Out[9]:










  
    

      
      creation_time_CompoundIdentifications
      last_modified_CompoundIdentifications
      name_CompoundIdentifications
      username_CompoundIdentifications
      fragmentation_spectra
      unique_id_Compounds
      unique_id_FragmentationReferences
      unique_id_MzReferences
      unique_id_RtReferences
      chebi_id
      chebi_url
      creation_time_Compounds
      description_CompoundIdentifications
      formula
      hmdb_id
      hmdb_url
      img_abc_id
      inchi
      inchi_key
      iupac_name
      kegg_id
      kegg_url
      last_modified_Compounds
      lipidmaps_id
      lipidmaps_url
      metacyc_id
      mono_isotopic_molecular_weight
      name_Compounds
      neutralized_2d_inchi
      neutralized_2d_inchi_key
      neutralized_inchi
      neutralized_inchi_key
      num_free_radicals
      number_components
      permanent_charge
      pubchem_compound_id
      pubchem_url
      source
      synonyms
      username_Compounds
      wikipedia_url
      collision_energy
      creation_time_CompoundIdentifications
      description_FragmentationReferences
      enabled_CompoundIdentifications
      last_modified_CompoundIdentifications
      lcms_run_FragmentationReferences
      mz_intensities
      name_CompoundIdentifications
      polarity
      precursor_mz
      ref_type_CompoundIdentifications
      technique
      username_CompoundIdentifications
      adduct
      creation_time_MzReferences
      description_CompoundIdentifications
      detected_polarity
      enabled_MzReferences
      last_modified_MzReferences
      lcms_run_MzReferences
      modification
      mz
      mz_tolerance
      mz_tolerance_units
      name_MzReferences
      observed_formula
      ref_type_MzReferences
      username_MzReferences
      creation_time
      description_RtReferences
      enabled
      last_modified
      lcms_run_RtReferences
      name
      ref_type
      rt_max
      rt_min
      rt_peak
      rt_units
      username
      FragmentationReferences_name
      FragmentationReferences_experiment
      FragmentationReferences_mzml_file
      FragmentationReferences_method
      MzReferences_name
      MzReferences_experiment
      MzReferences_mzml_file
      MzReferences_method
      RtReferences_name
      RtReferences_experiment
      RtReferences_mzml_file
      RtReferences_method
    

  
  
    

      0
      2016-06-22 17:38:46
      2016-06-22 17:38:51
      uridine
      bpb
      [(50.6455, 1739.9), (55.0192, 2034.74), (55.11...
      1975b25843974aa59bf40ab31c9c1dd2
      794ca716103842f8b9fcca530796f20a
      2e31392ff6964a5daff2f599fa261ce0
      2e9fc66669094ab296f5aa761ea54f5e
      CHEBI:16704
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:00:42
      A ribonucleoside composed of a molecule of ura...
      C9H12N2O6
      HMDB00296
      http://www.hmdb.ca/metabolites/HMDB00296
      
      InChI=1S/C9H12N2O6/c12-3-4-6(14)7(15)8(17-4)11...
      DRTQHJPVMGBUCF-XVFCMESISA-N
      
      C00299
      http://www.genome.jp/dbget-bin/www_bget?C00299
      2016-06-18 01:00:42
      
      
      URIDINE
      244.069536
      uridine
      InChI=1S/C9H12N2O6/c12-3-4-6(14)7(15)8(17-4)11...
      DRTQHJPVMGBUCF-UHFFFAOYSA-N
      InChI=1S/C9H12N2O6/c12-3-4-6(14)7(15)8(17-4)11...
      DRTQHJPVMGBUCF-XVFCMESISA-N
      0
      1
      0
      6029
      http://pubchem.ncbi.nlm.nih.gov/compound/6029
      chebi///wikidata///metacyc///hmdb
      uridine///Uridin///58-96-8///1-beta-D-Ribofura...
      bpb
      
      0.0
      2016-06-22 17:38:47
      No description
      True
      2016-06-22 17:38:51
      bf1e2add32ec483aaf1e5fcdf449a033
      [u'8dc2b871f99b40c5a988946842f6eb9c', u'b649cd...
      Untitled
      positive
      245.077
      
      cid
      bpb
      
      2016-06-22 17:38:47
      No description
      positive
      True
      2016-06-22 17:38:51
      bf1e2add32ec483aaf1e5fcdf449a033
      
      245.076800
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:38:47
      No description
      True
      2016-06-22 17:38:51
      bf1e2add32ec483aaf1e5fcdf449a033
      Untitled
      
      5.35
      4.75
      5.05
      min
      bpb
      20160119_pHILIC___POS_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

    

      1
      2016-06-22 17:38:31
      2016-06-22 17:38:51
      inosine
      bpb
      [(52.2652, 3482.39), (53.7242, 3295.81), (57.0...
      32b6b4e0b26140b5990ce37a5bbed6df
      b9f41ab592d04fc594956efd33e79ba2
      770548129e2341f38fb26be6835c7e11
      980eda3f864d46559c23d7841a51dcf1
      CHEBI:17596
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:19:41
      A purine nucleoside in which hypoxanthine is a...
      C10H12N4O5
      HMDB00195
      http://www.hmdb.ca/metabolites/HMDB00195
      
      InChI=1S/C10H12N4O5/c15-1-4-6(16)7(17)10(19-4)...
      UGQMRVRMYYASKQ-KQYNXXCUSA-N
      
      C00294
      http://www.genome.jp/dbget-bin/www_bget?C00294
      2016-06-18 01:19:41
      
      
      INOSINE
      268.080769
      inosine
      InChI=1S/C10H12N4O5/c15-1-4-6(16)7(17)10(19-4)...
      UGQMRVRMYYASKQ-UHFFFAOYSA-N
      InChI=1S/C10H12N4O5/c15-1-4-6(16)7(17)10(19-4)...
      UGQMRVRMYYASKQ-KQYNXXCUSA-N
      0
      1
      0
      6021
      http://pubchem.ncbi.nlm.nih.gov/compound/6021
      chebi///wikidata///metacyc///hmdb
      inosine///58-63-9///Hypoxanthosine///Atorel///...
      bpb
      
      0.0
      2016-06-22 17:38:32
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      [u'3071f0912d4d42a99c059de0a74892eb', u'194c99...
      Untitled
      positive
      269.088
      
      cid
      bpb
      
      2016-06-22 17:38:32
      No description
      positive
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      
      269.088056
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:38:32
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      Untitled
      
      7.14
      6.54
      6.84
      min
      bpb
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

    

      2
      2016-06-22 17:38:04
      2016-06-22 17:38:51
      arginine
      bpb
      [(60.0568, 2757480.0), (69.6344, 16756.1), (70...
      3a72f4d1704c419c8466710fe6b8eb04
      530570f2eb514d559cad1c11727e19dd
      46ad307654dc4004b8ed524196afcc71
      c5b8e49ebdab477db6b77b6e912e1112
      CHEBI:32690///CHEBI:32689///CHEBI:16467///CHEB...
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:12:49
      The D-enantiomer of argininium(1+).///An L-alp...
      C6H14N4O2
      HMDB00517
      http://www.hmdb.ca/metabolites/HMDB00517
      65521
      InChI=1S/C6H14N4O2/c7-4(5(11)12)2-1-3-10-6(8)9...
      ODKSFYDXXFIFQN-BYPYZUCNSA-N
      
      C00062
      http://www.genome.jp/dbget-bin/www_bget?C00062
      2016-06-18 01:12:49
      
      
      ARG
      174.111676
      L-arginine
      InChI=1S/C6H14N4O2/c7-4(5(11)12)2-1-3-10-6(8)9...
      ODKSFYDXXFIFQN-UHFFFAOYSA-N
      InChI=1S/C6H14N4O2/c7-4(5(11)12)2-1-3-10-6(8)9...
      ODKSFYDXXFIFQN-BYPYZUCNSA-N
      0
      1
      0
      6322
      http://pubchem.ncbi.nlm.nih.gov/compound/6322
      metacyc///img///msmls///wikidata///hmdb///chebi
      L-arginine///arginine///74-79-3///L-(+)-Argini...
      bpb
      
      0.0
      2016-06-22 17:38:05
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      [u'5a8959125b4b4e0482d48b88cee87788', u'3e9dbe...
      Untitled
      positive
      175.119
      
      cid
      bpb
      
      2016-06-22 17:38:05
      No description
      positive
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      
      175.118955
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:38:05
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      Untitled
      
      23.89
      20.89
      22.39
      min
      bpb
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

    

      3
      2016-06-22 17:37:35
      2016-06-22 17:38:51
      valine
      bpb
      [(53.1711, 2246.35), (53.2227, 2249.81), (53.8...
      f6d49e7aa4b44b42beea61c7bac1d933
      0d099fe4d7bc4176b17441dda13d1bc6
      7d121317ca984972848680f944717239
      b92fe3653ae14b4e9fc01be0fbfbdb11
      CHEBI:16414///CHEBI:32856///CHEBI:74338///CHEB...
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:08:48
      A D-alpha-amino acid zwitterion that is D-vali...
      C5H11NO2
      HMDB00883
      http://www.hmdb.ca/metabolites/HMDB00883
      
      InChI=1S/C5H11NO2/c1-3(2)4(6)5(7)8/h3-4H,6H2,1...
      KZSNJWFQEVHDMF-BYPYZUCNSA-N
      
      C00183
      http://www.genome.jp/dbget-bin/www_bget?C00183
      2016-06-18 01:08:48
      
      
      VAL
      117.078979
      L-valine
      InChI=1S/C5H11NO2/c1-3(2)4(6)5(7)8/h3-4H,6H2,1...
      KZSNJWFQEVHDMF-UHFFFAOYSA-N
      InChI=1S/C5H11NO2/c1-3(2)4(6)5(7)8/h3-4H,6H2,1...
      KZSNJWFQEVHDMF-BYPYZUCNSA-N
      0
      1
      0
      6287
      http://pubchem.ncbi.nlm.nih.gov/compound/6287
      chebi///wikidata///metacyc///hmdb
      L-valine///valine///72-18-4///(S)-Valine///(S)...
      bpb
      
      0.0
      2016-06-22 17:37:36
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      [u'e4bff2d52a984b47a585f884511b7d67', u'068042...
      Untitled
      positive
      118.086
      
      cid
      bpb
      
      2016-06-22 17:37:36
      No description
      positive
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      
      118.086255
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:37:36
      No description
      True
      2016-06-22 17:38:51
      0697d7b610f6420a8472201ce542bd16
      Untitled
      
      9.27
      8.67
      8.97
      min
      bpb
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ__KBL_QCMix_V2___...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

    

      4
      2016-06-22 17:37:39
      2016-06-22 17:38:51
      abscisic acid (peak 1)
      bpb
      [(50.8347, 1522.24), (65.757, 1756.25), (73.02...
      38bc6bd85fd94d0991270ad5faf250b7
      cc3ec8c6210c4004b78dfbd6c3def72d
      92209a343c564e559e91a5bf71dc579f
      8bde7acd55c64df0b466bacd3876fd6a
      CHEBI:18743///CHEBI:62421
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:06:47
      A 2-trans-abscisate obtained by removal of a p...
      C15H20O4
      HMDB35140
      http://www.hmdb.ca/metabolites/HMDB35140
      
      InChI=1S/C15H20O4/c1-10(7-13(17)18)5-6-15(19)1...
      JLIDBLDQVAYHNE-IBPUIESWSA-N
      
      
      
      2016-06-18 01:06:47
      LMPR0103050001
      http://www.lipidmaps.org/data/LMSDRecord.php?L...
      CPD-7731
      264.136159
      2-trans-abscisic acid
      InChI=1S/C15H20O4/c1-10(7-13(17)18)5-6-15(19)1...
      JLIDBLDQVAYHNE-UHFFFAOYSA-N
      InChI=1S/C15H20O4/c1-10(7-13(17)18)5-6-15(19)1...
      JLIDBLDQVAYHNE-IBPUIESWSA-N
      0
      1
      0
      5702609
      http://pubchem.ncbi.nlm.nih.gov/compound/5702609
      chebi///lipidmaps///metacyc///hmdb
      2-trans-abscisic acid///(2E,4E)-5-[(1S)-1-hydr...
      bpb
      
      0.0
      2016-06-22 17:37:40
      No description
      True
      2016-06-22 17:38:51
      bae92b589f544f68a915ef95b92ca71f
      [u'9f4c58b9135d4a10a9564a41172e4474', u'b12e6e...
      Untitled
      positive
      265.143
      
      cid
      bpb
      
      2016-06-22 17:37:40
      No description
      positive
      True
      2016-06-22 17:38:51
      bae92b589f544f68a915ef95b92ca71f
      
      265.143500
      10.0
      ppm
      Untitled
      
      
      bpb
      2016-06-22 17:37:40
      No description
      True
      2016-06-22 17:38:51
      bae92b589f544f68a915ef95b92ca71f
      Untitled
      
      1.58
      0.98
      1.28
      min
      bpb
      20160119_pHILIC___POS_MSMS_KZ_RootCass_8_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ_RootCass_8_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___POS_MSMS_KZ_RootCass_8_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

  




















In [14]:



    


temp_df[temp_df.name_Compounds.str.contains('Salicylate')]



    


















    
Out[14]:










  
    

      
      creation_time_CompoundIdentifications
      last_modified_CompoundIdentifications
      name_CompoundIdentifications
      username_CompoundIdentifications
      fragmentation_spectra
      unique_id_Compounds
      unique_id_FragmentationReferences
      unique_id_MzReferences
      unique_id_RtReferences
      chebi_id
      chebi_url
      creation_time_Compounds
      description_CompoundIdentifications
      formula
      hmdb_id
      hmdb_url
      img_abc_id
      inchi
      inchi_key
      iupac_name
      kegg_id
      kegg_url
      last_modified_Compounds
      lipidmaps_id
      lipidmaps_url
      metacyc_id
      mono_isotopic_molecular_weight
      name_Compounds
      neutralized_2d_inchi
      neutralized_2d_inchi_key
      neutralized_inchi
      neutralized_inchi_key
      num_free_radicals
      number_components
      permanent_charge
      pubchem_compound_id
      pubchem_url
      source
      synonyms
      username_Compounds
      wikipedia_url
      collision_energy
      creation_time_CompoundIdentifications
      description_FragmentationReferences
      enabled_CompoundIdentifications
      last_modified_CompoundIdentifications
      lcms_run_FragmentationReferences
      mz_intensities
      name_CompoundIdentifications
      polarity
      precursor_mz
      ref_type_CompoundIdentifications
      technique
      username_CompoundIdentifications
      adduct
      creation_time_MzReferences
      description_CompoundIdentifications
      detected_polarity
      enabled_MzReferences
      last_modified_MzReferences
      lcms_run_MzReferences
      modification
      mz
      mz_tolerance
      mz_tolerance_units
      name_MzReferences
      observed_formula
      ref_type_MzReferences
      username_MzReferences
      creation_time
      description_RtReferences
      enabled
      last_modified
      lcms_run_RtReferences
      name
      ref_type
      rt_max
      rt_min
      rt_peak
      rt_units
      username
      FragmentationReferences_name
      FragmentationReferences_experiment
      FragmentationReferences_mzml_file
      FragmentationReferences_method
      MzReferences_name
      MzReferences_experiment
      MzReferences_mzml_file
      MzReferences_method
      RtReferences_name
      RtReferences_experiment
      RtReferences_mzml_file
      RtReferences_method
    

  
  
    

      117
      2016-07-08 21:20:29
      2016-07-08 21:20:48
      salicylic acid
      pandeer
      [(51.9239, 41705.2), (59.4143, 53233.9), (65.0...
      d84938cb6b804b3e98eb71a26a29d9c7
      99c67acfc0964724906f43782942cd87
      80e5d28ce2754726bc4f45d20c5ca142
      2ffde93b7f724b34ba410b80441958e9
      CHEBI:30762///CHEBI:16914///CHEBI:9180
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:24:43
      A monohydroxybenzoic acid that is benzoic acid...
      C7H6O3
      HMDB15473///HMDB15470///HMDB01895///HMDB15469
      http://www.hmdb.ca/metabolites/HMDB15473///htt...
      73015///58194
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      
      C07587///C00805
      http://www.genome.jp/dbget-bin/www_bget?C07587...
      2016-07-08 21:20:48
      
      
      CPD-110
      138.031694
      Salicylate///Bismuth Subsalicylate///Salicylat...
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      0
      1
      0
      54675850///338
      http://pubchem.ncbi.nlm.nih.gov/compound/54675...
      metacyc///img///msmls///wikidata///hmdb///gnps...
      salicylate///salicylic acid///o-hydroxybenzoic...
      pandeer
      
      0.0
      2016-07-08 21:20:30
      No description
      True
      2016-07-08 21:20:48
      29b2702a49284062b6714e7d37d267b3
      [u'eb0376233d1949d8b232497812827666', u'7c7410...
      Untitled
      negative
      137.024
      
      cid
      pandeer
      
      2016-07-08 21:20:30
      No description
      negative
      True
      2016-07-08 21:20:48
      29b2702a49284062b6714e7d37d267b3
      
      137.0244
      10.0
      ppm
      Untitled
      
      
      pandeer
      2016-07-08 21:20:30
      No description
      True
      2016-07-08 21:20:48
      29b2702a49284062b6714e7d37d267b3
      Untitled
      
      2.77
      2.0
      2.47
      min
      pandeer
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

    

      132
      2016-07-08 21:20:30
      2016-07-08 21:20:49
      salicylic acid
      rlau
      [(51.9239, 41705.2), (59.4143, 53233.9), (65.0...
      8963452c77bb42b9983951c2e29e1329
      41d59c6e8e824d01bc48dc35cd986dd9
      d41e2c0e53e74c04a2c36bf5f9c43182
      bb13340395da45888ef9dd48357e922e
      CHEBI:30762///CHEBI:16914///CHEBI:9180
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:24:43
      A monohydroxybenzoic acid that is benzoic acid...
      C7H6O3
      HMDB15473///HMDB15470///HMDB01895///HMDB15469
      http://www.hmdb.ca/metabolites/HMDB15473///htt...
      73015///58194
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      
      C07587///C00805
      http://www.genome.jp/dbget-bin/www_bget?C07587...
      2016-07-08 21:20:49
      
      
      CPD-110
      138.031694
      Salicylate///Bismuth Subsalicylate///Salicylat...
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      0
      1
      0
      54675850///338
      http://pubchem.ncbi.nlm.nih.gov/compound/54675...
      metacyc///img///msmls///wikidata///hmdb///gnps...
      salicylate///salicylic acid///o-hydroxybenzoic...
      rlau
      
      0.0
      2016-07-08 21:20:31
      No description
      True
      2016-07-08 21:20:49
      29b2702a49284062b6714e7d37d267b3
      [u'9f716ccda1cf4a64b9f8430060b27a6c', u'1a6f79...
      Untitled
      negative
      137.024
      
      cid
      rlau
      
      2016-07-08 21:20:31
      No description
      negative
      True
      2016-07-08 21:20:49
      29b2702a49284062b6714e7d37d267b3
      
      137.0244
      10.0
      ppm
      Untitled
      
      
      rlau
      2016-07-08 21:20:31
      No description
      True
      2016-07-08 21:20:49
      29b2702a49284062b6714e7d37d267b3
      Untitled
      
      2.77
      2.0
      2.47
      min
      rlau
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

    

      221
      2016-07-08 21:20:31
      2016-07-08 21:20:50
      salicylic acid
      lpsilva
      [(51.9239, 41705.2), (59.4143, 53233.9), (65.0...
      ab03f53a7b5e4886874aa0bfe170084c
      5e742e9d94724fe181372448a22d24e8
      881a5a7d6943431ea3ca9ccce4184ce7
      3fd7d06d3dda4c0397c9e4cf5b48d576
      CHEBI:30762///CHEBI:16914///CHEBI:9180
      http://www.ebi.ac.uk/chebi/searchId.do?chebiId...
      2016-06-18 01:24:43
      A monohydroxybenzoic acid that is benzoic acid...
      C7H6O3
      HMDB15473///HMDB15470///HMDB01895///HMDB15469
      http://www.hmdb.ca/metabolites/HMDB15473///htt...
      73015///58194
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      
      C07587///C00805
      http://www.genome.jp/dbget-bin/www_bget?C07587...
      2016-07-08 21:20:50
      
      
      CPD-110
      138.031694
      Salicylate///Bismuth Subsalicylate///Salicylat...
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      InChI=1S/C7H6O3/c8-6-4-2-1-3-5(6)7(9)10/h1-4,8...
      YGSDEFSMJLZEOE-UHFFFAOYSA-N
      0
      1
      0
      54675850///338
      http://pubchem.ncbi.nlm.nih.gov/compound/54675...
      metacyc///img///msmls///wikidata///hmdb///gnps...
      salicylate///salicylic acid///o-hydroxybenzoic...
      lpsilva
      
      0.0
      2016-07-08 21:20:32
      No description
      True
      2016-07-08 21:20:50
      29b2702a49284062b6714e7d37d267b3
      [u'da2337be1c65491781c9737429475eed', u'919649...
      Untitled
      negative
      137.024
      
      cid
      lpsilva
      
      2016-07-08 21:20:32
      No description
      negative
      True
      2016-07-08 21:20:50
      29b2702a49284062b6714e7d37d267b3
      
      137.0244
      10.0
      ppm
      Untitled
      
      
      lpsilva
      2016-07-08 21:20:32
      No description
      True
      2016-07-08 21:20:50
      29b2702a49284062b6714e7d37d267b3
      Untitled
      
      2.77
      2.0
      2.47
      min
      lpsilva
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
      20160119_pHILIC___NEG_MSMS_KZ_RootCass_1_MeOH_...
      20160119_KZ_HILIC_Avena_exu_uptake
      /global/project/projectdirs/metatlas/raw_data/...
      NaN
    

  




















In [17]:



    


temp_df.to_csv('/global/homes/b/bpb/Downloads/references_out.tab',sep='\t')



    











In [ ]:



    


# remove_list = ['ISAKRJDGNUQOIC-UHFFFAOYSA-N']



    











In [29]:



    


terms = ['rt_peak_6550_ZIC-HILIC',
'rt_min_6550_ZIC-HILIC',
'rt_max_6550_ZIC-HILIC',
'rt_peak_QE139_ZIC-HILIC',
'rt_min_QE139_ZIC-HILIC',
'rt_max_QE139_ZIC-HILIC',
'rt_peak_QE144_ZIC-HILIC',
'rt_min_QE144_ZIC-HILIC',
'rt_max_QE144_ZIC-HILIC',
'rt_peak_QE119_ZIC-HILIC',
'rt_min_QE119_ZIC-HILIC',
'rt_max_QE119_ZIC-HILIC',
'rt_peak_6550_pHILIC',
'rt_min_6550_pHILIC',
'rt_max_6550_pHILIC',
'rt_peak_6550_pHILIC-noguard',
'rt_peak_QE139_pHILIC',
'rt_min_QE139_pHILIC',
'rt_max_QE139_pHILIC',
'RT_6550_RP',
'RT_QE139_RP',
        'RT_QE144_RP',
        'RT_QE119_RP']

sorted(list(set(['_'.join(t.split('_')[-2:])+'_0.5min_25ppm_1000counts' for t in terms])))



    


















    
Out[29]:








['6550_RP_0.5min_25ppm_1000counts',
 '6550_ZIC-HILIC_0.5min_25ppm_1000counts',
 '6550_pHILIC-noguard_0.5min_25ppm_1000counts',
 '6550_pHILIC_0.5min_25ppm_1000counts',
 'QE119_RP_0.5min_25ppm_1000counts',
 'QE119_ZIC-HILIC_0.5min_25ppm_1000counts',
 'QE139_RP_0.5min_25ppm_1000counts',
 'QE139_ZIC-HILIC_0.5min_25ppm_1000counts',
 'QE139_pHILIC_0.5min_25ppm_1000counts',
 'QE144_RP_0.5min_25ppm_1000counts',
 'QE144_ZIC-HILIC_0.5min_25ppm_1000counts']

















In [ ]:



    


to_remove = []
for c in cids:
    if c.frag_references:
        if c.compound:
            if c.compound[0].inchi_key in remove_list:
                to_remove.append(f)
                print c.unique_id,c.compound[0].inchi_key,c.username
#                 metob.remove('CompoundIdentifications',unique_id = c.unique_id,username = c.username,_override = True)



    











In [ ]:



    


# metob.remove('CompoundIdentifications',unique_id = '3f82a1e296ca4d1cb61b7ac6cf84f6d3',username = 'rlau',_override = True)



    











In [ ]:



    


len(to_remove)



    











In [ ]:



    


# query = 'SELECT DISTINCT experiment,creation_time,username FROM lcmsruns where creation_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL %d DAY))'%num_days
query = 'SELECT * from compoundidentifications_frag_references where unique_id is 9932583619ed4d92beba17a50f32bd40'#  experiment,creation_time,username FROM lcmsruns where creation_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL %d DAY))'%num_days

entries = [e for e in metob.database.query(query)]
len(entries)



    











In [ ]:



    


fr = metob.retrieve('FragmentationReferences',username='*')



    











In [ ]:



    


mz = []
for f in fr:
    mz.append(f.precursor_mz)
for s in sorted(list(set(mz))):
    print '%5.6f'%s



    











In [ ]:



    


cid = metob.retrieve('CompoundIdentifications',username = '*')



    











In [ ]:



    


dir(cid[0])



    











In [ ]:



    


for c in cid:
    if c.frag_references:
        if abs(c.frag_references[0].precursor_mz - 128.035324) < 0.01:
            print c.name
            print c.compound
            c.compound = metob.retrieve('Compounds',inchi_key = 'ODHCTXKNWHHXJC-VKHMYHEASA-N')
            metob.store(c)



    











In [ ]:



    


compounds = metob.retrieve('Compounds',synonyms='%pyroglutamic%')
for c in compounds:
    print c.name
    print c.inchi_key
    print ''



    











In [ ]:

Content source: metabolite-atlas/metatlas

Similar notebooks:

notebook.community | gallery | about