insert a character between instance of two characters

Question

I have a csv file with X amount of lines in the following format

P26439,341,P,L,,P26439,,-0.41

where everytime there is a double comma i.e. ,, I want to insert the word NA in between them so it would appear as ,NA,

would anyone have a sed, tr, perl or awk solution?

I know that I can replace a character using tr in the following way:

tr ",," ",NA,"

however this replaces any instance of a comma.

tr only replaces single characters. for example, `tr abc ABC` changes any a, b, or c to upper case — ysth, Dec 21 '14 at 21:35
Can you not have an empty field at the start or end of a line too? — Ed Morton, Dec 21 '14 at 23:16

score 2 · Answer 1 · answered Dec 21 '14 at 21:19

2

Try doing this with sed :

sed 's/,,/,NA,/g' file.csv

tr is for just one character at a time.

And if you want to edit the file in place, add the -i switch :

sed -i.bak 's/,,/,NA,/g' file.csv

answered Dec 21 '14 at 21:19

Gilles Quénot

173,512
41
224
223

score 2 · Accepted Answer · answered Dec 21 '14 at 21:26

2

If you have the possibility for sequential missing fields you will need to do:

$ cat file
P26439,341,P,L,,P26439,,-0.41
P26439,341,P,L,,,P26439,,-0.41
P26439,341,P,L,,,,P26439,,-0.41

$ sed ':a;s/,,/,NA,/;ta' file
P26439,341,P,L,NA,P26439,NA,-0.41
P26439,341,P,L,NA,NA,P26439,NA,-0.41
P26439,341,P,L,NA,NA,NA,P26439,NA,-0.41

answered Dec 21 '14 at 21:26

Chris Seymour

83,387
30
160
202

that is a good point. have just noticed this problem when using the first answer on here – brucezepplin Dec 21 '14 at 21:46
What means `:a` and `ta` iiSeymour ? – Gilles Quénot Dec 21 '14 at 21:47
@sputnick it's [conditional branching](http://www.thegeekstuff.com/2009/12/unix-sed-tutorial-6-examples-for-sed-branching-operation/) – Chris Seymour Dec 21 '14 at 21:59
assuming the NA is not for first and last field (as the request mention so ok) – NeronLeVelu Dec 22 '14 at 06:15

score 1 · Answer 3 · answered Dec 21 '14 at 21:24

1

Perl solution:

$ echo "P26439,341,P,L,,P26439,,-0.41" | perl -pe 's/,,/,NA,/g'
P26439,341,P,L,NA,P26439,NA,-0.41

answered Dec 21 '14 at 21:24

Fredrik Pihl

44,604
7
83
130

You might want to add a little more explanation here-- this answer was flagged by the system as "low quality" – Brian Rogers Dec 21 '14 at 23:07

score 0 · Answer 4 · answered Dec 22 '14 at 06:13

sed ':cycle
s/,,/,NA/g;t cycle
s/^,/NA,/;s/,$/,NA/' YourFile

set NA for any field, including first and last.
The cycle is needed for multiple following occurance ,,, where only the first ,, is treated at first cycle leaving the second (and any other multitple of 2 occurences) unchanged

score 0 · Answer 5 · answered Dec 22 '14 at 07:29

Through awk:

awk -F',' '{for (i=1;i<=NF;i++) if ($i=="") $i="NA" }1' OFS=','  file

Replace each empty fields in first,middle,last or if it was multiple occurrences with NA string.

Input:

,P26439,341,P,L,,P26439,,-0.41
P26439,341,P,L,,P26439,,-0.41
P26439,341,P,L,,P26439,,-0.41,
P26439,341,P,L,,,,P26439,,-0.41

Output:

NA,P26439,341,P,L,NA,P26439,NA,-0.41
P26439,341,P,L,NA,P26439,NA,-0.41
P26439,341,P,L,NA,P26439,NA,-0.41,NA
P26439,341,P,L,NA,NA,NA,P26439,NA,-0.41

insert a character between instance of two characters

5 Answers5

Input:

Output: