4

I have the following file named OpenSimStats.txt:

TestreportsRootAgentCount=0agent(s)
TestreportsChildAgentCount=0childagent(s)
TestreportsGCReportedMemory=10MB(Global)
TestreportsTotalObjectsCount=0Object(s)
TestreportsTotalPhysicsFrameTime=0ms
TestreportsPhysicsUpdateFrameTime=0ms
TestreportsPrivateWorkingSetMemory=2144MB(Global)
TestreportsTotalThreads=0Thread(s)(Global)
TestreportsTotalFrameTime=89ms
TestreportsTotalEventFrameTime=0ms
TestreportsLandFrameTime=0ms
TestreportsLastCompletedFrameAt=25msago
TestreportsTimeDilationMonitor=1
TestreportsSimFPSMonitor=55.3333320617676
TestreportsPhysicsFPSMonitor=55.4766654968262
TestreportsAgentUpdatesPerSecondMonitor=0persecond
TestreportsActiveObjectCountMonitor=0
TestreportsActiveScriptsMonitor=0
TestreportsScriptEventsPerSecondMonitor=0persecond
TestreportsInPacketsPerSecondMonitor=0persecond
TestreportsOutPacketsPerSecondMonitor=0persecond
TestreportsUnackedBytesMonitor=0
TestreportsPendingDownloadsMonitor=0
TestreportsPendingUploadsMonitor=0
TestreportsTotalFrameTimeMonitor=18.18239402771ms
TestreportsNetFrameTimeMonitor=0ms
TestreportsPhysicsFrameTimeMonitor=0.0106373848393559ms
TestreportsSimulationFrameTimeMonitor=0.17440040409565ms
TestreportsAgentFrameTimeMonitor=0ms
TestreportsImagesFrameTimeMonitor=0ms
TestreportsSpareFrameTimeMonitor=18.1818199157715ms
TestreportsLastReportedObjectUpdates=0
TestreportsSlowFrames=1

I want to transform this file into a CSV file like the following:

TestreportsRootAgentCount,TestreportsChildAgentCount,...,TestreportsSlowFrames
0,0,10,0,0...,1

By which I mean:

  1. take out all words before and after a delimiter in this case the delimeter is "="
  2. Put all words on the left of the delimeter in one line separated by commas
  3. Insert a new line at the end
  4. Then put whatever after the delimiter (=) - the numbers only (without the units or characters after the numbers) in another line where these numbers are separated by commas.
  5. Then insert a new line

Any ideas/suggestions on how this can be done in Linux shell scripting? By using sed or gawk?

9

The 9 paths to OpenSim enlightenment:

With sed and some shell magic:

sed 's/=.*//' OpenSimStats.txt | paste -sd, >out.csv
sed 's/.*=//; s/[^0-9]*$//' OpenSimStats.txt | paste -sd, >>out.csv

With sed, without shell magic:

sed -n 's/=.*//; 1{ h; b; }; $! H; $ { x; s/\n/,/g; p; }' OpenSimStats.txt >out.csv
sed -n 's/.*=//; 1{ s/[0-9]*$//; h; b; }; s/[^0-9]*$//; $! H; $ { x; s/\n/,/g; p; }' OpenSimStats.txt >>out.csv

With shell magic and a tiny bit of sed:

paste -sd, <(cut -d= -f1 OpenSimStats.txt) <(cut -d= -f2 OpenSimStats.txt | sed 's/[^0-9]*$//')

With cut and some shell magic:

cut -d= -f1 OpenSimStats.txt | paste -sd, >out.csv
cut -d= -f2 OpenSimStats.txt | sed 's/[^0-9]*$//' | paste -sd, >>out.csv

With GNU datamash:

sed 's/=/,/; s/[^0-9]*$//' OpenSimStats.txt | datamash -t, transpose

With perl:

perl -lnE 's/\D+$//o;
    ($a, $b) = split /=/;
    push @a, $a; push @b, $b;
    END { $, = ","; say @a; say @b }' OpenSimStats.txt

With grep:

grep -o '^[^=]*' OpenSimStats.txt | paste -sd, >out.csv
egrep -o '[0-9.]+' OpenSimStats.txt | paste -sd, >>out.csv

With bash:

#! /usr/bin/env bash
line1=()
line2=()
while IFS='=' read -r a b; do
    line1+=("$a")
    [[ $b =~ ^[0-9.]+ ]]
    line2+=("$BASH_REMATCH")
done <OpenSimStats.txt
( set "${line1[@]}"; IFS=,; echo "$*" ) >out.csv
( set "${line2[@]}"; IFS=,; echo "$*" ) >>out.csv

With awk:

