shell, read table and write the value for each factor to output


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting shell, read table and write the value for each factor to output
# 1  
Old 10-23-2012
shell, read table and write the value for each factor to output

Hey guyz,

I have a table like this:

1 A=#;B=#;C=#
2 A=#;C=#;D=#;E=#;E=#
3 B=#;B=#;B=#;D=#

# are just some numbers. I want to have the output like this:

* 1 2 3
A # # NA
B # NA #
C # # NA
D NA # #
E NA # NA

So basically, I wanna know in each of the rows in my input (which gonna be the columns in output) what is the number for each factor (A,B,C,...). sometimes there are repeating of one factor which I like to take the maximum number in that case. And sometimes the factor is missing which it turns NA in corresponding cell in output.

There are not the same number of factor in each row which makes it problematic.

Can anyone help me with this?
Thanks in advance
# 2  
Old 10-23-2012
Code:
awk -F "[ ;]" '{!X[$1]++;for(i=2;i<=NF;i++){split($i,b,"=");a[$1,b[1]]=b[2];!Y[b[1]]++;}}END{
for(i in X){s=s?s" "i:"* "i}
print s;
for(j in Y){printf j" "
for(i in X){
printf a[i,j]?a[i,j]" ":"NA "
}print ""}}' file


Last edited by pamu; 10-23-2012 at 09:16 AM..
# 3  
Old 10-23-2012
With some assumptions:
Code:
perl -lane '
@temp=split /[=;]/,$F[1];
for ($i=0;$i<@temp;$i+=2) {
 if(not exists $val{$temp[$i] . " " . $F[0]}) {
  $val{$temp[$i]  . $F[0]}=$temp[$i+1]
 }
 else {
  if ($temp[$i+1] >= $val{$temp[$i] . " " . $F[0]}) {
   $val{$temp[$i] . $F[0]}=$temp[$i+1]
  }
 }
 $exists{$temp[$i]}=1;
 $cols{$F[0]}=1;
}
END{
@cols=sort keys %cols;
unshift @cols,"*";
print "@cols";
for (sort keys %exists) {
 @temp=();
  for ($i=1;$i<@cols;$i++) {
   push @temp,((exists $val{$_  . $cols[$i]})?$val{$_ . $cols[$i]}:"NA")
  }
 unshift @temp,$_;
 print "@temp";
}
}' file


Last edited by elixir_sinari; 10-26-2012 at 07:10 AM..
This User Gave Thanks to elixir_sinari For This Post:
# 4  
Old 10-23-2012
Thank you both pamu and elixir_sinari! Your posts are always helpful! I need some time to go through your scripts and find out the logic and then applying them on my data.
I will let you know...

cheers!
# 5  
Old 10-25-2012
Data

Ok. I went through the scripts and applied it on my data. elixir_sinari it seems your script does not work for all factors. It doesn't give the information for all factors. With minor knowledge that I have about perl I couldn't find what is the problem.
pamu, your scripts seems to work just fine. There is just problem with those factors at the very end of each line. for some reasons the output is not true for those ones.

To make it more clear for you guys I write the first 5 lines of my input with some simplicity:

region1 A=0.001037;B=0.001054;C=0.001035;D=0.001026;D=0.001028
region2 A=0.002409;E=0.428007;D=0.002406;D=0.002408
region3 A=0.003289;C=0.0033;D=0.00331;D=0.00331;F=0.133698531
region4 A=0.005823;B=0.005901;C=0.005809;D=0.005819;D=0.005821;F=0.111969159
region5 A=0.005986;E=0.23462;D=0.005984;D=0.005987


And in this case the desired output will be:

* region1 region2 region3 region4 region5
A 0.001037 0.002409 0.003289 0.005823 0.005986
B 0.001054 NA NA 0.005901 NA
C 0.001035 NA 0.0033 0.005809 NA
D 0.001028 0.002408 0.00331 0.005821
E NA 0.428007 NA NA 0.23462
F NA NA 0.133698531 0.111969159 NA

If you can find out the solution you will make me so happy! Smilie

P.S. I have some factors which their names start with similar letters (for exmaple: zfh, z, zld). But I don't think that's the problem.
Thanks again

##before posting this I just applied pamu's script on this exmaple here and it works just fine! But on my own data which is rather big and compicated I face the problem that I mentioned! Smilie Help me! :| :|
# 6  
Old 10-25-2012
Made some changes to my earlier post. Check now.
# 7  
Old 10-25-2012
Quote:
Originally Posted by @man
O
region1 A=0.001037;B=0.001054;C=0.001035;D=0.001026;D=0.001028
region2 A=0.002409;E=0.428007;D=0.002406;D=0.002408
region3 A=0.003289;C=0.0033;D=0.00331;D=0.00331;F=0.133698531
region4 A=0.005823;B=0.005901;C=0.005809;D=0.005819;D=0.005821;F=0.111969159
region5 A=0.005986;E=0.23462;D=0.005984;D=0.005987

