4

How can I get the details and transpose it to horizontal form?

Every record ends after Couse. Couse will never be blank or null.

Note: These four headers will be there for the below data: Name, City, Age, Couse

If you see the second record, there isn't any "Name": "" -> missing so it should be null in place of that and the remaining will be appended after that with a pipe separated like this: null | Ors | 11 | MB

I have data like below in the demo.txt file

"Name":"asxadadad  ,aaf dsf"
"City":"Mum"
"Age":"23"
"Couse":"BBS"
"City":"Ors"
"Age":"11"
"Couse":"MB"
"Name":"adad sf"
"City":"Kol"
"Age":"21"
"Couse":"BB"
"Name":"pqr"
"Age":"21"
"Couse":"NN"

Expected Output:

asxadadad  ,aaf dsf | Mum  | 23 | BBS
null                | Ors  | 11 | MB
adad sf             | Kol  | 21 | BB
pqr                 | null | 21 | NN

I tried the below code: but not working my logic

counter=0
var_0='Couse'

 while read -r line

   echo "$line"

   counter=$(( counter + 1 ))

   var_1=`echo "$line" | grep -oh "Couse"`

   if [ $var_0 == $var_1 ]
   then
        head -$counter demo.txt > temp.txt
        sed -i '1,$counter' demo.txt
        counter = 0
   else
        echo "No thing to do"
   fi

 done < demo.txt
1
  • 1
    The data looks as if it at some point in time might have been a JSON document. If you still have the original document, it would be easier and safer to work with that directly rather than with a pre-chewed variant of the data.
    – Kusalananda
    Jun 27 at 21:05
4

Using any awk in any shell on every Unix box:

