Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Merge 2 columns/remove specific spaces

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge 2 columns/remove specific spaces
# 1  
Old 02-07-2011
Merge 2 columns/remove specific spaces
Hi,

I have a requirement to remove certain spaces from a table of information, but I'm unsure where to start.
A typical table will be like this:

Code:
ABCDE 1 Elton John 25 12 15 9 3
ABCDE 2 Oasis 29 13 4 6 9
ABCDE 3 The Rolling Stones 55 19 3 8 6

The goal is to remove only the spaces between the artist names (where more than 1 name exists), but leave everything else the same, like this:
Code:
ABCDE 1 EltonJohn 25 12 15 9 3
ABCDE 2 Oasis 29 13 4 6 9
ABCDE 3 TheRollingStones 55 19 3 8 6

I can't work out how to do this as the number of columns will vary depending on how many names the artist has. To complicate matters, some artists will also have numbers in their names. The number of columns before and after the artist names will always be the same, if that helps? Please help, very stuck and can't see a way around it!
Last edited by radoulov; 02-07-2011 at 11:40 AM.. Reason: Code tags, please!
# 2  
Old 02-07-2011
You could try something like this:

Code:
sed 's/\([^0-9]\)  *\([^0-9]\)/\1\2/g' infile

If it doesn't work, please post a bigger sample.
# 3  
Old 02-07-2011
Sorry, still inconsistent results with the whole dataset. I was trying to keep things simple with a small amount, but here's a much better dataset to work with:
Code:
ENGPREM    1    Man Utd    25    12    1    0
ENGPREM    2    Arsenal    25    8    1    3
ENGPREM    3    Man City    26    8    3    2
ENGPREM    4    Chelsea    25    8    2    2
ENGPREM    5    Tottenham    25    7    5    1
ENGPREM    6    Liverpool    26    8    3    2
ENGPREM    7    Sunderland    26    6    5    2
ENGPREM    8    Bolton    26    6    5    2
ENGPREM    9    Stoke    25    7    2    4
ENGPREM    10    Newcastle    25    4    5    4
ENGPREM    11    Blackburn    26    6    3    4
ENGPREM    12    Fulham    26    5    5    3
ENGPREM    13    Everton    25    4    5    3
ENGPREM    14    Aston Villa    26    5    5    3
ENGPREM    15    Blackpool    25    3    2    6
ENGPREM    16    Birmingham    24    3    7    2
ENGPREM    17    West Brom    25    5    3    4
ENGPREM    18    Wigan    26    3    6    5
ENGPREM    19    Wolverhampton    25    6    2    5
ENGPREM    20    West Ham United    26    3    4    6

Last edited by radoulov; 02-07-2011 at 12:30 PM.. Reason: Code tags, please!
# 4  
Old 02-07-2011
Code:
sed 's/\([^0-9 \t]\)  *\([^0-9 \t]\)/\1\2/g' infile

# 5  
Old 02-07-2011
When I run this, I'm getting the following out:
Code:
ENGPREM	1	ManUtd	25	12	1	0
ENGPREM	2	Arsenal	25	8	1	3
ENGPREM	3	ManCity	26	8	3	2
ENGPREM	4	Chelsea	25	8	2	2
ENGPREM	5	Tottenham	25	7	5	1
ENGPREM	6	Liverpool	26	8	3	2
ENGPREM	7	Sunderland	26	6	5	2
ENGPREM	8	Bolton	26	6	5	2
ENGPREM	9	Stoke	25	7	2	4
ENGPREM	10	Newcastle	25	4	5	4
ENGPREM	11	Blackburn	26	6	3	4
ENGPREM	12	Fulham	26	5	5	3
ENGPREM	13	Everton	25	4	5	3
ENGPREM	14	AstonVilla	26	5	5	3
ENGPREM	15	Blackpool	25	3	2	6
ENGPREM	16	Birmingham	24	3	7	2
ENGPREM	17	West Brom	25	5	3	4
ENGPREM	18	Wigan	26	3	6	5
ENGPREM	19	Wolverhampton	25	6	2	5
ENGPREM	20	West HamUnited	26	3	4	6

So almost there, but issues with lines 17 and 20...
# 6  
Old 02-07-2011
OK,
it seems that your sed implementations doesn't interpolate the \t escape sequence.
I put it only to be safer, so you can just remove it:

Code:
sed 's/\([^0-9 ]\)  *\([^0-9 ]\)/\1\2/g' infile

This User Gave Thanks to radoulov For This Post:
danhodges99
# 7  
Old 02-07-2011
What about?
I assumed that was a single space between everything.
(Looks like that original file was replaced with a file that used tabs... hmmmm...)

And I only worked with team names that were one-two-three words.

