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?

4 Answers 4

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, 2016 at 14:05
  • Amazing! Thank a lot Sato for the solution. It worked also.
    – HB87
    Jul 18, 2016 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, 2016 at 14:15
  • Right. sed can make one sad. Jul 18, 2016 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, 2016 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, 2016 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, 2016 at 15:17
  • I realised that later, no numbers after the decimal point "."
    – HB87
    Jul 18, 2016 at 16:22
  • I realized that too... I change the script with to print the number as string, should work now.
    – oliv
    Jul 18, 2016 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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .