awk to store in a list the values of certain fileds


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to store in a list the values of certain fileds
# 1  
Old 08-08-2014
Tools awk to store in a list the values of certain fileds

Dear Group,

I have following input:
Code:
sa;sb;sc;sd;period;ma;mb;mc;md;me
sa1;sb1;sc1;sd1;200001;ma1;mb1;mc1;md1;me1
sa2;sb2;sc2;sd2;200002;ma2;mb2;mc2;md2;me2
sa3;sb3;sc3;sd3;200003;ma3;mb3;mc3;md3;me3

first line contains the headers!

I want to create with one pass the following output:
Code:
sa1|sb1|sc1|sd1|200001_ma|ma1|
sa1|sb1|sc1|sd1|200001_mb|mb1|
sa1|sb1|sc1|sd1|200001_mc|mc1|
sa1|sb1|sc1|sd1|200001_md|md1|
sa1|sb1|sc1|sd1|200001_me|me1|
sa2|sb2|sc2|sd2|200002_ma|ma2|
sa2|sb2|sc2|sd2|200002_mb|mb2|
sa2|sb2|sc2|sd2|200002_mc|mc2|
sa2|sb2|sc2|sd2|200002_md|md2|
sa2|sb2|sc2|sd2|200002_me|me2|
sa3|sb3|sc3|sd3|200003_ma|ma3|
sa3|sb3|sc3|sd3|200003_mb|mb3|
sa3|sb3|sc3|sd3|200003_mc|mc3|
sa3|sb3|sc3|sd3|200003_md|md3|
sa3|sb3|sc3|sd3|200003_me|me3|

tried following one liner with partial success:
Code:
awk -F\; 'NR==1{for(ctyi=1;ctyi<=NF;ctyi++) if ($ctyi ~ /period/) found=++ctyi;for(m=found;m<=NF;m++) vector[$m];print vector[$m]}NR>1{i=$1"|"$2"|"$3"|"$4"|"$5;for(x=6;x<=NF;x++)print i"_""|"$x"|"}' in.csv > out.txt

I cannot read and add the 'ma mb mc md me'! The second part:
Code:
NR>1{i=$1"|"$2"|"$3"|"$4"|"$5;for(x=6;x<=NF;x++)print i"_""|"$x"|"}

works as expected. It does produce the following output:
Code:
sa1|sb1|sc1|sd1|200001_|ma1|
sa1|sb1|sc1|sd1|200001_|mb1|
sa1|sb1|sc1|sd1|200001_|mc1|
sa1|sb1|sc1|sd1|200001_|md1|
sa1|sb1|sc1|sd1|200001_|me1|
sa2|sb2|sc2|sd2|200002_|ma2|
sa2|sb2|sc2|sd2|200002_|mb2|
sa2|sb2|sc2|sd2|200002_|mc2|
sa2|sb2|sc2|sd2|200002_|md2|
sa2|sb2|sc2|sd2|200002_|me2|
sa3|sb3|sc3|sd3|200003_|ma3|
sa3|sb3|sc3|sd3|200003_|mb3|
sa3|sb3|sc3|sd3|200003_|mc3|
sa3|sb3|sc3|sd3|200003_|md3|
sa3|sb3|sc3|sd3|200003_|me3|

In the first part:
Code:
NR==1{for(ctyi=1;ctyi<=NF;ctyi++) if ($ctyi ~ /period/) found=++ctyi;for(m=found;m<=NF;m++) vector[$m];print vector[$m]}

I locate the position of the field 'period' in order to check which fields have to be considered till the end of the record. The number of fields after 'period' can vary!

Thanks for your help.


Moderator's Comments:
Mod Comment Please use code tags next time for your code and data. Thanks

Last edited by vbe; 08-08-2014 at 06:14 AM..
# 2  
Old 08-08-2014
You can try this:

Code:
awk 'NR==1 {next}
{for (x=6;x<=NF;x++) {
    print $1 OFS $2 OFS $3 OFS $4 OFS $5"_"substr($x,1,match($x,/[0-9]/)-1) OFS $x OFS
    }
}' FS=\; OFS=\| in.csv > out.txt


Last edited by pilnet101; 08-08-2014 at 06:45 AM..
This User Gave Thanks to pilnet101 For This Post:
# 3  
Old 08-08-2014
Thank you very much for your prompt reply and this did work well but there is a catch Smilie

The 'ma mb mc md me' are actually dummy values! Their length can vary! So it can be 'maaaaaaa' or any other value!
# 4  
Old 08-08-2014
Please try the updated code.
# 5  
Old 08-08-2014
awk to store in a list the values of certain fields

is producing the same result!

For example, let's assume you have the following input:
Code:
sa;sb;sc;sd;period;ma;mbbbbbbb;mcc;mddddddddddddd;m
sa1;sb1;sc1;sd1;200001;ma1;mb1;mc1;md1;me1
sa2;sb2;sc2;sd2;200002;ma2;mb2;mc2;md2;me2
sa3;sb3;sc3;sd3;200003;ma3;mb3;mc3;md3;me3

