Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

transpose based on condition

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting transpose based on condition
# 1  
Old 10-11-2009
transpose based on condition
Hi,

I have the oracle table coulns in an order like
date, state1, state2....state9
and i need to prepare data from the script output for loading in to this table

The script is
Code:
#!/bin/ksh
/usr/xpg4/bin/awk -F"-"  '{print $2,$4}' /aemu/ErrorLogs/data/MissingCGIcount.txt | /usr/xpg4/bin/awk -F" " '{print $1,$3}' > /aemu/
ErrorLogs/data/MissingCGIcount_mnc.txt 
sleep 3
/usr/xpg4/bin/awk -F" " '{arr[$1]+=$2} END {for (i in arr) {print i,arr[i]}}' /aemu/ErrorLogs/data/MissingCGIcount_mnc.txt

Quote:
MissingCGIcount.txt
404-73-502-2093 1
404-73-131-31783 11
404-73-131-31402 2
404-73-142-42633 2
404-71-304-59661 2
404-73-124-24052 2
404-73-142-42631 2
404-73-124-24061 1
404-73-142-42632 2
404-71-304-59601 2
404-73-139-39461 1
404-73-124-24102 2
404-71-304-59602 2
404-73-124-24063 1
404-73-142-42641 9
404-71-304-59603 1
404-73-142-42642 1
404-73-172-30272 4
404-73-110-23192 1
404-66-2453-46431 13
404-73-131-31731 16
....etc
and the output of the above script is
Quote:
# ./MissingCellscountMNCwise
81 1097
64 5
80 48
72 765
66 1398
71 787
73 22407
I need to prepare a text file for loading the above result in to the table based on the following condition

if col1 value is 64, corresponding col2 value is for state1 column in table
if col1 value is 80, corresponding col2 value is for state2 column in table
if col1 value is 72, corresponding col2 value is for state3 column in table
if col1 value is 71, corresponding col2 value is for state4 column in table
if col1 value is 73, corresponding col2 value is for state5 column in table
if col1 value in (38,74,75,76,77,81) corresponding sum of col2 value is for state6 column in table
if col1 value in (57,58,66) corresponding sum of col2 value is for state7 column in table
if col1 value in (34,51,53,54,55,59,62) corresponding sum of col2 value is for state8 column in table
if col1 value in (68,69) corresponding sum of col2 value is for state9 column in table


I need the data like the below format
Quote:
5 48 765 787 22407 1097 1398 0 0
Note it should add 0 for non existing values



With thanks
Aemunathan

---------- Post updated at 03:40 PM ---------- Previous update was at 01:31 PM ----------

HI

I have carried out by some round about methods ....since am an learner...

Code:
#!/bin/ksh
/usr/xpg4/bin/awk -F"-"  '{print $2,$4}' /aemu/ErrorLogs/data/MissingCGIcount.txt | /usr/xpg4/bin/awk -F" " '{print $1,$3}' > /aemu/
ErrorLogs/data/MissingCGIcount_mnc.txt 
sleep 3

/usr/xpg4/bin/awk -F" " '{arr[$1]+=$2} END {for (i in arr) {print i,arr[i]}}' /aemu/ErrorLogs/data/MissingCGIcount_mnc.txt > /aemu/E
rrorLogs/data/MissingCGIcount_mncwise.txt 
sleep 5

