4

I want to create a .csv file from a program output. I have tailored some of the original output to this level.

  36.343074719185125    -1.488697037254009     0.517768286726280  -1.488697037254009    48.906350248447872    -0.255764113311881   0.51776828
6726280    -0.255764113311881    31.687963239227631

I have used these commands till now,

tail -12 Q.out | head -3 | sed 's/^........//' | tr -d '\n' > q.txt

The problem is there is no specific number of whitespaces in between. Sometime there are 4 or sometime 3. This all depend on the output of the program. For instance, if the first value is 36.343 then there are two space at the front, if it is 3.6 there are three spaces, if it is 360.34 then one space. Is there any way to make it cleaner?

Original Data


                   0                     1                     2        

    0     36.343074719185125    -1.488697037254009     0.517768286726280
    1     -1.488697037254009    48.906350248447872    -0.255764113311881
    2      0.517768286726280    -0.255764113311881    31.687963239227631

    alpha_(0.000) =      38.979129402287 a.u.
FCHKWriter: !WARNING! method 'CCSD'' renamed to label 'CC'.
FCHKWriter: Writing 0100-A_ccsd.fchk with label ' CC Density'.

    Psi4 stopped on: Tuesday, 12 October 2021 04:09PM
    Psi4 wall time for execution: 0:17:43.19

*** Psi4 exiting successfully. Buy a developer a beer!

Expected result

36.343074719185125,-1.488697037254009,0.517768286726280,-1.488697037254009,48.906350248447872,-0.255764113311881,0.51776828
6726280,-0.255764113311881,31.687963239227631

I am using UBUNTU 20.04

5
  • Do you want to make one or more spaces into a comma? Please elaborate some more.
    – FelixJN
    Oct 12 at 16:16
  • 1
    I have added both original data and expected results. I want that 1. if there are more than one spaces they be squeezed into one, 2. That one space be transformed to comma (by simple sed). @FelixJN and they Oct 12 at 16:22
  • Please edit your question and add what operating system you are using. The tools available depend on that information.
    – terdon
    Oct 12 at 16:41
  • @terdon, OS is added. Oct 12 at 16:53
  • Your input data consists of three rows of three values. Your output data consists of two rows, the first with seven values and the second with three. What's going on?
    – roaima
    Oct 13 at 7:30

10 Answers 10

8

The following assumes that the three lines of data that we're interested in are lines 4, 5, and 6 of the original data, and that you want all those number as a comma-delimit list on a single line.

The sed expression below deletes all lines that are not in the wanted range of lines, and then deletes the first column containing only digits. The tr command reads this and transforms the data into a list of numbers, with one number per line. These newline-delimited numbers are then transformed into a comma-delimited list by paste.

sed -e '4,6 !d' \
    -e 's/[[:blank:]]*[[:digit:]]*[[:blank:]]*//' file |
tr -s ' ' '\n' |
paste -s -d, -

You could get rid of tr by letting sed replace repeated blanks by single commas.

sed -e '4,6 !d' \
    -e 's/[[:blank:]]*[[:digit:]]*[[:blank:]]*//' \
    -e 's/[[:blank:]]\{1,\}/,/g' file |
paste -s -d, -
2
  • where do I put input and output file name? Oct 12 at 16:39
  • 3
    @PrasantaBandyopadhyay In the code in my answer, I use the filename file. The result is written to standard output, so you should be able to redirect it to a new file by adding > newfile at the very end.
    – they
    Oct 12 at 16:40
7

is very good at handling whitespace-separated data. Like @they, assuming the 3 lines you're interested in are lines 4,5,6

awk -v OFS=, '4 <= NR && NR <= 6 {print $2,$3,$4}' Q.out

outputs

36.343074719185125,-1.488697037254009,0.517768286726280
-1.488697037254009,48.906350248447872,-0.255764113311881
0.517768286726280,-0.255764113311881,31.687963239227631

Then, to join that into one line, use paste

awk -v OFS=, '4 <= NR && NR <= 6 {print $2,$3,$4}' Q.out | paste -sd,

The data is in the last lines 10-12 of the file. So let's reverse the file, extract the data from the known line range, re-reverse the data, and then join.

tac Q.out \
| awk -v OFS=, '10 <= NR && NR <= 12 {print $2,$3,$4}' \
| tac \
| paste -sd,
36.343074719185125,-1.488697037254009,0.517768286726280,-1.488697037254009,48.906350248447872,-0.255764113311881,0.517768286726280,-0.255764113311881,31.687963239227631
2
  • The output file length can vary. So, what you have assumed at line 4,5,6 can also be line 7,8,9. However, the line number will not vary if taken from the last. In this sense, the results are found in last 12 - 10 lines. Can you solution be modified with this specification? Oct 12 at 16:57
  • 2
    @PrasantaBandyopadhyay Clarifications of the question should go in the question itself, not in a comment attached to one of the answers.
    – they
    Oct 13 at 6:09
6

You can use awk for processing the data file. Looking at your example it seems I may be able to assume four fields per line, with the first a non-negative integer:

awk 'NF == 4 && $1 ~ /^[0-9]+$/ {printf "%s,%s,%s\n", $2, $3, $4}' Q.out

Result

36.343074719185125,-1.488697037254009,0.517768286726280
-1.488697037254009,48.906350248447872,-0.255764113311881
0.517768286726280,-0.255764113311881,31.687963239227631

I'm seeing various comments that you want all the data on a single line. This is not stated in your question, where you confusingly have two irregular lines of output, but I'm going to address it anyway.