awk -F= '
    NR==1 { a = $1; sub(/[^0-9]+$/, "", $2); b = $2; next }
    { a = a "," $1; sub(/[^0-9]+$/, "", $2); b = b "," $2 }
    END { print a; print b }' OpenSimStats.txt

Bonus 10th path for data nerds, with csvtk:

csvtk replace -d= -f 2 -p '\D+$' -r '' <OpenSimStats.txt | csvtk transpose

Bonus 11th path with vim:

:%s/\D*$//
:%s/=/\r/
qaq
:g/^\D/y A | normal dd
:1,$-1 s/\n/,/
"aP
:2,$-2 s/\n/,/
:d 1
:w out.csv
4
  • Your second solution adds an extra , to the beginning of each output line. Also, it would be clearer if you explained that the two commands need to be run in sequence with the first redirected to a file and the second appended to it.
    – terdon
    Jul 18 '16 at 14:05
  • Amazing! Thank a lot Sato for the solution. It worked also.
    – HB87
    Jul 18 '16 at 14:06
  • Your magic-less solution still fails. It prints 0agent(s) as the first field of the second line for me. +1 for the other two though.
    – terdon
    Jul 18 '16 at 14:15
  • Right. sed can make one sad. Jul 18 '16 at 14:18
8

awk to help you:

awk -F= '{a[NR,1]=$1;a[NR,2]=$2}
         END{
            for(i=1; i<NR; i++){
                printf a[i,1] ","
            }
            print a[i,1]; 
            for(i=1; i<NR; i++){
                printf "%s", a[i,2]+0
            } 
            print a[i,2];
        }' file

The array a is filled with both key $1 in the first column and value $2 in the second column.

When all lines are read, loop twice through all elements of the arrays to show both key and values.

5
  • Brilliant! a thousand thank you! I tried it and it works like a charm!
    – HB87
    Jul 18 '16 at 14:03
  • How did you strip the unwanted characters from the second argument? And does this leave an extra ',' at the end of each line? Jul 18 '16 at 14:50
  • 1
    @EdwardFalk The %d is stripping out the characters of the second argument as it expects a decimal number. There isn't any trailing coma because both loops goes until the n-1 element, then the last print show the last element.
    – oliv
    Jul 18 '16 at 15:17
  • I realised that later, no numbers after the decimal point "."
    – HB87
    Jul 18 '16 at 16:22
  • I realized that too... I change the script with to print the number as string, should work now.
    – oliv
    Jul 18 '16 at 16:54
3

Here's a Perl solution:

$ perl -F= -lae '$F[1]=~s/[^0-9]//g; push @h,$F[0]; push @l,$F[1]; 
                  END{print join ",",@h; print join ",",@l}' OpenSimStats.txt 
TestreportsRootAgentCount,TestreportsChildAgentCount,TestreportsGCReportedMemory,TestreportsTotalObjectsCount,TestreportsTotalPhysicsFrameTime,TestreportsPhysicsUpdateFrameTime,TestreportsPrivateWorkingSetMemory,TestreportsTotalThreads,TestreportsTotalFrameTime,TestreportsTotalEventFrameTime,TestreportsLandFrameTime,TestreportsLastCompletedFrameAt,TestreportsTimeDilationMonitor,TestreportsSimFPSMonitor,TestreportsPhysicsFPSMonitor,TestreportsAgentUpdatesPerSecondMonitor,TestreportsActiveObjectCountMonitor,TestreportsActiveScriptsMonitor,TestreportsScriptEventsPerSecondMonitor,TestreportsInPacketsPerSecondMonitor,TestreportsOutPacketsPerSecondMonitor,TestreportsUnackedBytesMonitor,TestreportsPendingDownloadsMonitor,TestreportsPendingUploadsMonitor,TestreportsTotalFrameTimeMonitor,TestreportsNetFrameTimeMonitor,TestreportsPhysicsFrameTimeMonitor,TestreportsSimulationFrameTimeMonitor,TestreportsAgentFrameTimeMonitor,TestreportsImagesFrameTimeMonitor,TestreportsSpareFrameTimeMonitor,TestreportsLastReportedObjectUpdates,TestreportsSlowFrames
0,0,10,0,0,0,2144,0,89,0,0,25,1,553333320617676,554766654968262,0,0,0,0,0,0,0,0,0,1818239402771,0,00106373848393559,017440040409565,0,0,181818199157715,0,1

The -a flag makes perl act like awk and split each input line on the field delimiter given by -F (an = here) into the array @F. The -l adds a \n to each print call and the -e is the script that will be run on each line.

  • $F[1]=~s/[^0-9]//g; : remove any non-numerical characters from the 2nd field (arrays start counting at 0 so $F[1] is the second field).
  • push @h,$F[0]; push @l,$F[1]; : push the first field into the array @h and the second (now that the non-digits have been removed) into the array @l.
  • END{} : this is executed once, after the entire input file has been processed.
  • print join ",",@h; : join the @h array with , and print it.
  • print join ",",@l : as above but for @l.
