Split by cols

Problem

I have a file with thousand of rows, each row has a constant number of cols separated by semicolons (;) . I want to get a list of the second column in one row separated by commas (,)

Here are the first 6 lines of the input file:

204475_at;MMP1;-0.5620757464654499;-0.5378347048965268;-0.36471458589162814;
206157_at;PTX3;-0.5425012879749027;-0.5495237556470851;-0.44385302287262235;
204338_s_at;RGS4;-0.42077371090541654;-0.41919829358131366;-0.5264337704008629;
201109_s_at;THBS1;-0.31155577987681304;-0.5003300095895957;-0.4446094023373488;
209278_s_at;TFPI2;-0.4120512205810878;-0.46064450303497106;-0.5142082127194167;
204337_at;RGS4;-0.46042331809786813;-0.4469095486584169;-0.5476362816287376;<

Solution

First I get the desired column, that is the second:
cut -f2 -d ‘;’ file.csv
But the output format will be like this:
MMP1
PTX3
RGS4
THBS1
TFPI2
THBS1
RGS4

I want to replace the newlines by , . This can be done with sed:

cut -f2 -d ';' file.csv | sed ':a;N;$!ba;s/\n/, /g'

I only want the first 10 lines so the final result is:

cut -f2 -d ';' file.csv | sed ':a;N;$!ba;s/\n/, /g'

The output will be:

, MMP1, PTX3, RGS4, THBS1, TFPI2, THBS1, RGS4, DKK1, GREM1, PHLDA2

If we only want unique values:

cut -f2 -d ';' file.csv | sort | uniq | sed ':a;N;$!ba;s/\n/, /g'
Advertisements

2 thoughts on “Split by cols

  1. Nice blog man,

    In your second example I think you want head not nead. Also, you may be interested in checking out awk, you can do a lot of this in one command 😉

    Extract the second field of the first 10 lines, separating them by commas.

    $ awk -F ';' 'NR < 10 {printf("%s, ", $2);}' data.txt 
    MMP1, PTX3, RGS4, THBS1, TFPI2, RGS4, 
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s