Here, we can modify the original awk statement to add subsequent data rows to the initial line, and then append a trailing newline:

awk '
    NF == 4 && $1 ~ /^[0-9]+$/ {printf "%s%s,%s,%s", s, $2, $3, $4; s=","}
    END {print ""}
' Q.out
0
4

A different approach. Here, we assume that the lines we are interested in are those that end with at least 5 digits:

$ grep -P '\d{5,}$' Q.out
    0     36.343074719185125    -1.488697037254009     0.517768286726280
    1     -1.488697037254009    48.906350248447872    -0.255764113311881
    2      0.517768286726280    -0.255764113311881    31.687963239227631

This is then passed to perl which will slurp the whole dataset into memory (-007) and find sets of numbers, - and . that are at least 6 characters in length, and print the resulting list of strings joined by a comma:

$ grep -P '\d{5,}$' Q.out | perl -007 -nE 'say join ",",(/[-\d.]{6,}/g)' 
36.343074719185125,-1.488697037254009,0.517768286726280,-1.488697037254009,48.906350248447872,-0.255764113311881,0.517768286726280,-0.255764113311881,31.687963239227631
3

Using any awk in any shell in every Unix box and assuming you have multiple such blocks of indeterminate length in your input that you want converted to CSV:

$ cat tst.awk
BEGIN { OFS="," }
(NF==4) && sub(/^ +[0-9]+ +/,"") {
    $1 = $1
    rec = (rec == "" ? "" : rec OFS) $0
}
!NF && (rec != "") {
    print rec
    rec = ""
}

$ awk -f tst.awk file
36.343074719185125,-1.488697037254009,0.517768286726280,-1.488697037254009,48.906350248447872,-0.255764113311881,0.517768286726280,-0.255764113311881,31.687963239227631
2

Using Raku (formerly known as Perl_6):

raku -ne '.words.put if .trim-leading.match(/^^\d+ \s+/);' 

Sample Input:

                   0                     1                     2        

    0     36.343074719185125    -1.488697037254009     0.517768286726280
    1     -1.488697037254009    48.906350248447872    -0.255764113311881
    2      0.517768286726280    -0.255764113311881    31.687963239227631

    alpha_(0.000) =      38.979129402287 a.u.
FCHKWriter: !WARNING! method 'CCSD'' renamed to label 'CC'.
FCHKWriter: Writing 0100-A_ccsd.fchk with label ' CC Density'.

    Psi4 stopped on: Tuesday, 12 October 2021 04:09PM
    Psi4 wall time for execution: 0:17:43.19

*** Psi4 exiting successfully. Buy a developer a beer!

Sample Output (1):

0 1 2
0 36.343074719185125 -1.488697037254009 0.517768286726280
1 -1.488697037254009 48.906350248447872 -0.255764113311881
2 0.517768286726280 -0.255764113311881 31.687963239227631

Sometimes the easiest way to approach these sorts of problems is successive approximation. You didn't give us a definitive description of the data lines you were interested in, so the Raku code above assumes you want lines starting with an Integer column (neglecting preceding whitespace).

As you can see, the Raku code breaks your data into whitespace-separated words, and the 3-or-4 data lines are correctly plucked out of the ~14 line Sample Input you provided. From here removing the initial (non-data?) Integer column should be an easy matter.

For the sake of completeness, the Raku code provided below gets you from "Sample Output 1" to "Sample Output 2":

raku -e 'lines.grep(/ \.\d+ / ).map(*.split(/ ^\d+\s /)).words.join(",").put;'

Sample Output (2):

36.343074719185125,-1.488697037254009,0.517768286726280,-1.488697037254009,48.906350248447872,-0.255764113311881,0.517768286726280,-0.255764113311881,31.687963239227631

https://raku.org

1

Here is a smaller awk implementation:

awk '/^ +[0-2] /&&NF==4{printf("%s%s,%s,%s", C, $2, $3, $4);C=","}END{print X}' file

The /^ +[0-2] / portion recognizes lines with just a single 0..2 digit in the first field;

&&NF==4 further only selects lines with 4 fields -- so it strips out the header with 0 1 2;

The printf() prints the values, with a variable C which is initially blank (default in AWK), and then is set to a "," in subsequent lines. Thus you don't end with a trailing "," at the last line;

The END statement does add a newline at the final output, so all results are on a single line.

1

You were almost there. Modifying slightly your solution we can do as follows. Note this assumes GNU sed.

tail -12 Q.out | head -3 | 
sed -Ee 's/\S+//;s/^\s+|\s+$//g;s/\s+/,/g'| 
paste -sd, - > q.txt

Y

1

If you want within sed only, here is one way with GNU sed. The idea is to carry at any time 12 lines in the pattern space and as you approach the eof, perform a FIFO operation on the pattern space. Meaning, remove lines from the top of and append next line at the end.

m=12 n=3
p=$(((m-1)*2))

sed -Ee "
  1,${p}N;\$!{N;D;}
  s/^(([^\n]*\n){$n}).*/\1/
  s/^\s*\S+\s+//Mg
  s/^\s*|\s*\$//g;s/\s+/,/g
" Q.out > q.txt
1

Python is overkill here (versus awk) but perhaps more familiar?

python -c '

import sys
for line in sys.stdin.readlines():
      print(", ".join( line.split() ) )
'

or

python -c '
try:
  while True:
    print( ", ".join( input().split() ))
except EOFError:
  pass
'

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.