Add index column to CSV file

Question

I have a large Comma-Separated File (6GB) and would like to add an index column to it. I'm looking at Unix type solutions for efficiency. I'm using a Mac.

I have this:

V1  V2  V3
0.4625  0.9179  0.8384
0.9324  0.2486  0.1114 
0.6691  0.7813  0.6705
0.1935  0.3303  0.4336

Would like to get this:

ID  V1  V2  V3
1   0.4625  0.9179  0.8384
2   0.9324  0.2486  0.1114
3   0.6691  0.7813  0.6705
4   0.1935  0.3303  0.4336

Some platform information would be useful here. Answers so far have assumed a Unix-like platform. But maybe the OP is on Windows. Do you have a particular tool in mind? — LondonRob, May 29 '15 at 13:43
@LondonRob - I'm using a Mac. I kept the question vague because I know very little about Unix. — SharkSandwich, May 29 '15 at 13:53
@SharkSandwich, if it's comma separated then you should have shown that in your question. — user3439894, May 29 '15 at 13:59

score 16 · Accepted Answer · edited May 29 '15 at 13:22

16

This will probably work:

awk -F'\t' -v OFS='\t' '
  NR == 1 {print "ID", $0; next}
  {print (NR-1), $0}
' input.csv > output.csv

In awk, the NR variable is "the total number of input records seen so far", which in general means "the current line number". So the NR == 1 in the first line is how we match the first record and add the "ID" column header, and for the remaining lines we use NR-1 as the index.

The -F'\t' argument sets the input field separator, and -vOFS='\t' sets the output field separator.

edited May 29 '15 at 13:22

Tom Fenech

72,334
12
107
141

answered May 29 '15 at 13:10

larsks

277,717
41
399
399

Looks good, although personally I would remove the `-F` as the default will do. – Tom Fenech May 29 '15 at 13:21
any way to make it faster? or ideally to use multiple cores? – jangorecki Mar 26 '19 at 04:33
@AbdulMoiz I'm not sure what you're talking about. If you run this script against the sample input data, it produces exactly the required output. The output is a perfectly standard tab-separated file, which is what was requested by the OP. – larsks May 23 '20 at 03:10
@larsks Sorry my bad – Abdul Moiz May 23 '20 at 08:38

score 5 · Answer 2 · edited Jun 20 '20 at 09:12

5

Since no technology is specified in the original post, I'd be happy here to keep it simple.

(all the fancy Vim/bash solutions are fine if you know what you're doing).

Open the CSV file in your favourite spreadsheet programme (I'm using LibreOffice, but Excel or a native Mac equivalent will do)
insert a column to the left of column A
Enter a 1 into cell A2, the first cell under the headers
Double-click the blob at the bottom right of the cell as shown in the screenshot:

LibreOffice wizardry

This last step will fill the index column with 1,2,3... etc. You can then save the resulting spreadsheet as a CSV file again.

edited Jun 20 '20 at 09:12

Community

1
1

answered May 29 '15 at 14:07

LondonRob

73,083
37
144
201

Thanks for replying. I can't use a spreadsheet program due to the size of the file. It is approximately 6GB. That was why I was looking at Unix type solutions. – SharkSandwich May 29 '15 at 14:22
1

Why don't you add to the original post all the detail which you've added in the various comments to various answers? It'll save more people trying to guess what your situation is. – LondonRob May 29 '15 at 14:51

score 3 · Answer 3 · edited May 23 '17 at 10:30

3

I assume you have a commas delimited file.

Using vim, open the file. In normal mode, type

:%s/^/\=line('.').','/

:%s/^/\=line('.')/ adds the line number at the beginning of the line. Since you have a commas delimited file (add a column) you need a comma after your line number. so the .','

see this answer for full explanation about :%s/^/\=line('.')/

edited May 23 '17 at 10:30

Community

1
1

answered May 29 '15 at 13:41

Luc M

16,630
26
74
89

score 0 · Answer 4 · answered Jan 07 '19 at 17:36

Open the CSV file in your favorite spreadsheet program, such as Excel
Insert a column to the left side of first column
Type 1 in the first cell of this column
Type an equation '=A2+1' in the following cell

Double-click the blob at the bottom right of the cell as shown in the screenshot

Add index column to CSV file

4 Answers4