delete a column with awk or sed

Question

I have a file with three columns. I would like to delete the 3rd column(in-place editing). How can I do this with awk or sed?

123   abc  22.3
453   abg  56.7
1236  hjg  2.3

Desired output

123  abc
453  abg
1236 hjg

[For deleting two columns](https://unix.stackexchange.com/questions/222121/how-to-remove-a-column-or-multiple-columns-from-file-using-shell-command) — Josiah Yoder, Sep 06 '22 at 13:35

score 71 · Answer 1 · answered Mar 12 '13 at 13:00

71

try this short thing:

awk '!($3="")' file

answered Mar 12 '13 at 13:00

Kent

189,393
32
233
301

52

This doesn't actually delete the given column; it sets it to the empty string, but you still get an extra `FS` in your output. This may or not be important, depending on what you're doing with the transformed data. – larsks Mar 17 '14 at 14:04
try this to save the generate output to a newfile. awk '!($3="")' file > newfile – Saurabh Rana Apr 09 '15 at 20:05
3

@A.Danischewski this is not good way to go, what happens if the awk script has error? you lost your file. take this `awk '..' file > tmp && mv tmp file` – Kent Jul 07 '16 at 08:24
@A.Danischewski there are other circumstances that can affect: what if the file system has problems of space? – fedorqui Jul 08 '16 at 11:41
3

That will also recompile the current record replacing all white space between fields with single blank characters and removing any leading and/or trailing white space. It will not produce the OPs desired output given his posted input. To do that you need http://stackoverflow.com/a/38145415/1745001. – Ed Morton Feb 15 '17 at 21:07
You can use `awk '!($3="")' | column -t` to get the columns white space back back – Dave May 02 '22 at 21:49
This doesn't delete anything for me. It just creates a bunch of empty columns. In some sense, it does the opposite of what is desired. – Nike Aug 18 '23 at 02:52

score 43 · Answer 2 · edited May 23 '17 at 12:10

43

With GNU awk for inplace editing, \s/\S, and gensub() to delete

1) the FIRST field:

awk -i inplace '{sub(/^\S+\s*/,"")}1' file

or

awk -i inplace '{$0=gensub(/^\S+\s*/,"",1)}1' file

2) the LAST field:

awk -i inplace '{sub(/\s*\S+$/,"")}1' file

or

awk -i inplace '{$0=gensub(/\s*\S+$/,"",1)}1' file

3) the N^th field where N=3:

awk -i inplace '{$0=gensub(/\s*\S+/,"",3)}1' file

Without GNU awk you need a match()+substr() combo or multiple sub()s + vars to remove a middle field. See also Print all but the first three columns.

edited May 23 '17 at 12:10

Community

1
1

answered Jul 01 '16 at 12:24

Ed Morton

188,023
17
78
185

2

Note: on Ubuntu Trusty GNU Awk 4.0.1 doesn't have the `awk` inplace extension enabled by default. – Jul 07 '16 at 01:41
i think you mean `/\s+\S+/` with an s`+` and not `/\s*\S+/` – Brian Wiley Jan 04 '22 at 02:02
@BrianWiley no because then it wouldn't work for the first field if there were no preceding spaces. – Ed Morton Jan 04 '22 at 02:39

potong · Accepted Answer · 2013-03-12T13:05:01.110

25

This might work for you (GNU sed):

sed -i -r 's/\S+//3' file

If you want to delete the white space before the 3rd field:

sed -i -r 's/(\s+)?\S+//3' file

edited Mar 12 '13 at 13:05

answered Mar 12 '13 at 12:55

potong

55,640
6
51
83

3

@potong, is `\S` means _all characters that are not a space_ ? Where it is documented ? – Gilles Quénot Mar 12 '13 at 13:37
1

What's `-r` do? My sed doesn't have it. – Joshua Cheek Feb 18 '15 at 12:55
3

