merging of columns from two files

[Narasimham]

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

[Patrick Wallek]

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.

[MANOJ SRIVASTAVA]

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

[Narasimham]

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

[MANOJ SRIVASTAVA]

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

[Narasimham]

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

[Rodney Hills · Accepted solution]

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

[Steven Sim Kok Leong]

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

[Suhas_3]

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

[Tom Geudens]

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

Hope this helps,
Tom

[Narasimham]

Hi....Steven

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

Thanks
Narasimham

[Steven Sim Kok Leong]

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

[Steven Sim Kok Leong]

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

[Steve Steel]

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

[Narasimham]

Rod Hills


Your join command working fine for me

Thanks for the help

Narasimham