replace column name with file name shell script

Question

How to replace the 2nd column name in a .csv file with its corresponding file name for all files within a directory? Does anyone know how to do this with shell scripting? sed or awk

input file name CDXV1.csv

Gene,RPKM(26558640 pairs)
ENSTGUG00000013338 (GAPDH),971.678203888
ENSTGUG00000005054 (CAMKV),687.81249397
ENSTGUG00000006651 (ARPP19),634.296191033
ENSTGUG00000002582 (ITM2A),613.756010638

output file name CDXV1.csv (same)

Gene,CDXV1(26558640 pairs)
ENSTGUG00000013338 (GAPDH),971.678203888
ENSTGUG00000005054 (CAMKV),687.81249397
ENSTGUG00000006651 (ARPP19),634.296191033
ENSTGUG00000002582 (ITM2A),613.756010638

What did you try for yourself? Post your research attempts even if they aren't successful — Inian
– Inian, Commented Jun 8, 2017 at 6:53

RomanPerekhrest · Accepted Answer · 2017-06-08 07:27:58Z

1

sed solution:

for f in yourdir/*.csv; do sed -i "1s~^\([^,]*\),\([^(]*\)~\1,${f%%.*}~g" "$f"; done

Details:

for f in yourdir/*.csv - iterating through csv filenames
-i - modify the file in-place
1s - perform substitution only on the 1st line
~ - used as sed subcommand sections separator
^$[^,]*$,$[^(]*$ - capturing the 1st field and 2nd field value (till encountering ()
${f%%.*} - bash variable substitution, truncate right of the first .

edited Jun 8, 2017 at 7:27

answered Jun 8, 2017 at 7:08

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Madza Farias-Virgens Over a year ago

That worked! thanks! Would you mind explaining the command? I'm just getting into this stuff.

RomanPerekhrest Over a year ago

@MadzaYasodaraFariasVirgens, yes, see my details

P.... · Accepted Answer · 2017-06-08 07:18:06Z

1

awk -F, -v OFS=,  'NR==1{split(FILENAME,a,".");split($2,b,"(");$2= a[1] "(" b[2]}1' CDXV1.csv
Gene,CDXV1(26558640 pairs)
ENSTGUG00000013338 (GAPDH),971.678203888
ENSTGUG00000005054 (CAMKV),687.81249397
ENSTGUG00000006651 (ARPP19),634.296191033
ENSTGUG00000002582 (ITM2A),613.756010638

If your awk support inplace replacement then use -i inplace :

awk -i inplace -F, -v OFS=,  'NR==1{split(FILENAME,a,".");split($2,b,"(");$2= a[1] "(" b[2]}1' *.csv

edited Jun 8, 2017 at 7:18

answered Jun 8, 2017 at 6:59

P....

18.6k3 gold badges36 silver badges55 bronze badges

3 Comments

Inian Over a year ago

OP needs to apply this for all .csv files and also someway to modify the file in-place.

RavinderSingh13 Over a year ago

Thank you for nice code PS, could you please let me know which version of awk supports this, as I have gawk 4.0.1 and it is not having this option.

P.... Over a year ago

@RavinderSingh13 In latest GNU Awk (since 4.1.0 released), it has the option of "inplace" file editing check for more info stackoverflow.com/questions/16529716/…

Collectives™ on Stack Overflow

replace column name with file name shell script

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related