$ cat tst.awk
BEGIN {
    numTags = split("Name City Age Couse",nums2tags)
    for (tagNr=1; tagNr<=numTags; tagNr++) {
        tag = nums2tags[tagNr]
        tags2nums[tag] = tagNr
        wids[tagNr] = ( length(tag) > length("null") ? length(tag) : length("null") )
    }
    OFS=" | "
}
(NR==1) || (prevTag=="Couse") {
    numRecs++
}
{
    gsub(/^"|"$/,"")
    tag = val = $0
    sub(/".*/,"",tag)
    sub(/[^"]+":"/,"",val)

    tagNr = tags2nums[tag]
    vals[numRecs,tagNr] = val

    wid = length(val)
    wids[tagNr] = ( wid > wids[tagNr] ? wid : wids[tagNr] )

    prevTag = tag
}
END {
    # Uncomment these 3 lines if youd like a header line printed:
    # for (tagNr=1; tagNr<=numTags; tagNr++) {
    #   printf "%-*s%s", wids[tagNr], nums2tags[tagNr], (tagNr<numTags ? OFS : ORS)
    # }

    for (recNr=1; recNr<=numRecs; recNr++) {
        for (tagNr=1; tagNr<=numTags; tagNr++) {
            val = ( (recNr,tagNr) in vals ? vals[recNr,tagNr] : "null" )
            printf "%-*s%s", wids[tagNr], val, (tagNr<numTags ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
asxadadad  ,aaf dsf | Mum  | 23  | BBS
null                | Ors  | 11  | MB
adad sf             | Kol  | 21  | BB
pqr                 | null | 21  | NN

or if you didn't want to use a hard-coded list of tags (field/column names):

$ cat tst.awk
BEGIN { OFS=" | " }
(NR==1) || (prevTag=="Couse") {
    numRecs++
}
{
    gsub(/^"|"$/,"")
    tag = val = $0
    sub(/".*/,"",tag)
    sub(/[^"]+":"/,"",val)

    if ( !(tag in tags2nums) ) {
        tagNr = ++numTags
        tags2nums[tag] = tagNr
        nums2tags[tagNr] = tag
        wids[tagNr] = ( length(tag) > length("null") ? length(tag) : length("null") )
    }

    tagNr = tags2nums[tag]
    vals[numRecs,tagNr] = val

    wid = length(val)
    wids[tagNr] = ( wid > wids[tagNr] ? wid : wids[tagNr] )

    prevTag = tag
}
END {
    for (tagNr=1; tagNr<=numTags; tagNr++) {
        printf "%-*s%s", wids[tagNr], nums2tags[tagNr], (tagNr<numTags ? OFS : ORS)
    }

    for (recNr=1; recNr<=numRecs; recNr++) {
        for (tagNr=1; tagNr<=numTags; tagNr++) {
            val = ( (recNr,tagNr) in vals ? vals[recNr,tagNr] : "null" )
            printf "%-*s%s", wids[tagNr], val, (tagNr<numTags ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
Name                | City | Age | Couse
asxadadad  ,aaf dsf | Mum  | 23  | BBS
null                | Ors  | 11  | MB
adad sf             | Kol  | 21  | BB
pqr                 | null | 21  | NN

Note that the order of the columns in the output for that second script will be the order those tags appear in the input which is why they need a header row to identify the values unless all tags are guaranteed to occur in the input in the order you want them output.

0
4

In perl. I'd add more explanations of what it does/how it works, but I think the comments in the code cover all of that.

#!/usr/bin/perl

use strict;

my @people; # Array-of-Arrays (AoA) to hold each record
my %person; # hash to hold the current record as it's being read in.

# list of valid field names, in the order you want them printed
my @names   = qw(Name City Age Couse);
my $end_key = 'Couse';

# build a regex from the valid names
my $names    = join('|',@names);
my $names_re = qr/^(?:$names)$/;

# Initialise field widths, with a minimum of 4 (for 'null').
my %widths = map {$_ => (length > 4 ? length : 4) } @names;

while(<>) {
  chomp;

  s/^"|"$//g;                       # strip leading and trailing quotes
  my ($key,$val) = split /"?:"?/;   # split on :, with optional quotes.

  if ($key =~ m/$names_re/) {
    $widths{$key} = length($val) if ($widths{$key} < length($val) );

    $person{$key} = $val;

    if ($key eq $end_key) {
      # push an array into the @people array, containing the values of
      # the valid fields, in order.  Use null as the default value
      # if any field is empty/undefined.
      push @people, [ map { $person{$_} || 'null' } @names ];
      %person = ();
    };
  } else {
    print STDERR "Error on input line $.: unrecognised data\n";
  };
};

# build a printf format string, using the longest width of each field.
my $fmt = join(' | ', map { "%-$widths{$_}s" } @names) . "\n";

# optional header line, comment out if not wanted
printf $fmt, @names;

# optional ruler line, comment out if not wanted
print join('-|-', map { '-' x $widths{$_} } @names) . "\n";

foreach my $p (@people) {
  printf $fmt, @{ $p };
}

Save as, e.g., columns.pl, and make executable with chmod +x.

Output:

$ chmod +x columns.pl 
$ ./columns.pl demo.txt 
Name                | City | Age  | Couse
--------------------|------|------|------
asxadadad  ,aaf dsf | Mum  | 23   | BBS 
null                | Ors  | 11   | MB  
adad sf             | Kol  | 21   | BB  
pqr                 | null | 21   | NN  
3

A short GNU awk compatible (for the RS defined as regex) solution and works apart from where the tags are located within a block of records and we follows the tags order from the input when it's come to printing; and the last tag Couse is the record end identifier:

<infile awk -F'\n' -v tags='Name,City,Age,Couse' '
BEGIN{ tagsNum=split(tags, tgs, ","); RS="\n?\""tgs[tagsNum]"\":[^\n]*\n" }

function tbl(tag, field) {
    if(index(field, "\""tag"\"")==1 && !key[tag]++ || field==RT){
        gsub(/(^[^:]*:"|"\n?)/, "", field)
        key[tag]=field
    }
}
{ for(i=1; i<=NF; i++){ for(k in tgs) tbl(tgs[k], $i); tbl(RT, RT) }
  for(i=1; i<tagsNum; i++)
      printf "%s", (key[tgs[i]]!=""? key[tgs[i]]:"null") OFS; print key[RT]
  delete key
}' OFS='@|' |column -ts'@'

with calling the function for every tag name from the tgs array, we refill the values for them by the matching on relevant field they occurred in, and next we print them for each record and null if they don't take any value and then delete the array and do the same for next block and so on.

we used the column -ts'@' to tabular the output and that @ character is come from the OFS='@|' and using this way column will adjust the output fields based on that character and later will be removed from the output, so this assumed that @ character should not be occurred in your input data (if it can, change to another character instead). if you have column from util-linux package, you can change OFS='@|' |column -ts'@' to OFS='|' |column -t -s'|' -o' | '.


asxadadad  ,aaf dsf  |Mum   |23  |BBS
null                 |Ors   |11  |MB
adad sf              |Kol   |21  |BB
pqr                  |null  |21  |NN
0
1

Making use of bash arrays in which we Store the header field names. Since the sed code is quite repetitive for the various header fields, so we first generate the sed code based on the header. Then generate the output.

#!/usr/bin/env bash
declare -a tags=(Name City Age Couse end)
{
echo 'H;/^"Couse":/!d;z;x'
paste -d'\n' \
  <(printf 's/\\n"%s":"([^"]*)"(.*)/\\2|\\1/\n' "${tags[@]:0:4}") \
  <(printf 't %s\n' "${tags[@]:1:4}") \
  <(yes 's/$/|null/'|head -n4) \
  <(printf ':%s\n' "${tags[@]:1:4}");
echo 's/^\|//'
} |
sed -Ef - demo.txt |
column -ts'|' -o '|' |
sed -e 's/|/ & /g'

Output:

asxadadad  ,aaf dsf | Mum  | 23 | BBS 
null                | Ors  | 11 | MB  
adad sf             | Kol  | 21 | BB  
pqr                 | null | 21 | NN  

Another method using the perl utility is given here.

perl -lne '
  @tags = qw(Name City Age Couse) if $. == 1;
  %h = (%h, /"([^:]+)":"(.*)"/);
  next unless /^"Couse":/;
  for (@tags) {
    $h{$_} ||= q(null);
    length($h{$_})>$maxw{$_} and $maxw{$_}=length($h{$_});
    push @{$Aref->[$nr]}, $h{$_};
  }
  $nr++; %h=();
  }{
  my $fmt = join "A2", map { sprintf q[A%d], $_+1 } @maxw{@tags};
  print pack $fmt, split /([|])/, join q[|], @{shift @$Aref} while @$Aref;
' demo.txt
1

Using Raku (formerly known as Perl_6)

Below is a fairly quick-and-dirty solution in the Raku programming language. Save the code below to a file with a .raku extension (.p6 also works, currently):

my @a1= $*ARGFILES.IO.lines;
#my @a1= "/Can/use/hardcoded/path/to/file.txt".IO.lines;
@a1.=map(*.split(":")); 

my @a2; my $i=0, my $j=0;
my $b = << \"Name\" \"City\" \"Age\" \"Couse\" >> ;

while 
$i < (@a1.elems) { 
my $tag = $b[$j]; 
if @a1[$i][0] ~~ / <$tag> / {
    #`{above RHS matches inside double quotes}
    @a2.push(@a1[$i]);  
    $i++; $j=(($j+1) mod 4);}
    else 
    {@a2.push((<<$tag>>; <<\"NULL\">>));  
    $j=(($j+1) mod 4);}
};
.say for @a2;

"____________\n".say;

my @b;
loop (my $n=0; $n < $b.elems; $n++) { 
    my $tag0 := $b[$n];
    @b.push: @a2.grep(/<$tag0>/).[0..*-1]>>.[1];
};
$b.say; .say for @b;

"____________\n".say;
    
put $b.fmt('%s|').join("\n");
put ([Z] @b)>>.fmt('%s|').join("\n");    

Run the script above at the terminal command line, giving the name of your file-of-interest (e.g. ~$ raku make_table.p6 input.txt ). Choose one of the three outputs available (i.e. comment-out the other two). The output of the first section (the .say for @a2; line above) fills in NULL values for the 4 keys, giving:

("Name" "asxadadad  ,aaf dsf")
("City" "Mum")
("Age" "23")
("Couse" "BBS")
("Name" "NULL")
("City" "Ors")
("Age" "11")
("Couse" "MB")
("Name" "adad sf")
("City" "Kol")
("Age" "21")
("Couse" "BB")
("Name" "pqr")
("City" "NULL")
("Age" "21")
("Couse" "NN")

The output of the middle section (between the two horizontal line separators), gives:

("Name" "City" "Age" "Couse")
("asxadadad  ,aaf dsf" "NULL" "adad sf" "pqr")
("Mum" "Ors" "Kol" "NULL")
("23" "11" "21" "21")
("BBS" "MB" "BB" "NN")

The output of the lower section (below the second horizontal line separator), when piped through column -ts'\"' at the bash command line (using a fairly-old unix subsystem), gives:

Name                         |                    City  |     Age  |        Couse  |
asxadadad  ,aaf dsf          |                    Mum   |     23   |        BBS    |
NULL                         |                    Ors   |     11   |        MB     |
adad sf                      |                    Kol   |     21   |        BB     |
pqr                          |                    NULL  |     21   |        NN     |

The two main advantages of using Raku for this problem are 1) enhanced quotation support and 2) Unicode support. Adding Raku to your programming environment allows you to keep your pre-existing Unix/Linux tools intact--you just have an extra tool available when you need it.

[Above tested on GNU bash, version 3.2.57(1)-release (x86_64-apple-darwin14)]
https://raku.org/

0

The data looks as if has been modified from having originally been some JSON document.

Let's bring the JSON document structure back by

  1. adding [{ to the start of the document and }] to the end,
  2. adding },{ to the end of each line starting with the exact string "Couse" (but not to the last line), and
  3. adding commas to the end of each line whose end is not otherwise modified (i.e. that still has a double quote at the end of the line).
sed -e '1 s/^/[{/' -e '$ s/$/}]/' \
    -e '/^"Couse"/ { $! s/$/},{/; }' \
    -e 's/"$/&,/' file

With pretty-printing, this turns our document into

[
  {
    "Name": "asxadadad  ,aaf dsf",
    "City": "Mum",
    "Age": "23",
    "Couse": "BBS"
  },
  {
    "City": "Ors",
    "Age": "11",
    "Couse": "MB"
  },
  {
    "Name": "adad sf",
    "City": "Kol",
    "Age": "21",
    "Couse": "BB"
  },
  {
    "Name": "pqr",
    "Age": "21",
    "Couse": "NN"
  }
]

We may then convert this into CSV by piping it through jq (adding some column headers and replacing empty values with the string null while doing so):

jq -r '    [ "Name", "City", "Age", "Couse" ],
    (.[] | [ .Name,  .City,  .Age,  .Couse  ]) |
    map(. // "null") | @csv'

This would generate

"Name","City","Age","Couse"
"asxadadad  ,aaf dsf","Mum","23","BBS"
"null","Ors","11","MB"
"adad sf","Kol","21","BB"
"pqr","null","21","NN"

We may then use csvlook from the csvkit toolkit to generate a nice looking table.

The final pipeline would look like

sed -e '1 s/^/[{/' -e '$ s/$/}]/' \
    -e '/^"Couse"/ { $! s/$/},{/; }' \
    -e 's/"$/&,/' file |
jq -r '    [ "Name", "City", "Age", "Couse" ],
    (.[] | [ .Name,  .City,  .Age,  .Couse  ]) |
    map(. // "null") | @csv' |
csvlook --blanks

We're using csvlook with its --blanks option to make it keep the null strings as they are (it would otherwise remove these).

The result would be

| Name                | City | Age | Couse |
| ------------------- | ---- | --- | ----- |
| asxadadad  ,aaf dsf | Mum  |  23 | BBS   |
| null                | Ors  |  11 | MB    |
| adad sf             | Kol  |  21 | BB    |
| pqr                 | null |  21 | NN    |

Or, rendered as markdown:

Name City Age Couse
asxadadad ,aaf dsf Mum 23 BBS
null Ors 11 MB
adad sf Kol 21 BB
pqr null 21 NN

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.