3

This was a question coming from here Aligning values in csv file

The problem is that I would like to align the values on the 5th column based on how they match with the first column.

Input:

"DB Instance Identifier","Engine","Instance Type","MultiAZ","Reservation ID","State","Start Time","Reserved Engine","Reserved DB Instance Class"
"alpha-db-dev","postgres","db.t4g.micro",false,"alpha-db-dev-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"alpha-db-prod","postgres","db.t4g.small",true,"alpha-db-prod-26aug2022","active","2022-08-26","postgresql","db.t4g.small"
"alpha-db-staging","postgres","db.t4g.micro",false,"alpha-db-staging-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"beta-db-dev-primary","aurora-postgresql","db.t3.medium",false,"charlie-db-dev-8dec2021","active","2021-12-08","postgresql","db.t3.micro"
"beta-db-prod-primary","aurora-postgresql","db.r5.xlarge",false,
"beta-db-prod-replica","aurora-postgresql","db.r5.xlarge",false,
"beta-db-staging-primary","aurora-postgresql","db.t3.medium",false,
"charlie-db-dev","postgres","db.t3.micro",false,
"charlie-db-prod-dms","postgres","db.m4.4xlarge",true,
"charlie-db-prod-dms-replica","postgres","db.m4.4xlarge",false,
"charlie-db-staging","postgres","db.t2.large",false,
"charlie-db-staging-loadtest","postgres","db.m4.4xlarge",false,
"charlie-kong-db-dev","postgres","db.t3.micro",false,
"charlie-kong-db-prod","postgres","db.m4.large",true,
"charlie-kong-db-staging","postgres","db.t2.small",false,
"delta-db-prod","mysql","db.t3.small",true,
"delta-db-recon-prod","mysql","db.t3.micro",false,
"delta-db-staging","mysql","db.t3.micro",false,

The desired output would be:

"DB Instance Identifier","Engine","Instance Type","MultiAZ","Reservation ID","State","Start Time","Reserved Engine","Reserved DB Instance Class"
"alpha-db-dev","postgres","db.t4g.micro",false,"alpha-db-dev-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"alpha-db-prod","postgres","db.t4g.small",true,"alpha-db-prod-26aug2022","active","2022-08-26","postgresql","db.t4g.small"
"alpha-db-staging","postgres","db.t4g.micro",false,"alpha-db-staging-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"beta-db-dev-primary","aurora-postgresql","db.t3.medium",false,
"beta-db-prod-primary","aurora-postgresql","db.r5.xlarge",false,
"beta-db-prod-replica","aurora-postgresql","db.r5.xlarge",false,
"beta-db-staging-primary","aurora-postgresql","db.t3.medium",false,
"charlie-db-dev","postgres","db.t3.micro",false,"charlie-db-dev-8dec2021","active","2021-12-08","postgresql","db.t3.micro"
"charlie-db-prod-dms","postgres","db.m4.4xlarge",true,
"charlie-db-prod-dms-replica","postgres","db.m4.4xlarge",false,
"charlie-db-staging","postgres","db.t2.large",false,
"charlie-db-staging-loadtest","postgres","db.m4.4xlarge",false,
"charlie-kong-db-dev","postgres","db.t3.micro",false,
"charlie-kong-db-prod","postgres","db.m4.large",true,
"charlie-kong-db-staging","postgres","db.t2.small",false,
"delta-db-prod","mysql","db.t3.small",true,
"delta-db-recon-prod","mysql","db.t3.micro",false,
"delta-db-staging","mysql","db.t3.micro",false,

This was previously answered by Ed Morton in the previous question with an awk script

BEGIN { FS=OFS="," }
NR == FNR {
    id = $2
    sub(/-[^-]+$/,"",id)
    vals[id] = $2 OFS $3
    next
}
{ print (FNR>1 ? $1 OFS vals[$1] : $0) }

However when I try to understand and modify the values in the script to match it in the csv file I'm working on, I'm getting a bunch of , or getting the wrong values at all as initially the awk script above compares columns 1 and 2. But this time I would like it to compare the 1st and 5th column. Then the whole row (columns 5-9) to be aligned with the 1st column. How do I go about this? I'm still studying awk as I go.

Edit: changed the values on the input data to prevent confusion as I'm working with comma separated values. I think I pasted incorrectly when I opened the file in excel and pasted from there instead of doing it with a code editor. Sorry bout the confusion!

Improve this question
3
  • 1
    I don't understand what you need. Can you explain a bit more please? Your desired output doesn't appear to be "aligned". Are you sure you want to align, that is put one thing under another thing, or do you mean you want to concatenate some rows together? Please explain in words what the algorithm should be to change your data. Commented Sep 6, 2022 at 11:35
  • 1
    Is not a problem of formatting (putting things one over another aligned). Is a problem of aligning the contents of the data. He wants (part of) field $5 to match field $1. Commented Sep 6, 2022 at 12:23
  • 1
    By the way, the word you want is "join" rather than "align" for what you're trying to do. Commented Sep 6, 2022 at 15:36

2 Answers 2

Reset to default
3

Since you appear to have multiple similar files with different numbers of fields you want to do this with, I'd first split the input into a head (first 4 fields plus comma) and tail (5th to last fields) so it's easy to do this no matter which field you want to match on and/or how many fields you want to include from before/after the matching field, e.g. if you wanted to compare at and copy from the 7th field instead of 5th you'd just change {4} to {6} in this script:

