One to many relation by awk

Diya123 · May 15, 2013, 7:15pm

Hi,

I have a file with more than 50,000 rows. The file is

**hsa-miR-10a**	** ABBA-1 **
**hsa-miR-10a**	** ABCA1 **
**hsa-miR-10a**	** ABCC3 **
**hsa-miR-10a**	** ABCC3 **
**hsa-miR-10a**	** ABI3 **
**hsa-miR-10a**	** ACAD11 **
**hsa-miR-10a**	** ACAD11 **
**hsa-miR-10a**	** ACRC **
**hsa-miR-10a**	** ACRC **
**hsa-miR-10a**	** ACTG1 **
**hsa-miR-10a**	** ACTL7A **
**hsa-miR-10a**	** ADAR **
**hsa-miR-10a**	** ADCY7 **
**hsa-miR-10a**	** AGGF1 **
**hsa-miR-10a**	** AKAP10 **
**hsa-miR-10a**	** ALAS2 **
**hsa-miR-10a**	** ALDH7A1 **
**hsa-miR-10a**	** ALPL **
**hsa-miR-10a**	** ALPL **
**hsa-miR-10a**	** ALX3 **
**hsa-miR-10a**	** AMN **
**hsa-miR-10a**	** AMOTL1 **
**hsa-miR-10a**	** ANK1 **
**hsa-miR-10a**	** ANK1 **
**hsa-miR-10a**	** ANKH **
**hsa-miR-10a**	** ANKRD1 **
**hsa-miR-10a**	** ANKRD13A **
**hsa-miR-10a**	** ANXA13 **
**hsa-miR-10a**	** AP4E1 **
**hsa-miR-10a**	** APOF **
**hsa-miR-10a**	** ARG2 **
**hsa-miR-10a**	** ARGLU1 **
**hsa-miR-10a**	** ARHGEF11 **
**hsa-miR-10a**	** ARHGEF12 **
**hsa-miR-10a**	** ARHGEF12 **
**hsa-miR-10a**	** ARPC1A **
**hsa-miR-10a**	** ARSJ **
**hsa-miR-10a**	** ARSJ **
**hsa-miR-10a**	** ARSJ **
**hsa-miR-10a**	** ARSJ **
**hsa-miR-10a**	** ARSK **
**hsa-miR-10a**	** ARSK **
**hsa-miR-10a**	** ASB16 **
**hsa-miR-10a**	** ATCAY **
**hsa-miR-10a**	** ATG4D **
**hsa-miR-10a**	** ATG7 **
**hsa-miR-10a**	** ATOH8 **
**hsa-miR-10a**	** ATP5L **
**hsa-miR-10a**	** BAG5 **
**hsa-miR-10a**	** BAI1 **
**hsa-miR-10a**	** BAP1 **
**hsa-miR-10a**	** BCAS4 **
**hsa-miR-10a**	** BDNF **
**hsa-miR-10a**	** BMPR1B **
**hsa-miR-10a**	** BRWD2 **
**hsa-miR-10a**	** BTN2A1 **
**hsa-miR-10a**	** C10orf68 **
**hsa-miR-10a**	** C10orf68 **
**hsa-miR-10a**	** C12orf52 **
**hsa-miR-10a**	** C13orf34 **
**hsa-miR-10a**	** C14orf100 **
**hsa-miR-10a**	** C14orf37 **
**hsa-miR-10a**	** C15orf56 **
**hsa-miR-10a**	** C16orf33 **
**hsa-miR-10a**	** C16orf48 **
**hsa-miR-10a**	** C16orf53 **
**hsa-miR-10a**	** C16orf57 **
**hsa-miR-10a**	** C16orf7 **
**hsa-miR-10a**	** C17orf28 **
**hsa-miR-10a**	** C17orf37 **
**hsa-miR-10a**	** C17orf65 **
**hsa-miR-10a**	** C17orf65 **
**hsa-miR-10a**	** C18orf24 **
**hsa-miR-10a**	** C1orf119 **
**hsa-miR-10a**	** C1orf2 **
**hsa-miR-10a**	** C1orf71 **
**hsa-miR-10a**	** C20orf196 **
**hsa-miR-10a**	** C20orf200 **
**hsa-miR-10a**	** C21orf7 **
**hsa-miR-10a**	** C22orf23 **
**hsa-miR-10a**	** C22orf26 **
**hsa-miR-10a**	** C2orf25 **
**hsa-miR-10a**	** C3orf18 **
**hsa-miR-10a**	** C4orf19 **
**hsa-miR-10a**	** C6orf66 **
**hsa-miR-10a**	** C7orf50 **
**hsa-miR-10a**	** C7orf53 **
**hsa-miR-10a**	** C9orf126 **
**hsa-miR-10a**	** C9orf164 **
**hsa-miR-10a**	** C9orf37 **
**hsa-miR-10a**	** C9orf5 **
**hsa-miR-10a**	** C9orf91 **
**hsa-miR-10a**	** CALCR **
**hsa-miR-10a**	** CAMK2B **
**hsa-miR-10a**	** CAPN10 **
**hsa-miR-10a**	** CAPRIN1 **
**hsa-miR-10a**	** CARD11 **
**hsa-miR-10a**	** CASP9 **
**hsa-miR-10a**	** CCDC127 **
**hsa-miR-10a**	** CCDC142 **
**hsa-miR-10a**	** CCDC146 **
**hsa-miR-10a**	** CCDC40 **
**hsa-miR-10a**	** CCDC41 **
**hsa-miR-10a**	** CCDC59 **
**hsa-miR-10a**	** CCDC88A **
**hsa-miR-10a**	** CCNB3 **
**hsa-miR-10a**	** CCNB3 **
**hsa-miR-10a**	** CCNK **
**hsa-miR-10a**	** CD2BP2 **
**hsa-miR-10a**	** CD3G **
**hsa-miR-10a**	** CDC14A **
**hsa-miR-10a**	** CDC42SE1 **
**hsa-miR-10a**	** CECR6 **
**hsa-miR-10a**	** CEP350 **
**hsa-miR-10a**	** CETN1 **
**hsa-miR-10a**	** CGNL1 **
**hsa-miR-10a**	** CHDH **
**hsa-miR-10a**	** CHST4 **
**hsa-miR-10a**	** CHST4 **
**hsa-miR-10a**	** CHST4 **
**hsa-miR-10a**	** CHST9 **
**hsa-miR-10a**	** CLCC1 **
**hsa-miR-10a**	** CLEC2B **
**hsa-miR-10a**	** CLEC4F **
**hsa-miR-10a**	** CNN1 **
**hsa-miR-10a**	** CNO **
**hsa-miR-10a**	** CNPY3 **
**hsa-miR-10a**	** CNPY3 **
**hsa-miR-10a**	** CNR2 **
**hsa-miR-10a**	** CNR2 **
**hsa-miR-10a**	** CNTFR **
**hsa-miR-10a**	** CREB1 **
**hsa-miR-10a**	** CREBL2 **
**hsa-miR-10a**	** CRLF3 **
**hsa-miR-10a**	** CRLF3 **
**hsa-miR-10a**	** CROT **
**hsa-miR-10a**	** CRTAC1 **
**hsa-miR-10a**	** CSDE1 **
**hsa-miR-10a**	** CTBP2 **
**hsa-miR-10a**	** CUGBP2 **
**hsa-miR-10a**	** CXCL3 **
**hsa-miR-10a**	** CXCR7 **
**hsa-miR-10a**	** CXorf34 **
**hsa-miR-10a**	** CYP24A1 **
**hsa-miR-10a**	** CYP4X1 **
**hsa-miR-10a**	** CYP7B1 **
**hsa-miR-10a**	** CYP7B1 **
**hsa-miR-10a**	** DCDC2 **
**hsa-miR-10a**	** DCDC2B **
**hsa-miR-10a**	** DCDC2B **
**hsa-miR-10a**	** DDX52 **
**hsa-miR-10a**	** DENND4B **
**hsa-miR-10a**	** DKC1 **
**hsa-miR-10a**	** DNAJC11 **
**hsa-miR-10a**	** DNAL4 **
**hsa-miR-10a**	** DOC2A **
**hsa-miR-10a**	** DOCK11 **
**hsa-miR-10a**	** DOHH **
**hsa-miR-10a**	** DOHH **
**hsa-miR-10a**	** DOK5 **
**hsa-miR-10a**	** DOK6 **
**hsa-miR-10a**	** DPH3 **
**hsa-miR-10a**	** DSCAM **
**hsa-miR-10a**	** DUS2L **
**hsa-miR-10a**	** E2F3 **
**hsa-miR-10a**	** EBF2 **
**hsa-miR-10a**	** EBF2 **
**hsa-miR-10a**	** EGR3 **
**hsa-miR-10a**	** EIF2AK2 **
**hsa-miR-10a**	** EIF2B4 **
**hsa-miR-10a**	** ELAVL2 **
**hsa-miR-10a**	** ELOVL2 **
**hsa-miR-10a**	** ELOVL2 **
**hsa-miR-10a**	** EPB41L5 **
**hsa-miR-10a**	** EPHA2 **
**hsa-miR-10a**	** EPN3 **
**hsa-miR-10a**	** ERAP2 **
**hsa-miR-10a**	** ERP29 **
**hsa-miR-10a**	** EXOD1 **
**hsa-miR-10a**	** EXOD1 **
**hsa-miR-10a**	** FAM105A **
**hsa-miR-10a**	** FAM105A **
**hsa-miR-10a**	** FAM111B **
**hsa-miR-10a**	** FAM116A **
**hsa-miR-10a**	** FAM116A **
**hsa-miR-10a**	** FAM33A **
**hsa-miR-10a**	** FAM3A **
**hsa-miR-10a**	** FAM46D **
**hsa-miR-10a**	** FAM57B **
**hsa-miR-10a**	** FAM71C **
**hsa-miR-10a**	** FAM82B **
**hsa-miR-10a**	** FAM84B **
**hsa-miR-10a**	** FAM84B **
**hsa-miR-10a**	** FAM91A1 **
**hsa-miR-10a**	** FAM91A1 **
**hsa-miR-10a**	** FBXO24 **
**hsa-miR-10a**	** FCF1 **
**hsa-miR-10a**	** FCRL3 **
**hsa-miR-10a**	** FGF23 **
**hsa-miR-10a**	** FHL3 **
**hsa-miR-10a**	** FIGN **
**hsa-miR-10a**	** FKBP15 **
**hsa-miR-10a**	** FKBP5 **
**hsa-miR-10a**	** FLJ14154 **
**hsa-miR-10a**	** FLJ20309 **
**hsa-miR-10a**	** FLJ21062 **
**hsa-miR-10a**	** FMNL2 **
**hsa-miR-10a**	** FN3KRP **
**hsa-miR-10a**	** FRMD4A **
**hsa-miR-10a**	** FUZ **
**hsa-miR-10a**	** FXR2 **
**hsa-miR-10a**	** FXR2 **
**hsa-miR-10a**	** FZD1 **
**hsa-miR-10a**	** FZD4 **
**hsa-miR-10a**	** FZD9 **
**hsa-miR-10a**	** GABRB2 **
**hsa-miR-10a**	** GALE **
**hsa-miR-10a**	** GALNTL2 **
**hsa-miR-10a**	** GATA6 **
**hsa-miR-10a**	** GCAT **
**hsa-miR-10a**	** GDPD1 **
**hsa-miR-10a**	** GEMIN8 **
**hsa-miR-10a**	** GFOD2 **
**hsa-miR-10a**	** GGT7 **
**hsa-miR-10a**	** GINS4 **
**hsa-miR-10a**	** GINS4 **
**hsa-miR-10a**	** GINS4 **
**hsa-miR-10a**	** GJA5 **
**hsa-miR-10a**	** GLS **
**hsa-miR-10a**	** GLS **
**hsa-miR-10a**	** GLULD1 **
**hsa-miR-10a**	** GLULD1 **
**hsa-miR-10a**	** GNA15 **
**hsa-miR-10a**	** GNGT1 **
**hsa-miR-10a**	** GOLGA2L1 **
**hsa-miR-10a**	** GOPC **
**hsa-miR-10a**	** GP1BA **
**hsa-miR-10a**	** GPR108 **
**hsa-miR-10a**	** GPR132 **
**hsa-miR-10a**	** GPR146 **
**hsa-miR-10a**	** GPR64 **
**hsa-miR-10a**	** GPR92 **
**hsa-miR-10a**	** GPSM3 **
**hsa-miR-10a**	** GRIK1 **
**hsa-miR-10a**	** GRM3 **
**hsa-miR-10a**	** GSK3A **
**hsa-miR-10a**	** GTF2H1 **
**hsa-miR-10a**	** GUCY2D **
**hsa-miR-10a**	** H3F3B **
**hsa-miR-10a**	** HCN1 **
**hsa-miR-10a**	** HCN1 **
**hsa-miR-10a**	** HDAC4 **
**hsa-miR-10a**	** HERC6 **
**hsa-miR-10a**	** HERC6 **
**hsa-miR-10a**	** HHIPL2 **
**hsa-miR-10a**	** HHIPL2 **
**hsa-miR-10a**	** HLA-DPB1 **
**hsa-miR-10a**	** HLA-E **
**hsa-miR-10a**	** HMG2L1 **
**hsa-miR-10a**	** HNF4G **
**hsa-miR-10a**	** HOXA3 **
**hsa-miR-10a**	** HOXB3 **
**hsa-miR-10a**	** HOXD10 **
**hsa-miR-10a**	** HRH4 **
**hsa-miR-10a**	** HSPC105 **
**hsa-miR-10a**	** HTATSF1 **
**hsa-miR-10a**	** HTF9C **
**hsa-miR-10a**	** ICA1L **
**hsa-miR-10a**	** ID4 **
**hsa-miR-10a**	** IFNAR2 **
**hsa-miR-10a**	** IKZF4 **
**hsa-miR-10a**	** IL5RA **
**hsa-miR-10a**	** IL5RA **
**hsa-miR-10a**	** IMPAD1 **
**hsa-miR-10a**	** INPPL1 **
**hsa-miR-10a**	** INSIG2 **
**hsa-miR-10a**	** IRAK4 **
**hsa-miR-10a**	** IRS1 **
**hsa-miR-10a**	** ITGB2 **
**hsa-miR-10a**	** ITPKC **
**hsa-miR-10a**	** IVD **
**hsa-miR-10a**	** IVD **
**hsa-miR-10a**	** JARID2 **
**hsa-miR-10a**	** JMJD2A **
**hsa-miR-10a**	** KANK4 **
**hsa-miR-10a**	** KCNA6 **
**hsa-miR-10a**	** KCNJ6 **
**hsa-miR-10a**	** KCTD13 **
**hsa-miR-10a**	** KCTD16 **
**hsa-miR-10a**	** KCTD17 **
**hsa-miR-10a**	** KCTD18 **
**hsa-miR-10a**	** KIAA0753 **
**hsa-miR-10a**	** KIAA1310 **
**hsa-miR-10a**	** KIAA1530 **
**hsa-miR-10a**	** KIAA1609 **
**hsa-miR-10a**	** KLF11 **
**hsa-miR-10a**	** KLHL21 **
**hsa-miR-10a**	** KLK10 **
**hsa-miR-10a**	** KRT79 **
**hsa-miR-10a**	** LATS1 **
**hsa-miR-10a**	** LCA5 **
**hsa-miR-10a**	** LCA5 **
**hsa-miR-10a**	** LHPP **
**hsa-miR-10a**	** LHPP **
**hsa-miR-10a**	** LMLN **
**hsa-miR-10a**	** LNP1 **
**hsa-miR-10a**	** LOC400120 **
**hsa-miR-10a**	** LOC400120 **
**hsa-miR-10a**	** LOC51149 **
**hsa-miR-10a**	** LOC51252 **
**hsa-miR-10a**	** LOC730112 **
**hsa-miR-10a**	** LOR **
**hsa-miR-10a**	** LRFN4 **
**hsa-miR-10a**	** LRP12 **
**hsa-miR-10a**	** LRRC17 **
**hsa-miR-10a**	** LRRC4B **
**hsa-miR-10a**	** LSS **
**hsa-miR-10a**	** LY6E **
**hsa-miR-10a**	** LY6K **
**hsa-miR-10a**	** LYSMD3 **
**hsa-miR-10a**	** LYSMD3 **
**hsa-miR-10a**	** LYZL6 **
**hsa-miR-10a**	** M6PR **
**hsa-miR-10a**	** MAGEB6 **
**hsa-miR-10a**	** MAGEC2 **
**hsa-miR-10a**	** MAGEC2 **
**hsa-miR-10a**	** MAGEL2 **
**hsa-miR-10a**	** MAGIX **
**hsa-miR-10a**	** MAP1LC3C **
**hsa-miR-10a**	** MAP3K7 **
**hsa-miR-10a**	** MAP4K4 **
**hsa-miR-10a**	** MAPK8IP1 **
**hsa-miR-10a**	** MAPKBP1 **
**hsa-miR-10a**	** MBD5 **
**hsa-miR-10a**	** MCART6 **
**hsa-miR-10a**	** MDGA2 **
**hsa-miR-10a**	** MDM2 **
**hsa-miR-10a**	** MDM2 **
**hsa-miR-10a**	** MDM2 **
**hsa-miR-10a**	** MDM2 **
**hsa-miR-10a**	** MGC45438 **
**hsa-miR-10a**	** MICB **
**hsa-miR-10a**	** MKI67 **
**hsa-miR-10a**	** MKNK2 **
**hsa-miR-10a**	** MMP11 **
**hsa-miR-10a**	** MPI **
**hsa-miR-10a**	** MRPS12 **
**hsa-miR-10a**	** MS4A6A **
**hsa-miR-10a**	** MSL3L1 **
**hsa-miR-10a**	** MTHFSD **
**hsa-miR-10a**	** MTM1 **
**hsa-miR-10a**	** MTMR14 **
**hsa-miR-10a**	** MTMR6 **
**hsa-miR-10a**	** MTMR6 **
**hsa-miR-10a**	** MTUS1 **
**hsa-miR-10a**	** MUC5B **
**hsa-miR-10a**	** MUTED **
**hsa-miR-10a**	** MYBL1 **
**hsa-miR-10a**	** MYO1B **
**hsa-miR-10a**	** MYST4 **
**hsa-miR-10a**	** MYT1L **
**hsa-miR-10a**	** NCAPD2 **
**hsa-miR-10a**	** NCF4 **
**hsa-miR-10a**	** NCOA6 **
**hsa-miR-10a**	** NCOR1 **
**hsa-miR-10a**	** NCOR2 **
**hsa-miR-10a**	** NCOR2 **
**hsa-miR-10a**	** NDUFB6 **
**hsa-miR-10a**	** NDUFV3 **
**hsa-miR-10a**	** NDUFV3 **
**hsa-miR-10a**	** NEDD4 **
**hsa-miR-10a**	** NEGR1 **
**hsa-miR-10a**	** NF2 **
**hsa-miR-10a**	** NFE2L3 **
**hsa-miR-10a**	** NGDN **
**hsa-miR-10a**	** NHP2L1 **
**hsa-miR-10a**	** NKTR **
**hsa-miR-10a**	** NKX3-1 **
**hsa-miR-10a**	** NLE1 **
**hsa-miR-10a**	** NLRP1 **
**hsa-miR-10a**	** NME3 **
**hsa-miR-10a**	** NMUR1 **
**hsa-miR-10a**	** NOPE **
**hsa-miR-10a**	** NOPE **
**hsa-miR-10a**	** NPM2 **
**hsa-miR-10a**	** NR2F2 **
**hsa-miR-10a**	** NR4A3 **
**hsa-miR-10a**	** NR4A3 **
**hsa-miR-10a**	** NUDT9 **
**hsa-miR-10a**	** NYX **
**hsa-miR-10a**	** OAZ3 **
**hsa-miR-10a**	** ODZ2 **
**hsa-miR-10a**	** OPN4 **
**hsa-miR-10a**	** OPRM1 **
**hsa-miR-10a**	** ORC1L **
**hsa-miR-10a**	** OSMR **
**hsa-miR-10a**	** P15RS **
**hsa-miR-10a**	** P15RS **
**hsa-miR-10a**	** PAP2D **
**hsa-miR-10a**	** PARVG **
**hsa-miR-10a**	** PATL1 **
**hsa-miR-10a**	** PATL1 **
**hsa-miR-10a**	** PCBD1 **
**hsa-miR-10a**	** PDE4A **
**hsa-miR-10a**	** PDZD8 **
**hsa-miR-10a**	** PDZD8 **
**hsa-miR-10a**	** PEAR1 **
**hsa-miR-10a**	** PFKFB4 **
**hsa-miR-10a**	** PGBD2 **
**hsa-miR-10a**	** PHF13 **
**hsa-miR-10a**	** PHF17 **
**hsa-miR-10a**	** PHF20L1 **
**hsa-miR-10a**	** PIK3CA **
**hsa-miR-10a**	** PIK3R5 **
**hsa-miR-10a**	** PLA1A **
**hsa-miR-10a**	** PLA1A **
**hsa-miR-10a**	** PLA2G2D **
**hsa-miR-10a**	** PLEKHA2 **
**hsa-miR-10a**	** PLEKHA7 **
**hsa-miR-10a**	** PLEKHN1 **
**hsa-miR-10a**	** PLEKHN1 **
**hsa-miR-10a**	** PNLIPRP3 **
**hsa-miR-10a**	** POLH **
**hsa-miR-10a**	** POP4 **
**hsa-miR-10a**	** POPDC2 **
**hsa-miR-10a**	** PPEF2 **
**hsa-miR-10a**	** PPP1R15B **
**hsa-miR-10a**	** PRKACA **
**hsa-miR-10a**	** PRKACG **
**hsa-miR-10a**	** PRKCI **
**hsa-miR-10a**	** PRRG2 **
**hsa-miR-10a**	** PRRG2 **
**hsa-miR-10a**	** PRRT3 **
**hsa-miR-10a**	** PRTG **
**hsa-miR-10a**	** PRTG **
**hsa-miR-10a**	** PRX **
**hsa-miR-10a**	** PSAPL1 **
**hsa-miR-10a**	** PSD **
**hsa-miR-10a**	** PSMD8 **
**hsa-miR-10a**	** PTCHD2 **
**hsa-miR-10a**	** PTK7 **
**hsa-miR-10a**	** PURG **
**hsa-miR-10a**	** RAB15 **
**hsa-miR-10a**	** RAB15 **
**hsa-miR-10a**	** RAP2A **
**hsa-miR-10a**	** RB1CC1 **
**hsa-miR-10a**	** RBBP7 **
**hsa-miR-10a**	** RBM12B **
**hsa-miR-10a**	** RECQL4 **
**hsa-miR-10a**	** RER1 **
**hsa-miR-10a**	** RFPL2 **
**hsa-miR-10a**	** RFPL2 **
**hsa-miR-10a**	** RFPL3 **
**hsa-miR-10a**	** RG9MTD2 **
**hsa-miR-10a**	** RG9MTD2 **
**hsa-miR-10a**	** RHPN1 **
**hsa-miR-10a**	** RNF125 **
**hsa-miR-10a**	** RNF135 **
**hsa-miR-10a**	** RNF2 **
**hsa-miR-10a**	** RNF20 **
**hsa-miR-10a**	** RNF7 **
**hsa-miR-10a**	** RORA **
**hsa-miR-10a**	** RP9 **
**hsa-miR-10a**	** RPAP3 **
**hsa-miR-10a**	** RPL13A **
**hsa-miR-10a**	** RPL13A **
**hsa-miR-10a**	** RPS3 **
**hsa-miR-10a**	** RPS6KA1 **
**hsa-miR-10a**	** RS1 **
**hsa-miR-10a**	** RTN4R **
**hsa-miR-10a**	** RXRA **
**hsa-miR-10a**	** SAA4 **
**hsa-miR-10a**	** SAPS2 **
**hsa-miR-10a**	** SCGB2A2 **
**hsa-miR-10a**	** SDC1 **
**hsa-miR-10a**	** SEC14L1 **
**hsa-miR-10a**	** SEPT3 **
**hsa-miR-10a**	** SEPT7 **
**hsa-miR-10a**	** SERPINE1 **
**hsa-miR-10a**	** SERTAD1 **
**hsa-miR-10a**	** SF3B3 **
**hsa-miR-10a**	** SF3B3 **
**hsa-miR-10a**	** SHANK3 **
**hsa-miR-10a**	** SIM2 **
**hsa-miR-10a**	** SIRT5 **
**hsa-miR-10a**	** SIRT5 **
**hsa-miR-10a**	** SLBP **
**hsa-miR-10a**	** SLC10A1 **
**hsa-miR-10a**	** SLC10A4 **
**hsa-miR-10a**	** SLC23A3 **
**hsa-miR-10a**	** SLC25A1 **
**hsa-miR-10a**	** SLC25A15 **
**hsa-miR-10a**	** SLC25A2 **
**hsa-miR-10a**	** SLC25A2 **
**hsa-miR-10a**	** SLC25A38 **
**hsa-miR-10a**	** SLC30A4 **
**hsa-miR-10a**	** SLC39A3 **
**hsa-miR-10a**	** SLC39A3 **
**hsa-miR-10a**	** SLC5A3 **
**hsa-miR-10a**	** SMAP1 **
**hsa-miR-10a**	** SMG6 **
**hsa-miR-10a**	** SMTNL2 **
**hsa-miR-10a**	** SMU1 **
**hsa-miR-10a**	** SMU1 **
**hsa-miR-10a**	** SMURF1 **
**hsa-miR-10a**	** SMURF1 **
**hsa-miR-10a**	** SNAPC3 **
**hsa-miR-10a**	** SNX18 **
**hsa-miR-10a**	** SNX20
  hsa-miR-10a**	** SMURF1**
