前人的研究，已经将每条染色体分成了10个左右的bin区域，用于粗定位基因的物理位置。本工具用于将GWAS或QTL Mapping的位置信息转换成bin位置区域。
【我写好的excel下载】
https://dataholdcn.cn/excel/B73%20RefGen_v2%20bin%20physical%20position_tool_za.xlsx
【使用方法】
在 “Reslut”工作表中，替换SNP名称即可。
【实现过程】
首先，我们需要bin区域的物理信息，这里以B73 RefGen v2为例。将下表数据保存到excel文件的“Clean Table“工作表。Bin列表示物理位置bin区段，Chr是染色体号，Size是区段大小，Start是起始位置，End是结束位置，Index是用于查找的信息。
| Bin | Chr | Size (Mbp) | Start | End | Index |
| 1.00 | Chr1 | 2.04 | 1 | 2039901 | S1_1 |
| 1.01 | Chr1 | 10.36 | 2039901 | 12398949 | S1_2039901 |
| 1.02 | Chr1 | 16.86 | 12398949 | 29258944 | S1_12398949 |
| 1.03 | Chr1 | 22.89 | 29258944 | 52152777 | S1_29258944 |
| 1.04 | Chr1 | 30.42 | 52152777 | 82574898 | S1_52152777 |
| 1.05 | Chr1 | 93.09 | 82584786 | 175670048 | S1_82584786 |
| 1.06 | Chr1 | 23.22 | 175670048 | 198887570 | S1_175670048 |
| 1.07 | Chr1 | 29.4 | 198854443 | 228254156 | S1_198854443 |
| 1.08 | Chr1 | 21.84 | 228254156 | 250093155 | S1_228254156 |
| 1.09 | Chr1 | 17.77 | 250093155 | 267860952 | S1_250093155 |
| 1.10 | Chr1 | 15.33 | 267860952 | 283188047 | S1_267860952 |
| 1.11 | Chr1 | 14.78 | 283259156 | 298039872 | S1_283259156 |
| 1.12 | Chr1 | 3.39 | 297960525 | 301354135 | S1_297960525 |
| 2.00 | Chr2 | 1.55 | 1 | 1551442 | S2_1 |
| 2.01 | Chr2 | 2.64 | 1551442 | 4187615 | S2_1551442 |
| 2.02 | Chr2 | 10.82 | 4187615 | 15005045 | S2_4187615 |
| 2.03 | Chr2 | 13.75 | 15016290 | 28771109 | S2_15016290 |
| 2.04 | Chr2 | 42.97 | 28771109 | 71742767 | S2_28771109 |
| 2.05 | Chr2 | 81.4 | 71742767 | 153140073 | S2_71742767 |
| 2.06 | Chr2 | 34.04 | 153140073 | 187179001 | S2_153140073 |
| 2.07 | Chr2 | 17.72 | 187179001 | 204896812 | S2_187179001 |
| 2.08 | Chr2 | 18.42 | 204896812 | 223320002 | S2_204896812 |
| 2.09 | Chr2 | 12.15 | 223320002 | 235471439 | S2_223320002 |
| 2.10 | Chr2 | 2.42 | 234646685 | 237068873 | S2_234646685 |
| 3.00 | Chr3 | 1.73 | 1 | 1729470 | S3_1 |
| 3.01 | Chr3 | 2.16 | 1729470 | 3888321 | S3_1729470 |
| 3.02 | Chr3 | 4.71 | 3888331 | 8599890 | S3_3888331 |
| 3.03 | Chr3 | 4.5 | 8598772 | 13102315 | S3_8598772 |
| 3.04 | Chr3 | 113.15 | 13109892 | 126255823 | S3_13109892 |
| 3.05 | Chr3 | 42.15 | 126255823 | 168409685 | S3_126255823 |
| 3.06 | Chr3 | 22.7 | 168409685 | 191113733 | S3_168409685 |
| 3.07 | Chr3 | 14.26 | 191113733 | 205370271 | S3_191113733 |
| 3.08 | Chr3 | 10.5 | 205370271 | 215869148 | S3_205370271 |
| 3.09 | Chr3 | 16.29 | 215846541 | 232140174 | S3_215846541 |
| 3.10 | Chr3 | 0.07 | 232074985 | 232140174 | S3_232074985 |
| 4.00 | Chr4 | 0.69 | 1 | 694004 | S4_1 |
| 4.01 | Chr4 | 4.35 | 694004 | 5045991 | S4_694004 |
| 4.02 | Chr4 | 6.36 | 5045991 | 11403953 | S4_5045991 |
| 4.03 | Chr4 | 10.2 | 11403953 | 21605708 | S4_11403953 |
| 4.04 | Chr4 | 10.67 | 21605708 | 32280422 | S4_21605708 |
| 4.05 | Chr4 | 118.83 | 32251206 | 151082840 | S4_32251206 |
| 4.06 | Chr4 | 19.96 | 151110020 | 171066103 | S4_151110020 |
| 4.07 | Chr4 | 8.77 | 171066103 | 179834540 | S4_171066103 |
| 4.08 | Chr4 | 25.3 | 179834540 | 205136045 | S4_179834540 |
| 4.09 | Chr4 | 32.11 | 205136045 | 237246237 | S4_205136045 |
| 4.10 | Chr4 | 2.74 | 237246237 | 239987981 | S4_237246237 |
| 4.11 | Chr4 | 2.04 | 239987981 | 242029974 | S4_239987981 |
| 5.00 | Chr5 | 3.32 | 1 | 3323880 | S5_1 |
| 5.01 | Chr5 | 4.67 | 3323880 | 7997002 | S5_3323880 |
| 5.02 | Chr5 | 6.86 | 8008060 | 14870573 | S5_8008060 |
| 5.03 | Chr5 | 65.95 | 14853619 | 80804839 | S5_14853619 |
| 5.04 | Chr5 | 91.59 | 80804839 | 172395871 | S5_80804839 |
| 5.05 | Chr5 | 22.92 | 172438770 | 195358319 | S5_172438770 |
| 5.06 | Chr5 | 9.3 | 195305700 | 204605587 | S5_195305700 |
| 5.07 | Chr5 | 7.12 | 204659857 | 211775643 | S5_204659857 |
| 5.08 | Chr5 | 3.75 | 211775643 | 215521293 | S5_211775643 |
| 5.09 | Chr5 | 2.41 | 215465694 | 217872852 | S5_215465694 |
| 6.00 | Chr6 | 8.27 | 1 | 8274025 | S6_1 |
| 6.01 | Chr6 | 78.67 | 8276813 | 86946680 | S6_8276813 |
| 6.02 | Chr6 | 9.89 | 86946680 | 96840186 | S6_86946680 |
| 6.03 | Chr6 | 7.78 | 96840186 | 104619429 | S6_96840186 |
| 6.04 | Chr6 | 16.41 | 104619429 | 121033444 | S6_104619429 |
| 6.05 | Chr6 | 32.92 | 121033444 | 153956114 | S6_121033444 |
| 6.06 | Chr6 | 7.37 | 153762257 | 161129826 | S6_153762257 |
| 6.07 | Chr6 | 5.76 | 161129826 | 166892211 | S6_161129826 |
| 6.08 | Chr6 | 2.29 | 166885247 | 169174353 | S6_166885247 |
| 7.00 | Chr7 | 4.71 | 1 | 4707470 | S7_1 |
| 7.01 | Chr7 | 9.15 | 4712353 | 13861507 | S7_4712353 |
| 7.02 | Chr7 | 114.31 | 13861507 | 128175453 | S7_13861507 |
| 7.03 | Chr7 | 27.88 | 128175453 | 156050470 | S7_128175453 |
| 7.04 | Chr7 | 12.28 | 156050470 | 168334968 | S7_156050470 |
| 7.05 | Chr7 | 6.07 | 168334968 | 174407842 | S7_168334968 |
| 7.06 | Chr7 | 2.4 | 174362351 | 176764762 | S7_174362351 |
| 8.00 | Chr8 | 1.83 | 1 | 1831964 | S8_1 |
| 8.01 | Chr8 | 8.24 | 1831964 | 10072434 | S8_1831964 |
| 8.02 | Chr8 | 11.08 | 10072434 | 21153183 | S8_10072434 |
| 8.03 | Chr8 | 87.54 | 21153183 | 108690693 | S8_21153183 |
| 8.04 | Chr8 | 13.79 | 108690693 | 122478285 | S8_108690693 |
| 8.05 | Chr8 | 23.95 | 122478285 | 146426603 | S8_122478285 |
| 8.06 | Chr8 | 18.84 | 146426603 | 165267974 | S8_146426603 |
| 8.07 | Chr8 | 4.07 | 165267974 | 169336170 | S8_165267974 |
| 8.08 | Chr8 | 3.63 | 169336170 | 172961960 | S8_169336170 |
| 8.09 | Chr8 | 2.39 | 172961960 | 175347686 | S8_172961960 |
| 9.00 | Chr9 | 3.31 | 1 | 3312268 | S9_1 |
| 9.01 | Chr9 | 8.47 | 3312268 | 11781894 | S9_3312268 |
| 9.02 | Chr9 | 11.49 | 11781894 | 23268159 | S9_11781894 |
| 9.03 | Chr9 | 78.48 | 23268159 | 101747879 | S9_23268159 |
| 9.04 | Chr9 | 25.44 | 101747879 | 127192550 | S9_101747879 |
| 9.05 | Chr9 | 9.61 | 127192550 | 136806801 | S9_127192550 |
| 9.06 | Chr9 | 11.43 | 136806801 | 148241346 | S9_136806801 |
| 9.07 | Chr9 | 6.63 | 148241346 | 154869700 | S9_148241346 |
| 9.08 | Chr9 | 2.15 | 154599322 | 156750706 | S9_154599322 |
| 10.00 | Chr10 | 2.65 | 1 | 2650397 | S10_1 |
| 10.01 | Chr10 | 2.36 | 2650397 | 5008368 | S10_2650397 |
| 10.02 | Chr10 | 8.55 | 5008368 | 13559627 | S10_5008368 |
| 10.03 | Chr10 | 74.79 | 13559627 | 88348445 | S10_13559627 |
| 10.04 | Chr10 | 39.61 | 88348445 | 127958713 | S10_88348445 |
| 10.05 | Chr10 | 9.35 | 127936624 | 137286408 | S10_127936624 |
| 10.06 | Chr10 | 5.5 | 137311961 | 142812228 | S10_137311961 |
| 10.07 | Chr10 | 6.82 | 142812228 | 149627545 | S10_142812228 |
新建“Reslut”工作表，第一行的前4列分别命名为：
| SNP | Chr | Position | Bin |
对应的公式为：
| S1_191524962 | =”Chr”&MID(A2,2,FIND(“_”,A2)-2) | =MID(A2,FIND(“_”,A2)+1,LEN(A2)) | =XLOOKUP(A2,’Clean Table’!F:F,’Clean Table’!A:A,,-1,1) |
第一列用于填写SNP名称，第二列用于获得染色体信息，第三列获取物理位置信息，第四列通过查找获取bin位置。
接下来只需要修改第一列的名称，其他列下拉就可获得每个SNP的bin位置。
若要修改参考基因组，替换“Clean Table”工作表即可。
发表评论