Warm tip: This article is reproduced from stackoverflow.com, please click
awk bash grep perl sed

Building table from list using nearest value?

发布于 2020-04-11 22:51:26

I have a list similar to this...

1ID:42
2ID:85853
Name:Chris
Age:99
Species:Monkey
Name:Bob
Age:23
Species:Fish
Name:Alex
Age:67
Species:Cat
1ID:987
2ID:775437
Name:Tiffany
Age:32
Species:Dog
1ID:777
2ID:65336
Name:Becky
Age:122
Species:Hippo

I want to create a table where some of the data is taken from the nearest result. This prevents me from simply replacing "\n", "Name:", etc to make my table.

This is what I want to end up with...

Chris 99 Monkey 42 85853
Bob 23 Fish 42 85853
Alex 67 Cat 42 85853
Tiffany 32 Dog 987 775437
Becky 122 Hippo 777 65336

I hope that makes sense. The last 2 columns are taken from the nearest previous 1ID and 2ID. There could be any number of entries after the "ID" values.

Questioner
Chris
Viewed
53
markp-fuso 2020-02-03 04:11

Assumptions:

  • data is always formatted as presented and there is always a complete 3-tuple of name/age/species
  • first field of each line is spelled/capitalized exactly as in the example (the solution is based on an exact match)

Sample data file:

$ cat species.dat
1ID:42
2ID:85853
Name:Chris
Age:99
Species:Monkey
Name:Bob
Age:23
Species:Fish
Name:Alex
Age:67
Species:Cat
1ID:987
2ID:775437
Name:Tiffany
Age:32
Species:Dog
1ID:777
2ID:65336
Name:Becky
Age:122
Species:Hippo

One awk solution:

awk -F":" '
$1 == "1ID"     { id1=$2  ; next }
$1 == "2ID"     { id2=$2  ; next }
$1 == "Name"    { name=$2 ; next }
$1 == "Age"     { age=$2  ; next }
$1 == "Species" { print name,age,$2,id1,id2 }
' species.dat

NOTE: The next clauses are optional since each line is matching on a specific value in field 1 ($1).

Running the above generates:

Chris 99 Monkey 42 85853
Bob 23 Fish 42 85853
Alex 67 Cat 42 85853
Tiffany 32 Dog 987 775437
Becky 122 Hippo 777 65336