LinuxQuestions.org

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

jaufer 06-28-2010 01:49 AM

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

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 (Post 4016898)
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 (Post 4016898)
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. ;)


All times are GMT -5. The time now is 04:42 AM.