**hsa-miR-20a**	** SMURF1 **
**hsa-miR-30a**	** SMURF1 **
**hsa-miR-40a**	** SMURF1 **
**hsa-miR-50a**	** SMURF1 **
**hsa-miR-60a**	** SMURF1 **
**hsa-miR-70a**	** SNX20 **
**hsa-miR-80a**	** SNX20 **
**hsa-miR-90a**	** SNX20 
  hsa-miR-100a**	** SNX20 *

Column1 is miRNA and column 2 is genes. What I want to know is how many of my genes are targeted by atleast 4 different miRNA. If at all there are any then the output should be

  hsa-miR-10a**	** SMURF1**
**hsa-miR-20a**	** SMURF1 **
**hsa-miR-30a**	** SMURF1 **
**hsa-miR-40a**	** SMURF1 **
**hsa-miR-50a**	** SMURF1 **
**hsa-miR-60a**	** SMURF1 **
**hsa-miR-70a**	** SNX20 **
**hsa-miR-80a**	** SNX20 **
**hsa-miR-90a**	** SNX20 
  hsa-miR-100a**	** SNX20

Can I do this in awk?

Thanks,

alister · May 15, 2013, 7:36pm

One simple way is to read the file twice:

awk -F '[* ]+' 'NR==FNR {a[$3]++; next}; a[$3]>3' file file

For future reference, it helps if you describe the nature of the file format in addition to just dumping a data sample. There might actually be a method to the asterisk madness, but in the absence of an explanation, it seems like a file format designed to inflict pain.

Regards,
Alister

Scrutinizer · May 15, 2013, 7:59pm

This slightly cryptic awk:

awk -F'[ \t*]*' 'NR==FNR{ if(!T[$2,$3]++ && ++F[$3]==4) S[$3]; next} $3 in S && !U[$2,$3]++' file file

Produces:

**hsa-miR-10a**	** SMURF1 **
**hsa-miR-10a**	** SNX20
**hsa-miR-20a**	** SMURF1 **
**hsa-miR-30a**	** SMURF1 **
**hsa-miR-40a**	** SMURF1 **
**hsa-miR-50a**	** SMURF1 **
**hsa-miR-60a**	** SMURF1 **
**hsa-miR-70a**	** SNX20 **
**hsa-miR-80a**	** SNX20 **
**hsa-miR-90a**	** SNX20 
  hsa-miR-100a**	** SNX20 *

Which differs from your desired output. Do the targeted genes need to appear sequentially in the file?

Like Alister says, it would help to understand the meaning of the asterisks..

Diya123 · May 16, 2013, 1:10pm

Hi,

Thanks for the post. I really don't see the * in the excel file, but when i paste it in a text file i see them.. There is no meaning for them. We can eliminate them..

Thanks,