<jupyter_start><jupyter_text>read excel as dataframe<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pylab as plt
s = pd.read_excel(r"D:\MIT\STARmap\GFP-mCherry\20210407+0430_STARmap\Amplicon_count_based_quantification\sc_STARmap_3_replicates.xlsx",sheet_name = 0)
s2 = s.copy(deep = True) # make a copy of s<jupyter_output><empty_output><jupyter_text>determine the cutoff for transfection efficiencycutoff = 10 * mean(negative control)<jupyter_code>well = s["Well"]
name = s["Name"]
index = s["Index"]
mCherry = s["mCherry"]
GFP = s["GFP"]
length = len(well)

ctrl1 = s.loc[s["Index"] == 16,:]
ctrl2 = s.loc[s["Index"] == 17,:]

ctrl_mCherry1 = ctrl1["mCherry"]
ctrl_mCherry2 = ctrl2["mCherry"]
# ctrl_mCherry = ctrl_mCherry1+ctrl_mCherry2

ctrl_GFP1 = ctrl1["GFP"]
ctrl_GFP2 = ctrl2["GFP"]

cutoff_mCherry = 10*(np.sum(ctrl_mCherry1)+np.sum(ctrl_mCherry2))/(len(ctrl_mCherry1)+len(ctrl_mCherry2))
cutoff_GFP = 10*(np.sum(ctrl_GFP1)+np.sum(ctrl_GFP2))/(len(ctrl_GFP1)+len(ctrl_GFP2))
# cutoff = 100*(np.sum(ctrl_mCherry1)+np.sum(ctrl_mCherry2))/(len(ctrl_mCherry1)+len(ctrl_mCherry2))+10*(np.sum(ctrl_GFP1)+np.sum(ctrl_GFP2))/(len(ctrl_GFP1)+len(ctrl_GFP2))
print(cutoff_mCherry)
print(cutoff_GFP)<jupyter_output>2.005449591280654
0.6049046321525886<jupyter_text>1. count transfected cells2. exclude untransgected cells3. calculate transfection efficiencies4. calculate the mean and median values of GFP/mCherry of each well5. normalize mean and median values to control<jupyter_code>total_index_num = 17

index_count = np.zeros(total_index_num) # number of cells in each sample (3 replicates)
index_count_good = np.zeros(total_index_num) # number of transfected cells in each sample (3 replicates)
cutoff_list = np.zeros(total_index_num)
sc_mean = np.zeros(total_index_num)
sc_median = np.zeros(total_index_num)
sc_mean_norm = np.zeros(total_index_num) # normalized to ligation condition only
sc_median_norm = np.zeros(total_index_num) # normalized to ligation condition only
std = np.zeros(total_index_num) # standard deviation

for i in range(length):
    index_count[index[i]-1] = index_count[index[i]-1]+1
    if (name[i] == 'Transfection only') or (name[i] == 'WT cells only'):
        s["mCherry"][i] = -1 # label negative controls (Transfection only and WT cells only)
    elif name[i] == 'mCherry only':
        if mCherry[i] > cutoff_mCherry:
            index_count_good[index[i]-1] = index_count_good[index[i]-1]+1
        else:
            s["mCherry"][i] = -1 # label untransfeced cells
    else:
        if (mCherry[i] > cutoff_mCherry) and (GFP[i] > cutoff_GFP):
            index_count_good[index[i]-1] = index_count_good[index[i]-1]+1
        else:
            s["mCherry"][i] = -1 # label untransfeced cells
        
eff = np.zeros(total_index_num)
for j in range(total_index_num):
    eff[j] =  index_count_good[j]/index_count[j]   
print(index_count)
print(index_count_good)
print(eff)

# s.drop(index = (s.loc[(s['Intensity_IntegratedIntensity_mCherry'] == '-1')].index),inplace = True) # does not work
s = s.loc[s['mCherry'] != -1,:] # exclude untransfected cells

for j in range(total_index_num):
    ss = s.loc[s["Index"] == j+1,:]
    sc_mean[j] = np.mean(ss["log1p(GFP/mCherry)"])
    sc_median[j] = np.median(ss["log1p(GFP/mCherry)"])
    std[j] = np.std(ss["log1p(GFP/mCherry)"])