@JoshuaCheek `-r` is the GNU sed-specific option to enable EREs (google that). If you use `-E` instead of `-r` it'll work in GNU sed plus some other seds. – Ed Morton Jul 03 '16 at 12:29
1

@GillesQuenot Yes, exactly. You can see it documented in the GNU manual in the section on [regular expression extensions](https://www.gnu.org/software/sed/manual/html_node/regexp-extensions.html#regexp-extensions). – Kyle Barbour May 22 '18 at 14:33
this only works for the first row for my GNU sed – Brian Wiley Jan 04 '22 at 01:40
doesn't work for tab delimited! see comment for awk above – Brian Wiley Jan 04 '22 at 02:01

score 18 · Answer 4 · answered Mar 12 '13 at 12:51

18

It seems you could simply go with

awk '{print $1 " " $2}' file

This prints the two first fields of each line in your input file, separated with a space.

answered Mar 12 '13 at 12:51

tonio

10,355
2
46
60

3

This assumes only 3 columns. You'll otherwise need a loop: `awk '{printf $1 OFS $2; for(i=4;i<=NF;i++) printf OFS $i; printf ORS}' file` (OFS defaults to a space and ORS defaults to a newline). – Adam Katz Jul 06 '16 at 00:57

score 17 · Answer 5 · answered Aug 31 '17 at 14:55

Try using cut... its fast and easy

First you have repeated spaces, you can squeeze those down to a single space between columns if thats what you want with tr -s ' '

If each column already has just one delimiter between it, you can use cut -d ' ' -f-2 to print fields (columns) <= 2.

for example if your data is in a file input.txt you can do one of the following:

cat input.txt | tr -s ' ' | cut -d ' ' -f-2

Or if you better reason about this problem by removing the 3rd column you can write the following

cat input.txt | tr -s ' ' | cut -d ' ' --complement -f3

cut is pretty powerful, you can also extract ranges of bytes, or characters, in addition to columns

excerpt from the man page on the syntax of how to specify the list range

Each LIST is made up of one range, or many ranges separated by commas.
Selected input is written in the same order that it is read, and is
written exactly once. Each range is one of:

  N     N'th byte, character or field, counted from 1
  N-    from N'th byte, character or field, to end of line
  N-M   from N'th to M'th (included) byte, character or field
  -M    from first to M'th (included) byte, character or field

so you also could have said you want specific columns 1 and 2 with...

cat input.txt | tr -s ' ' | cut -d ' ' -f1,2

I know this isn't an answer to what has been asked, but it sure is the best answer! — Yan Foto, Nov 07 '21 at 22:02
agreed, the rest of the answers deal with white space which will not work if you have spaces in a column for a file that is tab delimited. this will work for tab delimited `cut -f1-241,243-267,269-278 -d$'\t'` which would remove columns 242 and 268. — Brian Wiley, Jan 04 '22 at 02:21
This is the actual correct way to do it. It can also easily delete multiple columns simultaneously, which the sed solution is not able to do. — Tianyi Shi, Aug 02 '22 at 07:34

Gilles Quénot · Answer 6 · 2013-03-12T13:29:12.433

7

Try this :

awk '$3="";1' file.txt > new_file && mv new_file file.txt

or

awk '{$3="";print}' file.txt > new_file && mv new_file file.txt

edited Mar 12 '13 at 13:29

answered Mar 12 '13 at 12:56

Gilles Quénot

173,512
41
224
223

score 1 · Answer 7 · answered Dec 18 '22 at 07:40

1

Try

awk '{$3=""; print $0}'

answered Dec 18 '22 at 07:40

Tony Barganski

1,873
20
17

score -1 · Answer 8 · answered Sep 11 '20 at 19:48

If you're open to a Perl solution...

perl -ane 'print "$F[0] $F[1]\n"' file

These command-line options are used:

-n loop around every line of the input file, do not automatically print every line
-a autosplit mode – split input lines into the @F array. Defaults to splitting on whitespace
-e execute the following perl code

delete a column with awk or sed

8 Answers8

Linked

Related