1

I know this question's been answered, I figured I'd just add my two cents as to how to accomplish this in a (ugly) bash one-liner

End Result

echo -e $(cut -d"=" -f1 OpenSimStats.txt | tr '\n' ',' | sed 's/,$/\\n/')$(sed -r 's/.*=([0-9]*).*/\1,/g' OpenSimStats.txt | tr -d '\n' | sed 's/,$//')

This will produce the following result you asked for in one line:

TestreportsRootAgentCount,TestreportsChildAgentCount,TestreportsGCReportedMemory,TestreportsTotalObjectsCount,TestreportsTotalPhysicsFrameTime,TestreportsPhysicsUpdateFrameTime,TestreportsPrivateWorkingSetMemory,TestreportsTotalThreads,TestreportsTotalFrameTime,TestreportsTotalEventFrameTime,TestreportsLandFrameTime,TestreportsLastCompletedFrameAt,TestreportsTimeDilationMonitor,TestreportsSimFPSMonitor,TestreportsPhysicsFPSMonitor,TestreportsAgentUpdatesPerSecondMonitor,TestreportsActiveObjectCountMonitor,TestreportsActiveScriptsMonitor,TestreportsScriptEventsPerSecondMonitor,TestreportsInPacketsPerSecondMonitor,TestreportsOutPacketsPerSecondMonitor,TestreportsUnackedBytesMonitor,TestreportsPendingDownloadsMonitor,TestreportsPendingUploadsMonitor,TestreportsTotalFrameTimeMonitor,TestreportsNetFrameTimeMonitor,TestreportsPhysicsFrameTimeMonitor,TestreportsSimulationFrameTimeMonitor,TestreportsAgentFrameTimeMonitor,TestreportsImagesFrameTimeMonitor,TestreportsSpareFrameTimeMonitor,TestreportsLastReportedObjectUpdates,TestreportsSlowFrames
0,0,10,0,0,0,2144,0,89,0,0,25,1,55,55,0,0,0,0,0,0,0,0,0,18,0,0,0,0,0,18,0,1

Breakdown of Code

Here's the breakdown of what's happening.

Outer Echo

echo -e $()$()

Echo the results of two nested commands which are executed first, the -e is so that printed \n will translate to newlines in the final result.

First Command

cut -d"=" -f1 OpenSimStats.txt | tr '\n' ',' | sed 's/,$/\\n/'

The first nested command. Use = as a delimiter to extract all the text as a column (series of \n-separated values). Replace all \n with ,, then replace the final comma with a \n again (otherwise the last value will be followed by a comma).

This command by itself produces the following output:

TestreportsRootAgentCount,TestreportsChildAgentCount,TestreportsGCReportedMemory,TestreportsTotalObjectsCount,TestreportsTotalPhysicsFrameTime,TestreportsPhysicsUpdateFrameTime,TestreportsPrivateWorkingSetMemory,TestreportsTotalThreads,TestreportsTotalFrameTime,TestreportsTotalEventFrameTime,TestreportsLandFrameTime,TestreportsLastCompletedFrameAt,TestreportsTimeDilationMonitor,TestreportsSimFPSMonitor,TestreportsPhysicsFPSMonitor,TestreportsAgentUpdatesPerSecondMonitor,TestreportsActiveObjectCountMonitor,TestreportsActiveScriptsMonitor,TestreportsScriptEventsPerSecondMonitor,TestreportsInPacketsPerSecondMonitor,TestreportsOutPacketsPerSecondMonitor,TestreportsUnackedBytesMonitor,TestreportsPendingDownloadsMonitor,TestreportsPendingUploadsMonitor,TestreportsTotalFrameTimeMonitor,TestreportsNetFrameTimeMonitor,TestreportsPhysicsFrameTimeMonitor,TestreportsSimulationFrameTimeMonitor,TestreportsAgentFrameTimeMonitor,TestreportsImagesFrameTimeMonitor,TestreportsSpareFrameTimeMonitor,TestreportsLastReportedObjectUpdates,TestreportsSlowFrames

Second Command

sed -r 's/.*=([0-9]*).*/\1,/g' OpenSimStats.txt | tr -d '\n' | sed 's/,$//'

The second nested command. Remove all text surrounding the desired number on each line, resulting in a column of numbers (\n-separated values). Replace all \n with ,, then remove the trailing comma.

This produces the following result:

0,0,10,0,0,0,2144,0,89,0,0,25,1,55,55,0,0,0,0,0,0,0,0,0,18,0,0,0,0,0,18,0,1

The one-liner up top just combines these three pieces into a single line to produce the end result.

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.