Converting methratio files for methylkit analysis¶
REDUX- New reformatting (11/2013)¶
M1¶
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'
In [5]:
!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_input.csv
In [6]:
!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_input.csv > /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_c.csv
In [7]:
!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_c.csv
In [8]:
!tr ',' "\t" </Volumes/web/cnidarian/BiGo_lar_M1_methylkit2_c.csv> /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2.txt
In [9]:
!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit2.txt
Hack to get unique ID¶
In [10]:
!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit3_input.csv
python tool to download hack
In [2]:
!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
In [3]:
!head /Volumes/Monarch/cnidary/BiGo_lar_M1_methylkit4_input.txt
T1D3¶
In [4]:
!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
T1D5¶
In [5]:
!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
M3¶
In [6]:
!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
T3D3¶
In [8]:
!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
T3D5¶
In [9]:
!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
In excel and textwrangler made a list of non_redundant locis from all 6 files
Left Join in SQLshare on non_redundant ID
SELECT *
FROM [sr320@washington.edu].[_BiGo_lar_nonred_ID.txt]id
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_M1_hack]M1
ON id.[chr_start]=M1.[chr_start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T1D3_hack]T1D3
ON id.[chr_start]=T1D3.[chr_start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T1D5_hack]T1D5
ON id.[chr_start]=T1D5.[chr_start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_M3_hack]M3
ON id.[chr_start]=M3.[chr_start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T3D3_hack]T3D3
ON id.[chr_start]=T3D3.[chr_start]
LEFT JOIN [sr320@washington.edu].[_BiGo_lar_T3D5_hack]T3D5
ON id.[chr_start]=T3D5.[chr_start]
In [ ]:
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
In [ ]:
Re ReDux 11/6/2013¶
Will try double column join ? as per Dan's Suggestion
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]
this worked fine..
In [ ]:
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]
In [12]:
/bin/sh: -c: line 0: syntax error near unexpected token `files/img/BiGo_lar_joineddata.jpg' /bin/sh: -c: line 0: `[im](files/img/BiGo_lar_joineddata.jpg)'
In [11]:
/bin/sh: -c: line 0: syntax error near unexpected token `files/img/pearl.jpeg' /bin/sh: -c: line 0: `[pearl](files/img/pearl.jpeg)'
In [ ]:
In [13]:
!head /Volumes/Monarch/cnidary/BiGo_lar_lociALL0.txt
chr start chr_start CountingCs SummingCs C16318 177 C16318_177 6 0 C16318 196 C16318_196 6 0 C16318 203 C16318_203 6 0 C16318 302 C16318_302 6 0 C19344 310 C19344_310 6 0 C19356 52 C19356_52 6 0 C19356 107 C19356_107 6 0 C19356 120 C19356_120 6 0 C19356 123 C19356_123 6 0
In [14]:
!wc /Volumes/Monarch/cnidary/BiGo_lar_lociALL0.txt
3230 16155 130260 /Volumes/Monarch/cnidary/BiGo_lar_lociALL0.txt
Up into SQLshare and generate file where loci covered by all 6 datasets and > 0 C_count
SELECT * FROM [sr320@washington.edu].[_BiGo_lar_joineddata_sums.csv]
where CountingCs = 6
and
SummingCs > 0
In [15]:
!head /Volumes/Monarch/cnidary/BiGo_lar_joineddata_mk.csv
Creation of new tab delimited text files with following structure
chr start strand CT_count C_count freqC freqT
These files only contain loci covered in all 6 files and at least 1 mCpG
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_M1_methylkit6.txt
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T1D3_methylkit6.txt
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T1D5_methylkit6.txt
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_M3_methylkit6.txt
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T3D3_methylkit6.txt
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T3D5_methylkit6.txt
Quicklist old
M3
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_M3_methylkit.csv
T3D3
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T3D3_methylkit.csv
T3D5
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T3D5_methylkit.csv
M1
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_M1_methylkit.csv
T1D3
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T1D3_methylkit.csv
T1D5
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_T1D5_methylkit.csv
QC
M3
http://eagle.fish.washington.edu/cnidarian/BiGo_lar_M3_methylkit_oner.csv
BiGo(gonad)
http://eagle.fish.washington.edu/cnidarian/BiGO_betty_plain_methylkit.csv
In [1]:
!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
In [2]:
!head /Volumes/web/cnidarian/BiGO_betty_plain_methylkit_input.csv
In [3]:
!tail -n +2 /Volumes/web/cnidarian/BiGO_betty_plain_methylkit_input.csv > /Volumes/web/cnidarian/BiGO_betty_plain_methylkit.csv
In [4]:
!head /Volumes/web/cnidarian/BiGO_betty_plain_methylkit.csv
M3¶
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
FROM [sr320@washington.edu].[BiGo_lar_M3]
where
context like '__CG_'
and
CT_Count >= 5
and
ratio <> 'NA'
SELECT
chr as chr,
start as start,
strand as strand,
CT_count as CT_count,
C_count as C_count,
C_count/CT_count as freqC
FROM [sr320@washington.edu].[_M3methylkit_s1]
SELECT
chr as chr,
start as start,
strand as strand,
CT_count as CT_count,
freqC as freqC
FROM [sr320@washington.edu].[_M3mehthykit_step2]
In [7]:
!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input.csv
In [8]:
!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_M3_methylkit.csv
In [9]:
!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit.csv
In [18]:
!wc /Volumes/web/cnidarian/BiGo_lar_M3_methylkit.csv
182475 182475 5044686 /Volumes/web/cnidarian/BiGo_lar_M3_methylkit.csv
In [14]:
#with one line of code...
!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input_oner.csv
In [15]:
!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_input_oner.csv > /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_oner.csv
In [16]:
!head /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_oner.csv
In [17]:
!wc /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_oner.csv
182475 182475 5410598 /Volumes/web/cnidarian/BiGo_lar_M3_methylkit_oner.csv
T3D3¶
In [12]:
!head /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit_input.csv
In [13]:
!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit.csv
In [14]:
!head /Volumes/web/cnidarian/BiGo_lar_T3D3_methylkit.csv
T3D5¶
In [9]:
!head /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit_input.csv
In [10]:
!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit.csv
In [11]:
!head /Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit.csv
FILE
/Volumes/web/cnidarian/BiGo_lar_T3D5_methylkit.csv
M1¶
In [5]:
!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit_input.csv
In [6]:
!tail -n +2 /Volumes/web/cnidarian/BiGo_lar_M1_methylkit_input.csv > /Volumes/web/cnidarian/BiGo_lar_M1_methylkit.csv
In [7]:
!head /Volumes/web/cnidarian/BiGo_lar_M1_methylkit.csv
FILE
/Volumes/web/cnidarian/BiGo_lar_M1_methylkit.csv
T1D3¶
In [3]:
!head /Volumes/web/cnidarian/BiGo_lar_T1D3_methylkit.csv
FILE
/Volumes/web/cnidarian/BiGo_lar_T1D3_methylkit.csv
T1D5¶
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_T1D5] where context like '__CG_' and CT_Count >= 5 and ratio <> 'NA'" -o /Volumes/web/cnidarian/BiGo_lar_T1D5_methylkit_input.csv
In [4]:
!head /Volumes/web/cnidarian/BiGo_lar_T1D5_methylkit.csv
FILE
/Volumes/web/cnidarian/BiGo_lar_T1D5_methylkit.csv
In [ ]:
In [ ]: