I have a bunch of directories in which there are 3 .csv files with different names. For example, in my directories aa bb cc dd there are 3 files in each:

aa:  EA_sing_aa.csv    EA_ska_aa.csv   EA_tat_aa.csv
bb:  EA_sing_bb.csv    EA_ska_bb.csv   EA_tat_bb.csv
cc:  EA_sing_cc.csv    EA_ska_cc.csv   EA_tat_cc.csv
dd:  EA_sing_dd.csv    EA_ska_dd.csv   EA_tat_dd.csv

I want to add the name of each file to a new column as row names to each files and then combine all EA_sing*.csv files together and combine all EA_ska*.csv files together and also combine all EA_tat*.csv files together! my out put will be just 3 files:

1) EA_sing.csv  ##the first column for the rows from EA_sing_aa.csv file
                will be aa and for the rows from EA_sing_bb.csv will be bb
                and for the rows from EA_sing_cc.csv will be cc..... ##   
2) EA_ska.csv
3) EA-tat.csv

How can I do this in *nix? Thanks

  • Slow down and ask one clear question then build off that. The way it's asked, it's unnecessarily difficult to parse and unlikely to be of use to others. – d-cubed Jul 24 '15 at 14:36
  • I don't understand this question. I'm really trying. – mikeserv Jul 24 '15 at 19:01
  • Basically, he has a lot of directories each with 3 CSVs with 3 name formats. He wants to iterate through the directories, consolidate all the similarly named files - with the file (or directory name?) as the leading column. – d-cubed Jul 24 '15 at 21:35
  • Please edit your question and show us an example of your input files and an example of your desired output files. How are the columns defined? Commas? Spaces? Tabs? – terdon Jul 26 '15 at 11:27

This is an off the cuff answer:

for dir in $(ls ); do echo " $dir - this is the directory"; for csv in $(ls $dir/*csv); do cat $csv|sed -e "s/\(.*\)/$dir, \1/g"; done;  done

You could then add options to specify file names, e.g.

for myfile in EA_sing EA_ska EA-tat; do for csv in $(ls $dir/$myfile*)...

and then pipe them to a file of your choice, e.g.

do cat $csv|sed -e "s/\(.*\)/$dir, \1/g" >> $(echo "$myfile_complete.csv")

which might then be combined into something like this:

for dir in aa bb cc dd; do for file in EA_sing EA_ska EA_tat; do for myfile in $(ls $dir/$file*);do echo "parsing $myfile"; cat $myfile | sed -e "s/\(.*\)/$dir,\1/g" >> $(echo "$file\_combined.csv"); done; done; done

Or more to the point:

for dir in $(ls -d */); do for file in $(ls $dir*csv); do echo "this is my file $file"; cat $file|sed -e "s|\(.*\)|$(echo $dir|sed -e 's/\///g'), \1|g";done; done
| improve this answer | |
  • Comments are not for extended discussion; this conversation has been moved to chat. – terdon Jul 26 '15 at 11:27

You should also consider using find - another very useful approach - e.g. look at:

find . -name "*csv" -exec grep "" {} + | sed -e 's/:/,/g' -e 's/\.\///g'

This is very similar to the questions described here: Concatenate filename inside of CSV file for each line for multiple CSV files?

See also:




| improve this answer | |

Relying on the filename always being correct

for t in $(find . -iname "*.csv" | awk -F_ '{print $2}' | sort | uniq); do 
    awk -v OFS="," 'split(FILENAME,f,/[_.]/){print $0,f[4]}' $(find . -iname "*$t*.csv" | sort) > EA_$t.csv
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.