Modifying col values based on another col Post: 302761281

Sponsored Content

Top Forums Shell Programming and Scripting Modifying col values based on another col Post 302761281 by newbie83 on Friday 25th of January 2013 11:36:57 AM

01-25-2013

Registered User

Modifying col values based on another col

Hi,

Please help with this.
I have several excel files (with and .xlsx format) with 10-15 columns each.
They all have the same type of data but the columns are not ordered in the same way.

Here is a 3 column example. What I want to do add the alphabet
from column 2 to column 3, provided they both have the keyword 'BAM'
in them. Also the prefix 12 needs to be removed (if it is present before BAM).
Also the second column may have a space between BAM and 2A, that space needs to be ignored. So BAM2A is the same as BAM 2A, similarly if space is present in 3rd col, that has to be removed in the output.
So 12BAM2 -1 becomes BAM2A-1.
The last row remains unchanged because both column dont have BAM.

Input sample

Code:

20	12BAM 2A		12BAM2 -1
20	12BAM 2A		12BAM2 -2
50	12BAM2A   		12BAM2 -3
20	12BAM2B  		12BAM2 -4
20	12BAM 2B		12BAM2 -5
520	12BAM 2A		XA3023

Expected output

Code:

20	12BAM 2A		BAM2A-1
20	12BAM 2A		BAM2A-2
50	12BAM2A  	   	BAM2A-3
20	12BAM2B  		BAM2B-4
20	12BAM 2B		BAM2B-5
520	12BAM 2A		XA3023

newbie83

View Public Profile for newbie83

Find all posts by newbie83

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Help On col command

Hello Can Any1 tell me the difference between the col command and the col command with the -f option. I tried running both of them but i can't see any difference. Please guide me.

2. UNIX for Dummies Questions & Answers

Info about col command

Hello every1. Can any1 help me with the col command. Wat is a reverse line feed. Which kind of files u need to use the col command.

3. Shell Programming and Scripting

Awk to print distinct col values

Hi Guys... I am newbie to awk and would like a solution to probably one of the simple practical questions. I have a test file that goes as: 1,2,3,4,5,6 7,2,3,8,7,6 9,3,5,6,7,3 8,3,1,1,1,1 4,4,2,2,2,2 I would like to know how AWK can get me the distinct values say for eg: on col2...

4. Ubuntu

Match col 1 of File 1 with col 1 File 2 and create a 3rd file

Hello, I have a 1.6 GB file that I would like to modify by matching some ids in col1 with the ids in col 1 of file2.txt and save the results into a 3rd file. For example: File 1 has 1411 rows, I ignore how many columns it has (thousands) File 2 has 311 rows, 1 column Would like to...

5. Shell Programming and Scripting

i can't cut the third col

SW_dist_intr false Enable SW distribution of interrupts True autorestart true Automatically REBOOT OS after a crash True boottype disk N/A False capacity_inc 1.00 ...

6. Shell Programming and Scripting

How to Delete the First Blank of the First Col?

Hi to all. In the following example, how can I delete the first blank of the first col? (using shell scripting) first second third fourth fifth sixth seventh eighth Thank's for reading.

7. UNIX for Advanced & Expert Users

Print line based on highest value of col (B) and repetion of values in col (A)

Hello everyone, I am writing a script to process data from the ATP world tour. I have a file which contains: t=540 y=2011 r=1 p=N409 t=540 y=2011 r=2 p=N409 t=540 y=2011 r=3 p=N409 t=540 y=2011 r=4 p=N409 t=520 y=2011 r=1 p=N409 t=520 y=2011 r=2 p=N409 t=520 y=2011 r=3 p=N409 The...

8. Shell Programming and Scripting

Printing from col x to end of line, except last col

Hello, I have some tab delimited data and I need to move the last col. I could hard code it, awk '{ print $1,$NF,$2,$3,$4,etc }' infile > outfile but it would be nice to know the syntax to print a range cols. I know in cut you can do, cut -f 1,4-8,11- to print fields 1,...

9. Shell Programming and Scripting

How to mark the row based on col value.?

Hi Gurus, I have requirement to identify the records based on one column value. the sample file as below: ID AMT, AMT1 100,10, 2 100,20, 3 200,30, 0 200, 40, 0 300, 20, 2 300, 50, 2 400, 20, 1 400, 60, 0 for each ID, there 2 records, if any one record amt1 is 0, the in 4th col add...

10. Emergency UNIX and Linux Support

Read values in each col starting 3rd row.Print occurrence value.

Hello Friends, Hope all are doing fine. Here is a tricky issue. my input file is like this 07 10 14 20 21 03 15 27 30 32 01 10 11 19 30 02 06 14 15 17 01 06 20 25 29 Logic: 1. Please print another column as "0-0-0-0-0" for the first and second rows. 2. Read the first column...

LEARN ABOUT DEBIAN

bam

BAM(1)								   User Commands							    BAM(1)

NAME

       bam - fast and flexible build system

SYNOPSIS

       bam [OPTION]... [VARIABLE=VALUE]... [TARGET]...

DESCRIPTION

       Builds applications using the bam build system.

	      Execution:

       -a     abort on error

       -c     clean targets

       -d     build targets that is dependent given targets

       --dry  dry run, don't run any jobs

       -j     sets the number of threads to use (default: 0, disabled)

       -s     bam file to use (default: bam.lua)

	      Lua:

       -l     print local variables in backtrace

       -t     print backtrace when an error occurs

	      Output:

       -r     build progress report format (default: s) b = progress bar c = use ansi colors s = build steps

       -v     be verbose

	      Other:

       -n     don't use cache

       -h, --help
	      prints this help

	      Debug:

       --debug-nodes
	      prints all the nodes with dependencies

       --debug-detail
	      prints all the nodes with dependencies and details

       --debug-jobs
	      prints all the jobs that exist

       --debug-dot
	      prints all nodes as a graphviz dot file

       --debug-jobs-dot
	      prints all jobs as a graphviz dot file

       --debug-trace-vm
	      prints a line for every instruction the vm makes

       --debug-dump-int
	      prints the internals scripts to stdout

       --debug-no-int
	      don't load internal scripts

       bam by Magnus Auvinen (magnus.auvinen@gmail.com)

bam								     May 2011								    BAM(1)