!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC, 1 - (cast (C_count as float) / cast (CT_count as float)) as freqT FROM [sr320@washington.edu].[BiGo_lar_M1] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -o /Users/sr320/Desktop/test.csv
!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_input.csv

!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_input.csv > /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_c.csv

!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_c.csv

!tr ',' "\t" </Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_c.csv> /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2.txt

!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2.txt

!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit3_input.csv

!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr + '_' + (cast (pos as varchar)) as chr_start, chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC, 1 - (cast (C_count as float) / cast (CT_count as float)) as freqT FROM [sr320@washington.edu].[BiGo_lar_M1] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -f tsv -o /Volumes/Monarch/cnidary/BiGo_lar_M1_methylkit4_input.txt


!head /Volumes/Monarch/cnidary/BiGo_lar_M1_methylkit4_input.txt

!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr + '_' + (cast (pos as varchar)) as chr_start, chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC, 1 - (cast (C_count as float) / cast (CT_count as float)) as freqT FROM [sr320@washington.edu].[BiGo_lar_T1D3] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -f tsv -o /Volumes/Monarch/cnidary/BiGo_lar_T1D3_methylkit4_input.txt


!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr + '_' + (cast (pos as varchar)) as chr_start, chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC, 1 - (cast (C_count as float) / cast (CT_count as float)) as freqT FROM [sr320@washington.edu].[BiGo_lar_T1D5] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -f tsv -o /Volumes/Monarch/cnidary/BiGo_lar_T1D5_methylkit4_input.txt


!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr + '_' + (cast (pos as varchar)) as chr_start, chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC, 1 - (cast (C_count as float) / cast (CT_count as float)) as freqT FROM [sr320@washington.edu].[BiGo_lar_M3] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -f tsv -o /Volumes/Monarch/cnidary/BiGo_lar_M3_methylkit4_input.txt


!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr + '_' + (cast (pos as varchar)) as chr_start, chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC, 1 - (cast (C_count as float) / cast (CT_count as float)) as freqT FROM [sr320@washington.edu].[BiGo_lar_T3D3] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -f tsv -o /Volumes/Monarch/cnidary/BiGo_lar_T3D3_methylkit4_input.txt


!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr + '_' + (cast (pos as varchar)) as chr_start, chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC, 1 - (cast (C_count as float) / cast (CT_count as float)) as freqT FROM [sr320@washington.edu].[BiGo_lar_T3D5] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -f tsv -o /Volumes/Monarch/cnidary/BiGo_lar_T3D5_methylkit4_input.txt

!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -d "[sr320@washington.edu].[BiGo_Larvae_joineddata]" -o /Volumes/Monarch/cnidary/BiGo_lar_joineddata.csv

python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -d "[sr320@washington.edu].[Snapshot of BiGo_Larvae_joineddata]" -o /Volumes/Monarch/cnidary/BiGo_lar_joineddata3.csv




SELECT * 
FROM [sr320@washington.edu].[BiGo_lar_nonred_ID split]id
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_M1_hack]M1
ON id.[chr]=M1.[chr] AND id.[start]=M1.[start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T1D3_hack]T1D3
ON id.[chr]=T1D3.[chr] AND id.[start]=T1D3.[start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T1D5_hack]T1D5
ON id.[chr]=T1D5.[chr] AND id.[start]=T1D5.[start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_M3_hack]M3
ON id.[chr]=M3.[chr] AND id.[start]=M3.[start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T3D3_hack]T3D3
ON id.[chr]=T3D3.[chr] AND id.[start]=T3D3.[start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T3D5_hack]T3D5
ON id.[chr]=T3D5.[chr] AND id.[start]=T3D5.[start]

![im](files/img/BiGo_lar_joineddata.jpg)


![pearl](files/img/pearl.jpeg)



!head /Volumes/Monarch/cnidary/BiGo_lar_lociALL0.txt

!wc /Volumes/Monarch/cnidary/BiGo_lar_lociALL0.txt

!head /Volumes/Monarch/cnidary/BiGo_lar_joineddata_mk.csv

!python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC FROM [sr320@washington.edu].[BiGO_betty_plain_methratio_v1.txt] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -o /Volumes/web/cnidarian/BiGO_betty_plain_methylkit_input.csv



!head /Volumes/web/cnidarian/BiGO_betty_plain_methylkit_input.csv

!tail -n +2 /Volumes/web/cnidarian/BiGO_betty_plain_methylkit_input.csv > /Volumes/web/cnidarian/BiGO_betty_plain_methylkit.csv

!head /Volumes/web/cnidarian/BiGO_betty_plain_methylkit.csv

!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input.csv

!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_M3_methylkit.csv

!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit.csv

!wc /Volumes/web/cnidarian/BiGo_lar_M3_methylkit.csv

#with one line of code...   
!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input_oner.csv

!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input_oner.csv > /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_oner.csv

!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_oner.csv

!wc /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_oner.csv

!head /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit_input.csv

!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit.csv

!head /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit.csv
python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC FROM [sr320@washington.edu].[BiGo_lar_T3D5] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -o /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit_input.csv
!head /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit_input.csv

!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit.csv

!head /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit.csv

!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit_input.csv

!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M1_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_M1_methylkit.csv

!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit.csv
python /Users/sr320/sqlshare-pythonclient/tools/fetchdata.py -s "SELECT chr as chr, pos as start, '+' as strand, cast (CT_count as float) as CT_count, cast (C_count as float) as C_count, cast (C_count as float) / cast (CT_count as float) as freqC FROM [sr320@washington.edu].[BiGo_lar_T1D3] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -o /Volumes/web/cnidarian/BiGo_lar_T1D3_methylkit_input.csvtail -n +2 /Volumes/web/cnidarian/BiGo_lar_T1D3_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_T1D3_methylkit.csv
!head /Volumes/web/cnidarian/BiGo_lar_T1D3_methylkit.csv
tail -n +2 /Volumes/web/cnidarian/BiGo_lar_T1D5_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_T1D5_methylkit.csv
!head /Volumes/web/cnidarian/BiGo_lar_T1D5_methylkit.csv