0

I have a table like this below;

"baseMean"  "log2FoldChange"    "lfcSE" "stat"  "pvalue"    "padj"
"ENSG00000000003.14"    11.3434183210348    0.753849141787545   0.682104979689654   1.10518052826785    0.269081372382168   0.999928163137131
"ENSG00000000419.12"    793.733816508413    -0.256066185652526  0.133681398896401   -1.91549600592503   0.0554292780227467  0.863889514659372
"ENSG00000000457.13"    948.240987147508    -0.088027064401221  0.0869481579436567  -1.01240861776811   0.3113427195966

And I want to delete the quotation marks and .X pattern in 1st column. I mean like this:

"baseMean"  "log2FoldChange"    "lfcSE" "stat"  "pvalue"    "padj"
ENSG00000000003 11.3434183210348    0.753849141787545   0.682104979689654   1.10518052826785    0.269081372382168   0.999928163137131
ENSG00000000419 793.733816508413    -0.256066185652526  0.133681398896401   -1.91549600592503   0.0554292780227467  0.863889514659372
ENSG00000000457 948.240987147508    -0.088027064401221  0.0869481579436567  -1.01240861776811   0.3113427195966

In this case, how can I write command line using sed or whatever?

0
0

Using sed:

$ echo \"ENSG00000009694.13\" 3.25851232080741 0.670268379884225 | sed -E "s|\"(.+?)\.[0-9]*\"|\1|g"
ENSG00000009694 3.25851232080741 0.670268379884225

I guess what you are dealing with is a huge log file. In this case, you can use

$ sed -E "s|\"(.+?)\.[0-9]*\"|\1|g" your_file.txt
"baseMean"  "log2FoldChange"    "lfcSE" "stat"  "pvalue"    "padj"
ENSG00000000003    11.3434183210348    0.753849141787545   0.682104979689654   1.10518052826785    0.269081372382168   0.999928163137131

and sed will print the result. You can also add a -i in front of -E; -i stands for "in place mode" so sed will directly modify your file.

Explanation:

I am using the "find and replace" function of sed. The basic grammar is

sed -E "s|p1|p2|g"

and then sed will replace p1 with p2. -E stand for "extended regex mode" so p1 can be some complicated regex.

My p1 here is (omit ( and ) for now)

\".+?\.[0-9]*\"

in which

  • \" maches the quotation marks,
  • \.[0-9]* maches patterns starting with a dot and have 0 to infinity numbers in the following and
  • .+? maches any patterns in the middle.

Then p2 is simply a \1 which means the string between the first ( and ) pair in p1. And it is done!

5
  • Note that .+? is a PCRE non-greedy match. Fro whatever reason, GNU sed seems to accept this illegal extended regular expression. You don't need the ? after .+ as you match a literal double quote later. There is also no real reason to change the default delimiters for the s command here, or to use extended regular expressions.
    – they
    Nov 25 '21 at 20:26
  • Yes, the ? is only a habit of Python regex. I myself think this is a good habit because it reduces many mistakes. The reason for changing the default delimiter from / to | is because this is much easy to read. Without -E, the sed on my computer throughs out an error sed: -e expression #1, char 22: invalid reference \1 on `s' command's RHS. So I think this is another good habit to stick to -E.
    – Youran
    Nov 26 '21 at 5:02
  • You get that error without -E because a capture group is written \(...\) without -E. Note too that -E is non-standard.
    – they
    Nov 26 '21 at 6:52
  • Yes, without -E there will be a lot of \, and very hard to read as a result. So it is better to always use -E.
    – Youran
    Nov 26 '21 at 9:17
  • My choices have their reason and they are the best based on my experience.
    – Youran
    Nov 26 '21 at 9:22
0
awk 'NR>1{gsub(/\"/,"",$0);gsub(/\.[0-9]*/,"",$1)}1' filename

output

"baseMean"  "log2FoldChange"    "lfcSE" "stat"  "pvalue"    "padj"
ENSG00000000003 11.3434183210348 0.753849141787545 0.682104979689654 1.10518052826785 0.269081372382168 0.999928163137131
ENSG00000000419 793.733816508413 -0.256066185652526 0.133681398896401 -1.91549600592503 0.0554292780227467 0.863889514659372
ENSG00000000457 948.240987147508 -0.088027064401221 0.0869481579436567 -1.01240861776811 0.3113427195966
0
$ sed 's/^"\(ENS[[:alnum:]]*\)\..*"/"\1"/' file
"baseMean"  "log2FoldChange"    "lfcSE" "stat"  "pvalue"    "padj"
"ENSG00000000003"    11.3434183210348    0.753849141787545   0.682104979689654   1.10518052826785    0.269081372382168   0.999928163137131
"ENSG00000000419"    793.733816508413    -0.256066185652526  0.133681398896401   -1.91549600592503   0.0554292780227467  0.863889514659372
"ENSG00000000457"    948.240987147508    -0.088027064401221  0.0869481579436567  -1.01240861776811   0.3113427195966

The sed command above removes the version from the Ensembl stable IDs in the first column only. It does this by means of matching "ENS at the start o the line, followed by any number of alphanumerical characters, a dot and some non-quote character. The dot and the non-quote characters are discarded using a substitution.

To remove the double quotes as well, change the replacement text from "\1" to just \1.

If you are certain that there are always a version attached to the stable IDs, you may shorten this to the following expression, which just removes the first substring between a dot and a double quote:

$ sed 's/\..*"/"/' file
"baseMean"  "log2FoldChange"    "lfcSE" "stat"  "pvalue"    "padj"
"ENSG00000000003"    11.3434183210348    0.753849141787545   0.682104979689654   1.10518052826785    0.269081372382168   0.999928163137131
"ENSG00000000419"    793.733816508413    -0.256066185652526  0.133681398896401   -1.91549600592503   0.0554292780227467  0.863889514659372
"ENSG00000000457"    948.240987147508    -0.088027064401221  0.0869481579436567  -1.01240861776811   0.3113427195966

Would you need to delete the quotes you could do that with the expression 2,$ s/"//g, e.g.,

sed -e 's/\..*"/"/' -e '2,$ s/"//g' file

Or, you could just pass everything through tr -d '"' to remove all quotes.

sed 's/\..*"/"/' file | tr -d '"'
4
  • From the comments under the question (which have been deleted now), we know this is a new-to-Linux user who does not know what is echo. So I think my explanations are much fresh-bird-friendly. And as I have replied to you, every choice has its reason.
    – Youran
    Nov 26 '21 at 5:08
  • @Youran I'm not quite sure what you are commenting on now. My answer does not use echo. It is clear that the user is a bioinformatician (possibly a student, maybe not) working with human genomic data. Such data is often large and stored on files on disk, which is why I assume that data is read from file.
    – they
    Nov 26 '21 at 6:54
  • My answer used echo to show the regex works. Then the author asked me what is echo. This was in the comments under the question (the comments are deleted now). We should be careful when asking newcomers questions. We should be user-friendly and explain everything patiently. I am very sure that he cannot understand your answer.
    – Youran
    Nov 26 '21 at 9:21
  • @Youran The user asked why you used echo, not what echo was.
    – they
    Nov 26 '21 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.