Code:
$ awk '{sub(" ","~"); sub(" ","~"); if(NF>=6) sub(" ","") ;if (NF==6) sub(" ","") ;gsub("~"," "); print $0}' <sample5.txt
ENGPREM 1 ManUtd 25 12 1 0
ENGPREM 2 Arsenal 25 8 1 3
ENGPREM 3 ManCity 26 8 3 2
ENGPREM 4 Chelsea 25 8 2 2
ENGPREM 5 Tottenham 25 7 5 1
ENGPREM 6 Liverpool 26 8 3 2
ENGPREM 7 Sunderland 26 6 5 2
ENGPREM 8 Bolton 26 6 5 2
ENGPREM 9 Stoke 25 7 2 4
ENGPREM 10 Newcastle 25 4 5 4
ENGPREM 11 Blackburn 26 6 3 4
ENGPREM 12 Fulham 26 5 5 3
ENGPREM 13 Everton 25 4 5 3
ENGPREM 14 AstonVilla 26 5 5 3
ENGPREM 15 Blackpool 25 3 2 6
ENGPREM 16 Birmingham 24 3 7 2
ENGPREM 17 WestBrom 25 5 3 4
ENGPREM 18 Wigan 26 3 6 5
ENGPREM 19 Wolverhampton 25 6 2 5
ENGPREM 20 WestHamUnited 26 3 4 6
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

To remove double quotes from specific columns

Hi, I've a requirement like, in a csv file of 30+ fields where all the columns are having double quotes I need to remove the double quotes from certain fields and certain field should remain as it is. Eg:... (6 Replies)
Discussion started by: Krishnanth S
6 Replies

2. Shell Programming and Scripting

Merge specific columns of two files

Hello, I have two tab delimited text files. Both files have the same number of rows but not necessarily the same number of columns. The column headers look like, File 1: f0order CVorder Name f0 RI_9 E99 E199 E299 E399 E499 E599 E699 E799 E899 E999 File 2:... (9 Replies)
Discussion started by: LMHmedchem
9 Replies

3. Shell Programming and Scripting

Remove first n characters from specific columns

I have a file like: s_20331 803 1 1 5 1:2=0.00000000 1:3=0.00000000 1:4=0.11111111 s_20331 814 1 1 5 1:2=0.00000000 1:3=0.12611607 1:4=0.00000000I would like to remove the four characters "x:x=" from all columns containing them (in my actual file, there are 15 total columns (i.e. columns... (1 Reply)
Discussion started by: pathunkathunk
1 Replies

4. Shell Programming and Scripting

How to remove spaces between the columns in UNIX script?.

Hi guru's, I am trying to write a script to generate a csv file by connecting to database run a query and put the values into csv file. But the problem i face is i am getting lot of space after one value.how can i remove those values?. Please help. #!/bin/bash export... (2 Replies)
Discussion started by: karingulanagara
2 Replies

5. UNIX for Dummies Questions & Answers

How to count specific columns and merge with unique ones?

Hi. I am not sure the title gives an optimal description of what I want to do. I have several text files that contain data in many columns. All the files are organized the same way, but the data in the columns might differ. I want to count the number of times data occur in specific columns,... (0 Replies)
Discussion started by: JamesT
0 Replies

6. Shell Programming and Scripting

remove white space from specific columns in text file

Hello i have a text file like this: 1 AB AC AD EE 2 WE TR YT WW 3 AS UY RF YT the file is bigger , but that's an example of the data what i want to do is to merge all columns together except the first one, it will become like this : 1 ABACADEE 2 WETRYTWW 3 ASUYRFYT (8 Replies)
Discussion started by: shelladdict
8 Replies

7. UNIX for Dummies Questions & Answers

How to remove columns with specific string?

If I have a data test.txt with 1000 columns such as: id sex gene1 gene2 gene2.dl gene3 gene4 gene4.dl ....... 1 1 AA AT AT TT AT AT ....... 2 1 AG TT TT TA AA AA ....... 3 2 AA AT AT TT AT ... (2 Replies)
Discussion started by: AMBER
2 Replies

8. Shell Programming and Scripting

merge columns into one line after a specific pattern

Hi all, im a linux newbie, plz help! I have a file - box -------- Fox-2 -------- UF29 zip42 -------- zf-CW SNF2_N Heli_Z -------- Fox -------- Kel_1 box (3 Replies)
Discussion started by: sam_2921
3 Replies

9. Shell Programming and Scripting

Remove spaces from columns

I have a file with only one field something like this : 213.45 220.65 240.47 345.67 320.45 I want to remove all spaces in between. Is there any unix command for that ?Thanks in advance.. (2 Replies)
Discussion started by: jacks
2 Replies
Login or Register to Ask a Question