and you want to transform to:
Code:
sa1|sb1|sc1|sd1|200001_ma|ma1|
sa1|sb1|sc1|sd1|200001_mbbbbbbb|mb1|
sa1|sb1|sc1|sd1|200001_mcc|mc1|
sa1|sb1|sc1|sd1|200001_mddddddddddddd|md1|
sa1|sb1|sc1|sd1|200001_m|me1|
sa2|sb2|sc2|sd2|200002_ma|ma2|
sa2|sb2|sc2|sd2|200002_mbbbbbbb|mb2|
sa2|sb2|sc2|sd2|200002_mcc|mc2|
sa2|sb2|sc2|sd2|200002_mddddddddddddd|md2|
sa2|sb2|sc2|sd2|200002_m|me2|
sa3|sb3|sc3|sd3|200003_ma|ma3|
sa3|sb3|sc3|sd3|200003_mbbbbbbb|mb3|
sa3|sb3|sc3|sd3|200003_mcc|mc3|
sa3|sb3|sc3|sd3|200003_mddddddddddddd|md3|
sa3|sb3|sc3|sd3|200003_m|me3|


Thank you for your time

Last edited by vbe; 08-08-2014 at 08:10 AM.. Reason: code tags please...
# 6  
Old 08-08-2014
Sorry I get you now:

Code:
awk 'NR==1 {
for (i=6;i<=NF;i++) {
    h[i]=$i
}
next
}
{for (x=6;x<=NF;x++) {
    print $1 OFS $2 OFS $3 OFS $4 OFS $5"_"h[x] OFS $x OFS
    }
}' FS=\; OFS=\|  in.csv > out.txt

This User Gave Thanks to pilnet101 For This Post:
# 7  
Old 08-08-2014
That did the job!

Thank you
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to find the X highest values in a list depending on the values of another list with bash/awk?

Hi everyone, This is an exemple of inpout.txt file (a "," delimited text file which can be open as csv file): ID, Code, Value, Store SP|01, AABBCDE, 15, 3 SP|01, AABBCDE, 14, 2 SP|01, AABBCDF, 13, 2 SP|01, AABBCDE, 16, 3 SP|02, AABBCED, 15, 2 SP|01, AABBCDF, 12, 3 SP|01, AABBCDD,... (1 Reply)
Discussion started by: jeremy589
1 Replies

2. Shell Programming and Scripting

Add two more fileds to awk command

This is what I was trying but failed to do so need help. cat 1.sql | awk '{printf("%s",NR%4 ? $0",":$0"\n")}' Output :- I want to add 2 more values for each line (hostname,user name) so the output should have hostname & username (testsrv01,test1) Output should be like... (4 Replies)
Discussion started by: lazydev
4 Replies

3. UNIX for Dummies Questions & Answers

Specific values from a list, awk

Hello together! I have a list like this 1 3 2 5 3 7 4 2 Now I want to take the average of the second column and multiply it by the difference of the first and last value of the first column. I posted an similar thread, but now I wonder how I can tell awk in an easy way to take specific... (2 Replies)
Discussion started by: bjoern456
2 Replies

4. Shell Programming and Scripting

Re: using AWK to compare fileds

I have following text: NAME=ora.LISTENER.lsnr TYPE=ora.listener.type TARGET=ONLINE , ONLINE , ONLINE , ONLINE STATE=ONLINE on host1, ONLINE on host2, ONLINE on host3, ONLINE on host4 NAME=ora.LISTENER_1525.lsnr TYPE=ora.listener.type TARGET=ONLINE ... (2 Replies)
Discussion started by: rcc50886
2 Replies

5. Shell Programming and Scripting

How to read values and store in array?

I am reading a value from a file and want to store the value in a dynamic array as i don't know the number of occurrences of the value in that file. How can i do that and then later fetch that value from array (25 Replies)
Discussion started by: Prachi Gupta
25 Replies

6. Shell Programming and Scripting

Store the output values in array

Hi, How to store the values in array from output result, EG: I have the result like this, ps, google, 1.txt, 1 sam, google, 2.txt, 2 These are the four values followed by comma in two sets. I need to store these values set by set. One set contains four values followed by comma. ... (2 Replies)
Discussion started by: KarthikPS
2 Replies

7. Shell Programming and Scripting

Store values in an Array

Hi all. Well, I have the next code: I need to make an array with the values I have in the bucle, but just don't get it... Question is, how can I store in an array that values, and how can I display them with echo? (8 Replies)
Discussion started by: crcbad
8 Replies

8. UNIX for Advanced & Expert Users

how do I store the values in array using shell

Hi, Is is possible to get the value using shell script? x=1 y1 = 10 y2 = 15 y3 = 7 echo $y$x is giving y1 (variable name) but I need the value of y1 (i.e. 10 dynamically) Is there any solution? if so, please mail me at kkodava@maxis.com.my ... (2 Replies)
Discussion started by: krishna
2 Replies
Login or Register to Ask a Question