CODEWITHSUNDEEP
awk
OXXO
OXXO

Reputation: 724

Move values to column based on row value

The input file the date block change every each 4 lines (column 1). Example for days 061218 and 061418, but not in the case for date 061318, which contends 8 lines.

Then in the case where the date does not change after 5 lines,like the example on date 061318 in that case the values of the second part lines 5-8 need to be added to the END ond the lines 1-4. To get correctly in the output file desired.

Input file

061218,2660,2660,2661
061218,0,0,0,0
061218,48,30,569
061218,SD/05,F1/R0,SD/05
061318,2654,2654
061318,0,0
061318,114,60
061318,SD/05,F1/R0
061318,2666
061318,0
061318,1
061318,F1/R0
061418,2648,2648,2649
061418,0,0,0
061418,871,868,876
061418,SD/05,F1/R0,SD/05

Output file

061218,2660,2660,2661
061218,0,0,0,0
061218,48,30,569
061218,SD/05,F1/R0,SD/05
061318,2654,2654,2666
061318,0,0,0
061318,114,60,1
061318,SD/05,F1/R0,F1/R0
061418,2648,2648,2649
061418,0,0,0
061418,871,868,876
061418,SD/05,F1/R0,SD/05

I tried:

awk -F, '{a[$1]=a[$1]?a[$1]","$2:$2;}END{for (i in a)print i, a[i];}' OFS=, file

Thanks in advance

Upvotes: 1

Views: 59

Answers (1)

RavinderSingh13
RavinderSingh13

Reputation: 133780

If your Input_file is same as shown sample(which you mentioned in your comments it is) then could you please try following.

awk '
BEGIN{
   FS=OFS=","
}
prev!=$1 && prev{
   for(i=1;i<=count;i++){
     print prev,a[prev,i]
   }
   prev=count=""
}
{
   prev=$1
   sub(/[^,]*,/,"")
   if(count==4){
     count=1
   }
   else{
     count++
   }
   a[prev,count]=a[prev,count]?a[prev,count] OFS $0:$0
}
END{
   if(prev){
     for(i=1;i<=count;i++){
        print prev,a[prev,i]
     }
   }
}'  Input_file

Change above a[prev,count] line to a[prev,count]=(a[prev,count]?a[prev,count] OFS:"")$0 in Ed Morton sir's style too, to shorten and make it compatible to other awks too.

Upvotes: 2

Related Questions