<jupyter_start><jupyter_text>Sharvani was needing a table of biomarkers with stock ids. This is quick and dirty because we are on a time crunch.<jupyter_code>import os
from pathlib import Path
import pandas as pd
# load stocks data and split into bloomington and janelia
stocks = pd.read_csv('../../data/external/bloomington_gal4.tsv', 
                     comment='#', sep='\t', usecols=['stk_id', 'gene_symbol', 'symbol'])

janelia = stocks[stocks.symbol.str.contains('GMR')]
bloomington = stocks[~stocks.symbol.str.contains('GMR')]
# collapse stock numbers to a list
def collapse_stocks(df, name='stocks'):
    stocks = df.stk_id.astype(int).tolist()
    return pd.Series([
        stocks
    ], [name]) 

bs = bloomington.groupby('gene_symbol').apply(lambda x: collapse_stocks(x, 'bloomington_stocks'))
js = janelia.groupby('gene_symbol').apply(lambda x: collapse_stocks(x, 'janelia_stocks'))
stocks_collapsed = bs.join(js)
# load biomarker data
biomarkers = pd.read_csv('../../output/testes_scRNAseq_pilot/biomarkers.tsv', sep='\t', index_col='gene')
# load fbgn2symbol data
REF = os.environ['REFERENCES_DIR']

fbgn2symbol = pd.read_csv(Path(REF, 'dmel/r6-16/fb_annotation/dmel_r6-16.fb_annotation'), 
                          sep='\t', usecols=['primary_FBgn', 'gene_symbol'], index_col='primary_FBgn')

symbol2fbgn = fbgn2symbol.reset_index().set_index('gene_symbol')
# put evertyhing together
bioStock = biomarkers.join(fbgn2symbol, how='left').merge(stocks_collapsed, left_on='gene_symbol', right_index=True, how='left').sort_values(by=['cluster'])
# add links to flybase for easy browsing
def linkify(fbgn):
    link = f'http://flybase.org/reports/{fbgn}.html'
    return f'=HYPERLINK("{link}","FlyBase")'

bioStock['LinkOut'] = bioStock.index.map(lambda x: linkify(x))
# Clean up table
bioStock.index.name = 'FBgn'
bioStock.set_index('gene_symbol', append=True, inplace=True)
header = ['cluster', 'p_val', 'p_val_adj', 'avg_logFC', 'pct.1', 'pct.2', 
          'bloomington_stocks', 'janelia_stocks', 'LinkOut']
bioStock = bioStock[header].copy()
# sort 
bioStock.sort_values(by='p_val_adj', ascending=False, inplace=True)
bioStock.sort_values(by='avg_logFC', inplace=True)
bioStock.sort_values(by='cluster', inplace=True)
# write data
bioStock.to_csv('../../output/20171205_testis_biomarkers_with_stocks.tsv', sep='\t')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1570038087177317, 'fraction_numerical': 0.009310198899703767, 'mean_word_length': 5.584958217270195, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '615686', 'license': 'MIT', 'n_tokens_mistral': 895, 'n_tokens_neox': 837, 'n_words': 157, 'path': 'notebook/2017-12-05_testis_biomarkers_with_stocks.ipynb', 'repo_name': 'jfear/larval_gonad', 'size': '2363'}