LinuxQuestions.org - Convert columns of data into rows

- Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)

- - Convert columns of data into rows (https://www.linuxquestions.org/questions/linux-software-2/convert-columns-of-data-into-rows-816747/)

Convert columns of data into rows

Hi,

I have the following CSV file (file name system1.csv);

TIME,data_in1,data_in2,data_out1,data_out2,Total_in,total_out
0900,10,9,5,10,15,24
1000,11,10,6,11,17,26
1100,12,11,7,12,19,28
1200,13,12,8,13,21,30
1300,14,13,9,14,23,32
1400,15,14,10,15,25,34

Now, I need to create an out put file, last two columns as a row of data (using the first columns as heading) with the file name as first row. For an example the out put file should look like;

system_name,0900_in,1000_in,1100_in,1200_in,1300_in,1400_in,0900_out,1000_out,1100_out,1200_out,1300 _out,1400_out
system1,15,17,19,21,23,25,24,26,28,30,32,34

I need to run a script to do above conversion. Need your help.

Jaufe

Use "cut -c<column-beging>-<column-end> <input-file>" for extracting from columns from a file.

for example, in your case "cut -c1-4 input_file" for extracting first 4 chars from each line.

Also if you have a known delimiter for the to-be-extracted column, you can use "cut -d" along with delimiter.

There can be a million other ways to do the same. these are the easiest IMHO.

Hope this helps
-jork

Quote:

Originally Posted by jaufer (Post 4016898)

We'll be glad to help. Post what you've written so far, and where you're getting stuck.

Quote:

Originally Posted by jaufer (Post 4016898)

Just learned Ruby, thought it would be a fun opportunity to try it out:

Code:

#!/usr/bin/env ruby



# Coded by Christopher Howard :)



if ARGV.length > 0 then

  puts "system_name,0900_in,1000_in,1100_in,1200_in,1300_in,1400_in,0900_out,1000_out,1100_out,1200_out,1300_out,1400_out"

end



ARGV.each do|file_path|

  data = {}

  IO.foreach(file_path) do |line|

    if $. != 1 then

      raw_array = line.chomp.split(',')

      data[raw_array[0]] = { 'in' => raw_array[5],

                            'out' => raw_array[6] }

    end

  end

  print File.basename(file_path, '.csv') + ','

  data.keys.sort.each do |time|

    print data[time]['in'] + ','

  end

  data.keys.sort.each do |time|

    if time == data.keys.sort.last

      print data[time]['out'] + "\n"

    else

      print data[time]['out'] + ','

    end

  end

end

There is some unnecessary repetition of code in there, but I'm too busy to fix it. Anyway, just execute the script with the name of each csv file after it on the command line.

My royalty fee is only 3 cents per script execution or 1 cent per file processed -- which ever is more expensive. ;)