##before posting this I just applied pamu's script on this exmaple here and it works just fine! But on my own data which is rather big and compicated I face the problem that I mentioned! Smilie Help me! :| :|
Yes. If on sample file it is working fine then we don't have any chance of checking what could be the problem.

And please clarify - above highlighted in RED there are two D present is first row. What to do here..?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Shell Script to read hive table and send email

HI Team, I am working on reading hive table and send email in email body using shell script, can you please help on fixing the errors: I have 6 columns in my hive table and trying to send the email in the mail body. below script: #!/bin/bash hive -e 'SELECT count(*) from db.table' >... (4 Replies)
Discussion started by: Mi4304
4 Replies

2. Shell Programming and Scripting

Shell output format like table

Hi, OS: Redhat 7.5 shell: Bash Wrote below script to login into oracle via shell script and trying to reset locked account..It works as expected. But I need specific output << EOF should go to target terminal not all out put running below script from ansible command line.. #!/bin/bash... (1 Reply)
Discussion started by: onenessboy
1 Replies

3. Shell Programming and Scripting

Read input write multply output with creteria

Hi All Please Help Read input write multply output with creteria Exemple i have file abc 111 444 abc 111 444 def 111 444 def111 444 bbb 111 444 bbb 111 444 i would need write 3 files pos 1-3 is the Criteria output would be file1 contains abc file2 def file3 bbb ... (3 Replies)
Discussion started by: tonyk334
3 Replies

4. UNIX for Beginners Questions & Answers

How do I use grep to grab prime number output from my factor program?

I have a factor program that runs and outputs to stdout all the prime numbers that are specified in the given paramters, in this case 30000000-31000000. Command: factor/factor 30000000-31000000 Sample output: 30999979 = 30999979 30999980 = 2^2 5 11 140909 30999981 = 3 10333327... (6 Replies)
Discussion started by: steezuschrist96
6 Replies

5. Shell Programming and Scripting

I want query output to send on mail using table tag and output should be in table

#! /bin/ksh #] && . ./.profile 2>/dev/null if test -f '.profile'; then . ./.profile; fi; #. .profile LOG_DIR=/app/rpx/jobs/scripts/just/logs sendEmail() { pzCType="$1"; pzTitle="$2"; pzMsg="$3"; pzFrom="$4"; pzTo="$5"; pzFiles="$6"; pzReplyTo="$7" ( ... (21 Replies)
Discussion started by: ankit.mca.aaidu
21 Replies

6. Shell Programming and Scripting

awk - read from a file and write conditional output

I have a file, which has '|' as separator; I need to read each line from that file and produce output to another file. While reading, I have certain condition on few specific columns (like column3 ='good'); only those lines will be processed. (3 Replies)
Discussion started by: mady135
3 Replies

7. Homework & Coursework Questions

Write a shell script for SQL loader to load data into a staging table

Hi, I'm new to Linux. I'm working on a database, and need to load data in a database table (which I already created) using shell script. The table has two columns - Acct_number (not nullable) and date (timestamp). I'm not able to write a shell script for that. Can any one help me? ... (3 Replies)
Discussion started by: saisudeep
3 Replies

8. Shell Programming and Scripting

Read parameter file in a shell script to unload a DB2 Table???

Hi , I Have following requirement: DB2 Sql query to pass from a parameter file for example, I would create a parameter file with (SELECT column 1, column 2 FROM Table name) then job would read it and create a file with the contents named table.txt How to write/modify below ksh script to... (10 Replies)
Discussion started by: developer.dwh9
10 Replies

9. Shell Programming and Scripting

How to Add Table with Header for Shell Output

I have a Script that generates 3 columns of the result. The line script is /usr/xpg4/bin/awk -F' ' '{print $1 /t $2}' File_Name | awk -F'>' '{print $2}' | cut -d'<' -f1 | sort -rn | uniq -c The output of the Command is : Code.500 4 Input Error Code.404 ... (4 Replies)
Discussion started by: raghunsi
4 Replies

10. IP Networking

read/write,write/write lock with smbclient fails

Hi, We have smb client running on two of the linux boxes and smb server on another linux system. During a backup operation which uses smb, read of a file was allowed while write to the same file was going on.Also simultaneous writes to the same file were allowed.Following are the settings in the... (1 Reply)
Discussion started by: swatidas11
1 Replies
Login or Register to Ask a Question