for j in range(total_index_num):
    sc_mean_norm[j] = sc_mean[j]-sc_mean[1]
    sc_median_norm[j] = sc_median[j]-sc_median[1]

print(sc_mean)
print(sc_median)    
print(sc_mean_norm)
print(sc_median_norm)
print(std)<jupyter_output><ipython-input-24-b47fbedfa159>:25: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  s["mCherry"][i] = -1 # label untransfeced cells
<ipython-input-24-b47fbedfa159>:20: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  s["mCherry"][i] = -1 # label untransfeced cells
<ipython-input-24-b47fbedfa159>:15: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  s["mCherry"][i] = -1 # label negative controls (Transfection only and WT cells only)<jupyter_text>generate and save violin plots<jupyter_code>import matplotlib.pyplot as plt
import seaborn as sns
plt.figure(figsize=(20,5))
plt.title("Single-cell STARmap quantification")
p1 = sns.violinplot(x = s["Name"],y = s["log1p(GFP/mCherry)"],linewidth = 2,width = 0.5,palette = ["darkgrey","darkgrey","darkgoldenrod","palevioletred","palevioletred","palevioletred","palevioletred","c","c","mediumpurple","mediumpurple","palegreen","palegreen","palegreen","darkgrey"],order = ["Mock ligation","No ligation","29xrA_ddC","3xSrA_ddC","3xSrA_InvdT","3xSrG_InvdT","6xSr(AG)","3xdA_ddC","23xdA_ddC","G4_telo_DNA_GtoC","G4_telo_DNA_WT","G4_C9orf72_RNA_6xSrG","G4_C9orf72_DNA_6xSG","G4_telo_DNA_6xSG","mCherry only"],scale = "width",inner = "box",cut = 0)
plt.xticks([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14],["Mock ligation\n(%d)"%(index_count_good[1]),"No ligation\n(%d)"%(index_count_good[0]),"29xrA_ddC\n(%d)"%(index_count_good[2]),"3xSrA_ddC\n(%d)"%(index_count_good[3]),"3xSrA_InvdT\n(%d)"%(index_count_good[4]),"3xSrG_InvdT\n(%d)"%(index_count_good[5]),"6xSr(AG)\n(%d)"%(index_count_good[10]),"3xdA_ddC\n(%d)"%(index_count_good[6]),"23xdA_ddC\n(%d)"%(index_count_good[7]),"G4_telo_DNA_GtoC\n(%d)"%(index_count_good[8]),"G4_telo_DNA_WT\n(%d)"%(index_count_good[9]),"G4_C9orf72_RNA_6xSrG\n(%d)"%(index_count_good[11]),"G4_C9orf72_DNA_6xSG\n(%d)"%(index_count_good[12]),"G4_telo_DNA_6xSG\n(%d)"%(index_count_good[13]),"mCherry only\n(%d)"%(index_count_good[14])],rotation = 45)
# plt.ylim(-0.5,5)
plt.ylabel('ln(1+GFP/mCherry)')
# plt.savefig(r'D:\MIT\STARmap\GFP-mCherry\20210407+0430_STARmap\Amplicon_count_based_quantification\log1p_violinplot.png')
# plt.savefig(r'D:\MIT\STARmap\GFP-mCherry\20210407+0430_STARmap\Amplicon_count_based_quantification\log1p_violinplot.svg',format = "svg")
plt.show()<jupyter_output><empty_output><jupyter_text>export transfection data and mean and median values of GFP/mCherry to excel<jupyter_code># list_output = [index_count,index_count_good,eff,cutoff_list,sc_mean,sc_median,sc_mean_norm,sc_median_norm,std]
# df_output = pd.DataFrame(list_output)
# df_output.to_excel(r"D:\MIT\STARmap\GFP-mCherry\20210407+0430_STARmap\list.xlsx")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15881481481481483, 'fraction_numerical': 0.04281481481481481, 'mean_word_length': 6.942352941176471, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 25, 'https://': 3, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '370596', 'license': 'MIT', 'n_tokens_mistral': 3033, 'n_tokens_neox': 2692, 'n_words': 400, 'path': 'GFPmCherry_STARmap_violinplot_figure.ipynb', 'repo_name': 'wanglab-broad/rna-therapeutic', 'size': '6750'}