$ cat tst.awk
BEGIN { FS=OFS="," }
{
    match($0,/([^,]+,){4}/)
    head = substr($0,RSTART,RLENGTH)
    tail = substr($0,RSTART+RLENGTH)
}
NR == FNR {
    $0 = tail
    id = $1
    sub(/-[^-]+$/,"\"",id)
    vals[id] = $0
    next
}
{
    print (FNR>1 ? head vals[$1] : $0)
}


$ awk -f tst.awk file file
"DB Instance Identifier","Engine","Instance Type","MultiAZ","Reservation ID","State","Start Time","Reserved Engine","Reserved DB Instance Class"
"alpha-db-dev","postgres","db.t4g.micro",false,"alpha-db-dev-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"alpha-db-prod","postgres","db.t4g.small",true,"alpha-db-prod-26aug2022","active","2022-08-26","postgresql","db.t4g.small"
"alpha-db-staging","postgres","db.t4g.micro",false,"alpha-db-staging-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"beta-db-dev-primary","aurora-postgresql","db.t3.medium",false,
"beta-db-prod-primary","aurora-postgresql","db.r5.xlarge",false,
"beta-db-prod-replica","aurora-postgresql","db.r5.xlarge",false,
"beta-db-staging-primary","aurora-postgresql","db.t3.medium",false,
"charlie-db-dev","postgres","db.t3.micro",false,"charlie-db-dev-8dec2021","active","2021-12-08","postgresql","db.t3.micro"
"charlie-db-prod-dms","postgres","db.m4.4xlarge",true,
"charlie-db-prod-dms-replica","postgres","db.m4.4xlarge",false,
"charlie-db-staging","postgres","db.t2.large",false,
"charlie-db-staging-loadtest","postgres","db.m4.4xlarge",false,
"charlie-kong-db-dev","postgres","db.t3.micro",false,
"charlie-kong-db-prod","postgres","db.m4.large",true,
"charlie-kong-db-staging","postgres","db.t2.small",false,
"delta-db-prod","mysql","db.t3.small",true,
"delta-db-recon-prod","mysql","db.t3.micro",false,
"delta-db-staging","mysql","db.t3.micro",false,
Improve this answer
1
  • 1
    Thank you Ed! Once again you’ve helped me a lot here as I’ll probably be facing this dilemma again. Although with a different twist and flavor this shed some reaaaally good concept on using awk. I can’t thank you enough! Thanks! Commented Sep 7, 2022 at 12:10
1

The file you are using now is using "," as field delimiter and many fields are surrounded by double quotes. You could o something like:

$ awk '  BEGIN{ FS=OFS="," }
         NR==FNR{
             if (NR==1) {next}
             id=$5;
             sub(/-[^-]+"$/,"\"",id);
             a[id]=$5 OFS $6 OFS $7 OFS $8 OFS $9; next
         } 
         { 
             if (FNR==1) {print; next}
             print $1,$2,$3,$4,a[$1] 
         }
      ' infile infile

That, for the input given, will print this:

"DB Instance Identifier","Engine","Instance Type","MultiAZ","Reservation ID","State","Start Time","Reserved Engine","Reserved DB Instance Class"
"alpha-db-dev","postgres","db.t4g.micro",false,"alpha-db-dev-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"alpha-db-prod","postgres","db.t4g.small",true,"alpha-db-prod-26aug2022","active","2022-08-26","postgresql","db.t4g.small"
"alpha-db-staging","postgres","db.t4g.micro",false,"alpha-db-staging-26aug2022","active","2022-08-26","postgresql","db.t4g.micro"
"beta-db-dev-primary","aurora-postgresql","db.t3.medium",false,
"beta-db-prod-primary","aurora-postgresql","db.r5.xlarge",false,
"beta-db-prod-replica","aurora-postgresql","db.r5.xlarge",false,
"beta-db-staging-primary","aurora-postgresql","db.t3.medium",false,
"charlie-db-dev","postgres","db.t3.micro",false,"charlie-db-dev-8dec2021","active","2021-12-08","postgresql","db.t3.micro"
"charlie-db-prod-dms","postgres","db.m4.4xlarge",true,
"charlie-db-prod-dms-replica","postgres","db.m4.4xlarge",false,
"charlie-db-staging","postgres","db.t2.large",false,
"charlie-db-staging-loadtest","postgres","db.m4.4xlarge",false,
"charlie-kong-db-dev","postgres","db.t3.micro",false,
"charlie-kong-db-prod","postgres","db.m4.large",true,
"charlie-kong-db-staging","postgres","db.t2.small",false,
"delta-db-prod","mysql","db.t3.small",true,
"delta-db-recon-prod","mysql","db.t3.micro",false,
"delta-db-staging","mysql","db.t3.micro",false,
Improve this answer
4
  • Thanks for updating your answer! After some few data cleanup, I managed to make it work in respect to your code! big thanks! Commented Sep 6, 2022 at 13:46
  • Answer edited to process comma separated values, where values are double quoted. Two changes were needed (1) change the value of FS (2) add a double quote character when the value of $5 was cut (sub(...)). @GifterVillanueva Commented Sep 6, 2022 at 14:22
  • Thank you so much! This worked as intended and how I would've wanted it! Commented Sep 6, 2022 at 14:30
  • 1
    @GifterVillanueva You are welcome !. :-) Commented Sep 6, 2022 at 14:32

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.