Convert columns of data into rows

jaufer · 06-28-2010, 01:49 AM

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

jork · 06-29-2010, 12:24 PM

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

TB0ne · 06-29-2010, 12:42 PM

Quote:

Originally Posted by jaufer

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

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

CoderMan · 06-29-2010, 05:19 PM

Quote:

Originally Posted by jaufer

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

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.