topic Re: merging of columns from two files in Operating System - HP-UX

merging of columns from two files

Narasimham — Fri, 24 May 2002 17:34:09 GMT

I have a question , I have two files as shown below

FILE1 FILE2
Time cpu Time memory
0:00 45 0:00 15
0:15 72 0:15 82
0:30 15 0:30 15
0:45 95 0:45 25
1:00 1:00 40
1:15 1:15 25
1:30 1:30 45

***************************************
I want to create a new file by merging the above two files as below:

NEW FILE
Time cpu memory
0:00 45 15
0:15 72 82
0:30 15 15
0:45 95 25
1:00 40
1:15 25
1:30 45

*****************
I tried to merge the fields using pr and paste commands. But i am getting a misaligned output file as follows:

OUTPUT FILE
Time cpu memory
0:00 45 15
0:15 72 82
0:30 15 15
0:45 95 25
1:00 40
1:15 25
1:30 45

can any one suggest a script to get the correct output file. can we use awk for this.??

Thanks
Narasimham

Re: merging of columns from two files

Patrick Wallek — Fri, 24 May 2002 17:48:05 GMT

Maybe I'm not seeing something, but the example of what you want your new file to look like and what your current attempt output look the same.

I don't see the problem.

Re: merging of columns from two files

MANOJ SRIVASTAVA — Fri, 24 May 2002 17:48:53 GMT

Hi

Please look at this link :

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x66ae6af52b04d5118fef0090279cd0f9,00.html

There is a good script also there.

Manoj Srivastava

Re: merging of columns from two files

Narasimham — Fri, 24 May 2002 17:54:19 GMT

Once again i am submitting my question

This may give you more clarity
FILE1

Time cpu
0:00 45
0:15 72
0:30 15
0:45 95
1:00
1:15
1:30
*****************
FILE2
Time memory
0:00 15
0:15 82
0:30 15
0:45 25
1:00 40
1:15 25
1:30 45

***************
As there are only four records under cpu field the values from memory are getting shifted to the left side as below;

OUTPUT FILE
Time cpu memory
0:00 45 15
0:15 72 82
0:30 15 15
0:45 95 25
1:00 40
1:15 25
1:30 45

I think you understand my problem.

Thanks

Re: merging of columns from two files

MANOJ SRIVASTAVA — Fri, 24 May 2002 19:24:52 GMT

Hi

here is the solution may not be a straight one though

paste file2 file1 |awk '{print $1,$2,$4}'

o/p looks like this

Time memory cpu
0:00 15 45
0:15 82 72
0:30 15 15
0:45 25 95
1:00 40
1:15 25
1:30 45

better than the previous one.

Manoj Srivastava

Re: merging of columns from two files

Narasimham — Fri, 24 May 2002 19:34:39 GMT

Manoj

I know that we can do this way.
but what happens for instance, if first column has less number of records than the second????

can we use join for this??

Thanks
Narasimham

Re: merging of columns from two files

Rodney Hills — Fri, 24 May 2002 20:50:25 GMT

You could use join, but you don't have much flexibility. The command you would use is-

join -a 1 -a 2 -e \* -o 1.1,1.2,2.2 file1 file2

This will place a "*" in the fields that are missing. Unfortunately if their are any missing time stamps in file1, they will be replaced with "*" also.

-- Rod Hills

Re: merging of columns from two files

Steven Sim Kok Leong — Sat, 25 May 2002 01:02:01 GMT

Hi,

This script should meet your needs:

======================================
bash-2.05a# cat ./combine
#!/sbin/sh

echo `head -1 file1` `head -1 file2 | cut -d" " -f2-`
for i in `cat file1 file2 | grep ':' | awk '{print $1}' | sort -n | uniq`
do
echo $i `grep $i file1 | cut -d" " -f2-` `grep $i file2 | cut -d" " -f2-`
done
======================================

Output of my testing:

======================================
bash-2.05a# cat file1
Time cpu
0:00 45
0:15 72
0:30 15
0:45 95
1:00
1:15
1:30

bash-2.05a# cat file2
Time memory
0:00 15
0:15 82
0:30 15
0:45 25
1:00 40
1:15 25
1:30 45

bash-2.05a# ./combine
Time cpu memory
0:00 45 15
0:15 72 82
0:30 15 15
0:45 95 25
1:00 40
1:15 25
1:30 45
======================================

The for condition is needed to ensure that all timings in both files are accounted for even when timings in one file does not exist in other.

Hope this helps. Regards.

Steven Sim Kok Leong

Re: merging of columns from two files

Suhas_3 — Mon, 27 May 2002 03:52:41 GMT

HI,

If the files are sorted on first columns, then you can use "join command"
join FILE1 FILE2

join command has another options also, for which you can refer to "man join"

In your case, I think what you can do is remove the title, sort FILE1 and FILE2, then join the files and then add the title again.

Hope this helps,
Regards,
Suhas

Re: merging of columns from two files

Tom Geudens — Mon, 27 May 2002 04:01:32 GMT

Hi,
1. Take of the headers (join only works on sorted files)
2. #join file1 file2
3. Add the headers

Hope this helps,
Tom

Re: merging of columns from two files

Narasimham — Tue, 28 May 2002 13:09:15 GMT

Hi....Steven

The output is not as reqd. .. still the last four rows of the file2 are shfting to the left.

Thanks
Narasimham

Re: merging of columns from two files

Steven Sim Kok Leong — Tue, 28 May 2002 13:18:40 GMT

Hi,

I see what you mean. I have now made script modifications to shift the columns to the right:

=========================================
#!/sbin/sh

echo `head -1 file1` `head -1 file2 | cut -d" " -f2-`
for i in `cat file1 file2 | grep ':' | awk '{print $1}' | sort -n | uniq`
do
col1=`grep $i file1 | cut -d" " -f2-`
if echo $col1 | grep [1-9] >/dev/null 2>&1
then
echo $i `grep $i file1 | cut -d" " -f2-` `grep $i file2 | cut -d" " -f2-`
else
echo $i " " `grep $i file2 | cut -d" " -f2-`
fi
done
=========================================

Hope this helps. Regards.

Steven Sim Kok Leong

Re: merging of columns from two files

Steven Sim Kok Leong — Tue, 28 May 2002 13:22:37 GMT

Hi,

Due to limitations of the forum in displaying consecutive spaces, I cannot display the two spaces within the double quotes in my posting.

To explain, the line:

echo $i " " `grep $i file2 | cut -d" " -f2-`

is explicitly the following:

echo $i "<1st space><2nd space>" `grep $i file2 | cut -d" " -f2-`

Hope this helps. Regards.

Steven Sim Kok Leong

Re: merging of columns from two files

Steve Steel — Tue, 28 May 2002 13:37:17 GMT

Hi

use typeset

I copied your files to $HOME/tmp f1 f2 on my machine

#!/bin/ksh
cd
typeset -L6 a=$1
typeset -R6 b=$2
typeset -R7 d=$4
paste tmp/f1 tmp/f2|while read a b c d
do
echo "$a$b$d"
done

Gives

Time cpu memory
0:00 45 15
0:15 72 82
0:30 15 15
0:45 95 25
1:00 1:00
1:15 1:15
1:30 1:30

That is neat enough

Steve Steel

Re: merging of columns from two files

Narasimham — Tue, 28 May 2002 14:05:16 GMT

Rod Hills

Your join command working fine for me

Thanks for the help

Narasimham