LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 06-28-2010, 01:49 AM   #1
jaufer
LQ Newbie
 
Registered: Jun 2010
Posts: 1

Rep: Reputation: 0
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
 
Old 06-29-2010, 12:24 PM   #2
jork
LQ Newbie
 
Registered: Jun 2010
Location: beweth
Distribution: Ubuntu, RHEL, Monta Vista, QNX
Posts: 16

Rep: Reputation: 0
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
 
Old 06-29-2010, 12:42 PM   #3
TB0ne
Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 14,223

Rep: Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474Reputation: 2474
Quote:
Originally Posted by jaufer View Post
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.
 
Old 06-29-2010, 05:19 PM   #4
CoderMan
Member
 
Registered: Jan 2009
Location: Gemini Capsule 25164
Distribution: Gentoo
Posts: 375
Blog Entries: 24

Rep: Reputation: 43
Quote:
Originally Posted by jaufer View Post
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.
 
  


Reply

Tags
unix


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert columns to rows (tab separated file to csv) doug23 Programming 16 08-16-2009 09:14 PM
Script to convert logs columns to rows fono Linux - Software 10 05-19-2009 08:29 PM
How to print data in rows and columns suran Linux - General 3 03-15-2009 02:53 PM
text data conversion: rows into columns frankie_DJ Programming 6 06-03-2006 06:43 AM
rows and columns digitalgravy Linux - General 2 03-16-2004 06:47 PM


All times are GMT -5. The time now is 07:53 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration