manipulating a file by R or bash?

Question

[Za]

Hi,

I have a big file as below;

GeneID  GO  geneid

Could you please help me, to have a dataframe with 3 more columns for independently? So these values would come in front of corresponding genes and GO term

Thanks a lot for any help

[cpad0112]

dput would help better understanding df.

[Za]

Sorry, help me please

Instead of placed in below rows of genes, I want them independently in 3 columns

For example ideal results would be

[zx8754]

Where is the data gone?

Answer 1

[Ram]

I think you can use awk on the mixed-up file and match $1 to a floating point value [0-9.]+, writing only those rows to a separate file. Then you can grep -Fvx the newly generated file against the larger file to create a third file that excludes all numeric valued rows, containing only the GeneID-GO-geneid rows.

Now you'd have two files with two different, internally consistent data formats that you can use any tool of your choice to process.

I'm not giving you a ready-made solution as I've addressed most important parts and you should be able to play around with awk and grep and get to the final solution.

[Za]

Sorry you mean

awk $1 [0-9.]+ file.txt > file1.txt

Gives

awk: syntax error at source line 1
 context is
     >>> [ <<< 
awk: bailing out at source line 1

[goodez]

That is not how awk works. At least make an attempt to read some documentation

[Za]

Sorry believe me but I don't have time stress does not allow me to focus on googling

However I found this in R, I wanna try that

[Ram]

Whatever works for you, I guess. These utilities are quite easy to learn, here is awk syntax:

awk 'pattern { action }' in_file

pattern is the part where you can use $1, etc to pick where you want to do an operation/action, and action is the operation/action you wish to perform. awk '$1==10 {print}' prints where the first column has value 10.

See this tl;dr page for a few examples.

[Za]

Hi, @RamRS

Please don't be angry at me

Honestly, yesterday an office mate asked me to solve this problem for him but neither I had time nor enough knowledge to solve that; So, I asked my question here and biostars kindly solved my problem as always. I sent him the solution and he got pleased a lot because he does know too much less than me in such staffs. Suddenly, I thought by copying and pasting these code in google, he would simply find my post here. So, honestly, I was shy if he knew how I solved his problem. Therefore, I removed some part of data because gene IDs is specific to his working data. I am sorry for that and thank you for helping me

Believe, I am not a too bad person :( a bit stupid though

[Ram]

Za, there is nothing to be angry about. We know you're doing your best, I would nevertheless like for you to try a bit harder to learn basic unix utilities as they empower you and when you wield them well, they can give a silver lining to a bad day.

IMHO you should not take on tasks that you cannot handle, especially if there is an option to pass on them. At this point, you need to focus on your learning and avoid taking on time-bound tasks that you cannot learn from in a relaxed manner. If you try to do people favors by stretching yourself thin, you're not giving yourself the attention you deserve. Help others but not at your cost.

You're neither a bad person nor are you stupid. Don't call yourself names, that only affects your growth. You might feel stupid at times, that does not make you a stupid person.

Answer 2

[n,n]

I also came up with a scripting solution which I'll explain step by step (but won't throw the literal code since this is a good exercise for learning). I also assumed your input is tab-delimited, that all gene Ids start with "DDB" and that there is only 3 values per unique GO ID as shown in your post:

1- isolate the lines that contain pvalue, oddsratio and expcount with grep in a new file

2- get a GO ID element from column 2 of the new file with awk and print the corresponding $1 column (values themselves) if the GO ID element matches $2 (GO IDs) in the new file, you should get 3 values.

3- paste the 3 printed matches to a tmp file in a single row

4- define variables for each column in the tmp file (3 variables each one pointing to pvalue, oddsratio and expcount respectively)

5- grep your original input for the GO ID that you are working with from step 2 and sed the resulting lines with the 3 defined variables separated by tabs at the end of each line, append the output to a file that will be your output file.

6- this process describes what you need to do for a single GO ID, however you will notice it is possible to organize the whole thing starting from step 2 in a loop so that you automate the process for a list of uniq GO IDs that you can get in step 2.

Hope this helps, what you wanna do can be kinda tricky so don't feel discouraged.

Answer 3

[zx8754]

By the time I posted this answer the data got removed, so I am using below as example input:

# example dataframe
df1 <- read.table(text = "a   ID  desc
                  DDB_G0284157    GO:0042981  geneIds1
                  DDB_G0285005    GO:0042981  geneIds2
                  DDB_G0285251    GO:0042981  geneIds3
                  DDB_G0285411    GO:0042981  geneIds4
                  DDB_G0289445    GO:0042981  geneIds5
                  DDB_G0294046    GO:0042981  geneIds6
                  0.498871327 GO:0042981  pvalue
                  1.641005803 GO:0042981  oddsRatio
                  0.652256034 GO:0042981  expCount
                  DDB_G0291275    GO:0006873  geneIds20
                  DDB_G0291858    GO:0006873  geneIds21
                  DDB_G0292412    GO:0006873  geneIds22
                  DDB_G0292564    GO:0006873  geneIds23
                  0.929591794 GO:0006873  pvalue
                  0.371461227 GO:0006873  oddsRatio
                  2.500314795 GO:0006873  expCount
                  DDB_G0270658    GO:0009725  geneIds1
                  DDB_G0280961    GO:0009725  geneIds2
                  DDB_G0289445    GO:0009725  geneIds3
                  0.292013941 GO:0009725  pvalue
                  4.105415861 GO:0009725  oddsRatio
                  0.326128017 GO:0009725  expCount", header = TRUE)

# split on ID, then get columns from desc
res <- do.call(rbind,
               lapply(split(df1, df1$ID), function(i){
                 x1 <- head(i, -3)
                 x2 <- tail(i, 3)
                 x3 <- as.data.frame(t(x2[, "a", drop = FALSE]))
                 colnames(x3) <- x2$desc
                 cbind(x1, x3)
               }))
rownames(res) <- NULL

# output
res
#               a         ID      desc      pvalue   oddsRatio    expCount
# 1  DDB_G0291275 GO:0006873 geneIds20 0.929591794 0.371461227 2.500314795
# 2  DDB_G0291858 GO:0006873 geneIds21 0.929591794 0.371461227 2.500314795
# 3  DDB_G0292412 GO:0006873 geneIds22 0.929591794 0.371461227 2.500314795
# 4  DDB_G0292564 GO:0006873 geneIds23 0.929591794 0.371461227 2.500314795
# 5  DDB_G0270658 GO:0009725  geneIds1 0.292013941 4.105415861 0.326128017
# 6  DDB_G0280961 GO:0009725  geneIds2 0.292013941 4.105415861 0.326128017
# 7  DDB_G0289445 GO:0009725  geneIds3 0.292013941 4.105415861 0.326128017
# 8  DDB_G0284157 GO:0042981  geneIds1 0.498871327 1.641005803 0.652256034
# 9  DDB_G0285005 GO:0042981  geneIds2 0.498871327 1.641005803 0.652256034
# 10 DDB_G0285251 GO:0042981  geneIds3 0.498871327 1.641005803 0.652256034
# 11 DDB_G0285411 GO:0042981  geneIds4 0.498871327 1.641005803 0.652256034
# 12 DDB_G0289445 GO:0042981  geneIds5 0.498871327 1.641005803 0.652256034
# 13 DDB_G0294046 GO:0042981  geneIds6 0.498871327 1.641005803 0.652256034

Answer 4

[Chirag Parsania]

Tidy way

## input
df1 <- read.table(text = "a   ID  desc
                  DDB_G0284157    GO:0042981  geneIds1
                  DDB_G0285005    GO:0042981  geneIds2
                  DDB_G0285251    GO:0042981  geneIds3
                  DDB_G0285411    GO:0042981  geneIds4
                  DDB_G0289445    GO:0042981  geneIds5
                  DDB_G0294046    GO:0042981  geneIds6
                  0.498871327 GO:0042981  pvalue
                  1.641005803 GO:0042981  oddsRatio
                  0.652256034 GO:0042981  expCount
                  DDB_G0291275    GO:0006873  geneIds20
                  DDB_G0291858    GO:0006873  geneIds21
                  DDB_G0292412    GO:0006873  geneIds22
                  DDB_G0292564    GO:0006873  geneIds23
                  0.929591794 GO:0006873  pvalue
                  0.371461227 GO:0006873  oddsRatio
                  2.500314795 GO:0006873  expCount
                  DDB_G0270658    GO:0009725  geneIds1
                  DDB_G0280961    GO:0009725  geneIds2
                  DDB_G0289445    GO:0009725  geneIds3
                  0.292013941 GO:0009725  pvalue
                  4.105415861 GO:0009725  oddsRatio
                  0.326128017 GO:0009725  expCount", header = TRUE) %>% as_tibble()


## output
library(tidyverse)
out <- df1 %>% 
 spread(key = desc , value = a) %>% ## separate gene ids from other variable 
 gather(key = "vars" , value = "val" , c(expCount,pvalue,oddsRatio)) %>% ## take numeric vars (pvalue ,oddsRatio, expCount) under one column
  gather(key = "desc" , value = "gene_id" , geneIds1:geneIds6)  %>% ##  desc and gene id in separate col
  spread(key = vars , value = val) ## spread numeric vars 

> out 
# A tibble: 30 x 6
   ID         desc      gene_id      expCount    oddsRatio   pvalue     
   <fct>      <chr>     <chr>        <chr>       <chr>       <chr>      
 1 GO:0006873 geneIds1  NA           2.500314795 0.371461227 0.929591794
 2 GO:0006873 geneIds2  NA           2.500314795 0.371461227 0.929591794
 3 GO:0006873 geneIds20 DDB_G0291275 2.500314795 0.371461227 0.929591794
 4 GO:0006873 geneIds21 DDB_G0291858 2.500314795 0.371461227 0.929591794
 5 GO:0006873 geneIds22 DDB_G0292412 2.500314795 0.371461227 0.929591794
 6 GO:0006873 geneIds23 DDB_G0292564 2.500314795 0.371461227 0.929591794
 7 GO:0006873 geneIds3  NA           2.500314795 0.371461227 0.929591794
 8 GO:0006873 geneIds4  NA           2.500314795 0.371461227 0.929591794
 9 GO:0006873 geneIds5  NA           2.500314795 0.371461227 0.929591794
10 GO:0006873 geneIds6  NA           2.500314795 0.371461227 0.929591794
# ... with 20 more rows

[Chirag Parsania]

Tiny update:

NA from gene_id column should be removed. Final code would be

out <- df1 %>% spread(key = desc , value = a) %>% ## separate gene ids from other variable 
  gather(key = "vars" , value = "val" , c(expCount,pvalue,oddsRatio)) %>% ## take numeric vars (pvalue ,oddsRatio, expCount) under one column
  gather(key = "desc" , value = "gene_id" , geneIds1:geneIds6)  %>% ##  desc and gene id in separate col
  spread(key = vars , value = val) %>% filter(!is.na(gene_id))

>out 
# A tibble: 13 x 6
   ID         desc      gene_id      expCount    oddsRatio   pvalue     
   <fct>      <chr>     <chr>        <chr>       <chr>       <chr>      
 1 GO:0006873 geneIds20 DDB_G0291275 2.500314795 0.371461227 0.929591794
 2 GO:0006873 geneIds21 DDB_G0291858 2.500314795 0.371461227 0.929591794
 3 GO:0006873 geneIds22 DDB_G0292412 2.500314795 0.371461227 0.929591794
 4 GO:0006873 geneIds23 DDB_G0292564 2.500314795 0.371461227 0.929591794
 5 GO:0009725 geneIds1  DDB_G0270658 0.326128017 4.105415861 0.292013941
 6 GO:0009725 geneIds2  DDB_G0280961 0.326128017 4.105415861 0.292013941
 7 GO:0009725 geneIds3  DDB_G0289445 0.326128017 4.105415861 0.292013941
 8 GO:0042981 geneIds1  DDB_G0284157 0.652256034 1.641005803 0.498871327
 9 GO:0042981 geneIds2  DDB_G0285005 0.652256034 1.641005803 0.498871327
10 GO:0042981 geneIds3  DDB_G0285251 0.652256034 1.641005803 0.498871327
11 GO:0042981 geneIds4  DDB_G0285411 0.652256034 1.641005803 0.498871327
12 GO:0042981 geneIds5  DDB_G0289445 0.652256034 1.641005803 0.498871327
13 GO:0042981 geneIds6  DDB_G0294046 0.652256034 1.641005803 0.498871327

[cpad0112]

df1 %>% 
    spread(desc,-desc) %>%
    gather(Genes,a,-c(pvalue,ID,oddsRatio,expCount),na.rm = T) %>%
    arrange(ID) %>% 
    select(ID,Genes,a,expCount:pvalue)

           ID     Genes            a    expCount   oddsRatio      pvalue
1  GO:0006873 geneIds20 DDB_G0291275 2.500314795 0.371461227 0.929591794
2  GO:0006873 geneIds21 DDB_G0291858 2.500314795 0.371461227 0.929591794
3  GO:0006873 geneIds22 DDB_G0292412 2.500314795 0.371461227 0.929591794
4  GO:0006873 geneIds23 DDB_G0292564 2.500314795 0.371461227 0.929591794
5  GO:0009725  geneIds1 DDB_G0270658 0.326128017 4.105415861 0.292013941
6  GO:0009725  geneIds2 DDB_G0280961 0.326128017 4.105415861 0.292013941
7  GO:0009725  geneIds3 DDB_G0289445 0.326128017 4.105415861 0.292013941
8  GO:0042981  geneIds1 DDB_G0284157 0.652256034 1.641005803 0.498871327
9  GO:0042981  geneIds2 DDB_G0285005 0.652256034 1.641005803 0.498871327
10 GO:0042981  geneIds3 DDB_G0285251 0.652256034 1.641005803 0.498871327
11 GO:0042981  geneIds4 DDB_G0285411 0.652256034 1.641005803 0.498871327
12 GO:0042981  geneIds5 DDB_G0289445 0.652256034 1.641005803 0.498871327
13 GO:0042981  geneIds6 DDB_G0294046 0.652256034 1.641005803 0.498871327

[Kevin Blighe]

Good work, Chirag!

[Chirag Parsania]

Thanks, Kevin.......!