CHN=$(/usr/xpg4/bin/awk -F" " '$1=="64"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
TN=$(/usr/xpg4/bin/awk -F" " '$1=="80"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
KER=$(/usr/xpg4/bin/awk -F" " '$1=="72"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
KTK=$(/usr/xpg4/bin/awk -F" " '$1=="71"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
AP=$(/usr/xpg4/bin/awk -F" " '$1=="73"  {print $2} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
EZ=$(/usr/xpg4/bin/awk -F" " '$1=="38" || $1=="74" || $1=="75" || $1=="76" || $1=="77" || $1=="81"  { sum+=$2 } END { print sum} ' /
aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
WZ=$(/usr/xpg4/bin/awk -F" " '$1=="57" || $1=="58" || $1=="66"  { sum+=$2 } END { print sum} ' /aemu/ErrorLogs/data/MissingCGIcount_
mncwise.txt)
NZ=$(/usr/xpg4/bin/awk -F" " '$1=="34" || $1=="51" || $1=="53" || $1=="54" || $1=="55" || $1=="59" || $1=="62"{ sum+=$2 } END { prin
t sum} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt)
MTNL=$(/usr/xpg4/bin/awk -F" " '$1=="68" || $1=="69" { sum+=$2 } END { print sum} ' /aemu/ErrorLogs/data/MissingCGIcount_mncwise.txt
)

if [ $CHN ==  ]
then
CHN=0
fi
if [ $TN ==  ]
then
TN=0
fi
if [ $KER ==  ]
then
KER=0
fi
if [ $KTK ==  ]
then
KTK=0
fi
if [ $AP ==  ]
then
AP=0
fi
if [ $EZ ==  ]
then
EZ=0
fi
if [ $WZ ==  ]
then
WZ=0
fi
if [ $NZ ==  ]
then
NZ=0
fi
if [ $MTNL ==  ]
then
MTNL=0
fi
echo $CHN $TN $KER $KTK $AP $EZ $WZ $NZ $MTNL
exit

but the thing is am getting some errors as well as the correct output.

Quote:
# ./MissingCellscountMNCwise
./MissingCellscountMNCwise[18]: test: argument expected
./MissingCellscountMNCwise[22]: test: argument expected
./MissingCellscountMNCwise[26]: test: argument expected
./MissingCellscountMNCwise[30]: test: argument expected
./MissingCellscountMNCwise[34]: test: argument expected
./MissingCellscountMNCwise[38]: test: argument expected
./MissingCellscountMNCwise[42]: test: argument expected
5 48 765 787 22407 1097 1398 0 0
What would be the problem

---------- Post updated at 04:42 PM ---------- Previous update was at 03:40 PM ----------

Hi

Found the answer for that error as well ffrom this forum itself...
tried this way in all if statements , got the answer
Code:
if [ -z "$AP" ]
then
AP=0
ap=0
fi

# 2  
Old 10-11-2009
Quote:
Originally Posted by aemunathan
Code:
if [ $CHN ==  ]
if [ $TN ==  ]
if [ $KER ==  ]
if [ $KTK ==  ]
if [ $AP ==  ]
if [ $EZ ==  ]
if [ $WZ ==  ]
if [ $NZ ==  ]
if [ $MTNL ==  ]

Comparing "something to nothing" is not going to work Smilie what are $CHN et al. supposed to equal?
# 3  
Old 10-11-2009
Quote:
Originally Posted by aemunathan
The script is
Code:
#!/bin/ksh
/usr/xpg4/bin/awk -F"-"  '{print $2,$4}' /aemu/ErrorLogs/data/MissingCGIcount.txt | /usr/xpg4/bin/awk -F" " '{print $1,$3}' > /aemu/
ErrorLogs/data/MissingCGIcount_mnc.txt 
sleep 3
/usr/xpg4/bin/awk -F" " '{arr[$1]+=$2} END {for (i in arr) {print i,arr[i]}}' /aemu/ErrorLogs/data/MissingCGIcount_mnc.txt

Wow , what's that ? 3 time awk ? somethig like:
Code:
awk -F"-"  '{print $2,$4}' file | awk -F" " '{print $1,$3}' | awk -F" " '{arr[$1]+=$2} END {for (i in arr) {print i,arr[i]}}'

Your logic using awk only once.
Code:
awk -F"[- ]" '{arr[$2]+=$NF} END {for (i in arr) {print i,arr[i]}}' file

# 4  
Old 10-11-2009
cool...its nice to have in one awk...
# 5  
Old 10-11-2009
You can try this all-in-one awk solution Smilie
Code:
# /usr/xpg4/bin/awk '
BEGIN{FS="[- ]";ORS=" "}                        # Set field separator and other record separator
    $2==64{_[1]+=$NF}                           # if col1 value is 64, corresponding col2 value is for state1 column in table
    $2==80{_[2]+=$NF}                           # if col1 value is 80, corresponding col2 value is for state2 column in table
    $2==72{_[3]+=$NF}                           # if col1 value is 72, corresponding col2 value is for state3 column in table
    $2==71{_[4]+=$NF}                           # if col1 value is 71, corresponding col2 value is for state4 column in table
    $2==73{_[5]+=$NF}                           # if col1 value is 73, corresponding col2 value is for state5 column in table
    $2~"38\|7[4567]\|81"{_[6]+=$NF}             # if col1 value in (38,74,75,76,77,81) corresponding sum of col2 value is for state6 column in table
    $2~"5[78]\|66"{_[7]+=$NF}                   # if col1 value in (57,58,66) corresponding sum of col2 value is for state7 column in table
    $2~"38\|7[4567]\|81"{_[8]+=$NF}             # if col1 value in (34,51,53,54,55,59,62) corresponding sum of col2 value is for state8 column in table
    $2~"6[89]"{_[9]+=$NF}                       # if col1 value in (68,69) corresponding sum of col2 value is for state9 column in table
END{
        for(i=0;++i<10;) print (_[i])?_[i]:0    # for each member of _ array print value or add 0 if no value set
   }
' /aemu/ErrorLogs/data/MissingCGIcount.txt

Last edited by danmero; 10-11-2009 at 02:23 PM.. Reason: fix add 0
# 6  
Old 10-11-2009
Nice result...very effective coding...Thanks
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Copy down based on condition

Hello: I need to copy down some data from the previous record in to the next record based on the below conditions If position 41- 59 of the current record is same as the previous record and the value of position 62 is not equal to 1 then copy the previous records value for positions... (1 Reply)
Discussion started by: techedipro
1 Replies

2. Shell Programming and Scripting

Transpose matrix based on second column using awk

Hi, Is it possible to transpose the matrix like this using awk ? Many thanks in advance Input abc Name_1 0 abc Name_2 1 abc Name_3 2 abc Name_4 0.4 def Name_1 0 def Name_2 9 def Name_3 78 def Name_4 1 Output abc def Name_1 0 ... (4 Replies)
Discussion started by: quincyjones
4 Replies

3. UNIX for Dummies Questions & Answers

Condition based on Timestamp (Date/Time based) from logfile (Epoch seconds)

Below is the sample logfile: Userids Date Time acb Checkout time: 2013-11-20 17:00 axy Checkout time: 2013-11-22 12:00 der Checkout time: 2013-11-17 17:00 xyz Checkout time: 2013-11-19 16:00 ddd Checkout time: 2013-11-21 16:00 aaa Checkout... (9 Replies)
Discussion started by: asjaiswal
9 Replies

4. Shell Programming and Scripting

Columns to Rows - Transpose - Special Condition

Hi Friends, Hope all is well. I have an input file like this a gene1 10 b gene1 2 c gene2 20 c gene3 10 d gene4 5 e gene5 6 Steps to reach output. 1. Print unique values of column1 as column of the matrix, which will be a b c (5 Replies)
Discussion started by: jacobs.smith
5 Replies

5. Shell Programming and Scripting

Transpose timestamp based on column values and calculate time difference

Hello Expert, I need to transpose Date-Timestamp based on same column values and calculate time difference. The input file would be as below and required output is mentioned in the bottom INPUT File ======== 08/23/2012 12:36:09 JOB_5340 08/23/2012 12:36:14 JOB_5340 08/23/2012... (2 Replies)
Discussion started by: asnandhakumar
2 Replies

6. UNIX for Dummies Questions & Answers

moving files based on condition

hi i have to move files and send an email and attached the bad files to inform the developer about that. #!/bin/ksh BASE_DIR=/data/SrcFiles cd $BASE_DIR ## finding the files from work directory which are changed in 1 day find -type f -name "*.csv" –ctime 0 > /home/mydir/flist.txt ##... (14 Replies)
Discussion started by: awais290
14 Replies

7. Shell Programming and Scripting

Comment based on a condition

I want to comment 2 lines based on a condition. If THEN occurs immediately after WHEN then i have to comment both the lunes For example : $cat file1.txt CASE WHEN THEN 1 WHEN c1= 'I' AND c2= '0' THEN 2 So in this example i want to... (2 Replies)
Discussion started by: ashwin3086
2 Replies

8. Shell Programming and Scripting

Condition based concatenation.

Hello, I am looking for concatenating the lines based on conditions. Below are the contents of the file: Infile: ----- Test1.PO_Itm COLUMN GAC_DT. Test1.PO_Itm COLUMN (PRODTCD ,PLNTCD). Test1.PO_Itm COLUMN PLNTCD. Test1.PO_Itm COLUMN ACTVIND. Test2.RgnToTerrtryGPI COLUMN... (3 Replies)
Discussion started by: indrajit_u
3 Replies

9. Shell Programming and Scripting

Merging of all files based on a condition

Hi Friends, I am new to UNIX. I need to merge all the files(to FINAL.txt) in single directory based one condition. Out of all the files one of file will have specific value like :GF01: at any where in the file. so the file which is having :GF01: should be appended at the last. EX:... (5 Replies)
Discussion started by: arund_01
5 Replies

10. Shell Programming and Scripting

How to split the String based on condition?

hi , I have a String str="/opt/ibm/lotus/ibw/latest" or ="/opt/lotus/ibw/latest" this value is dynamic..I want to split this string into 2 strings 1. /opt/ibm/lotus(/opt/lotus) this string must ends with "lotus" 2./ibw/latest can any body help me on this? Regards, sankar (2 Replies)
Discussion started by: sankar reddy
2 Replies
Login or Register to Ask a Question