How do you rearrange the order of columns in a csv file?

Question

I'm trying to reorder the columns of a csv file using ruby.

Before:

$ cat foo.csv 
foo,bar,foobar
1,2,3,
4,5,6,
7,8,9,

After:

bar,foo,foobar
2,1,3,
5,4,6,
8,7,9,

Unfortunately, when I write out the file, I get extra "," values like so:

bar,foo,foobar
"2,1,3,
","5,4,6,
","8,7,9,
"

Can anyone help me identify why the extra quotation marks are showing up in the csv file?

I'm pretty new to ruby, so I'm open to other ideas if my code is less than ideal.

require 'csv' 

acsv = CSV.read("./foo.csv", {headers:true, return_headers:false})
@headers = CSV.open("./foo.csv", 'r', :headers => true).read.headers

# Rearrange headers
temp_index = @headers[0]
@headers[0]     = @headers[1]
@headers[1]     = temp_index

# Rearrange Columns
acsv.each do |row|
  temp_index = row[0]
  row[0]     = row[1]
  row[1]     = temp_index
end

puts "acsv is"
puts "#{acsv}" 

# Example to write headers http://stackoverflow.com/questions/15905985/how-to-write-columns-header-to-a-csv-file-with-ruby
newcsv = CSV.open("bar.csv", "wb", write_headers: true, headers: @headers) do |csv|
    csv << acsv
end

Update Removed unnecessary conversion of headers to headers_array

What does your CSV file look like? Why does your CSV have a trailing comma at the end? — , Apr 05 '14 at 21:26
@Cupcake I edited the question, it literally says foo,bar,foobar... — spuder, Apr 05 '14 at 21:28
The trailing commas are giving you an extra column that you don't actually have. Can you fix that, or are you stuck with them? — , Apr 05 '14 at 21:34
Just a tip for the future, Ruby has [parallel assignment](http://en.wikipedia.org/wiki/Assignment_(computer_science)#Parallel_assignment), which makes swapping variables easy one-liners, since you don't need a temporary variable, so you should use it more often. — , Apr 05 '14 at 21:52

score 5 · Answer 1 · 2014-04-05T21:48:55.967

5

Here's another way you can do this, by turning columns into rows by transposing them, then swapping the foo row with the bar row, then transposing again:

require 'csv'

csv = CSV.read("./foo.csv", { headers: true, return_headers: false })
csv = csv.to_a.transpose
csv[0],csv[1] = csv[1],csv[0]
csv = csv.transpose

CSV.open("./foo.csv", "wb") do |lines|
  csv.each { |line| lines << line }
end

edited Apr 05 '14 at 21:48

answered Apr 05 '14 at 21:39

This is a really good technique, I'm going to implement this idea in the next revision of my code. – spuder Apr 06 '14 at 01:53
This strategy has the benefit (compared to the other answers) of easily being able to re-order arbitrary columns in a CSV file with many columns. – Dennis Sep 09 '15 at 15:33

score 2 · Accepted Answer · edited Apr 05 '14 at 21:59

You can make it simple:

require 'csv'

result = []

CSV.read("./foo.csv").each do |line|
  result << [line[1], line[0], line[2]]
end

CSV.open("./bar.csv", "wb") do |csv|
  result.each{ |line| csv << line }
end

And there was some empty columns, due to the extra comma at the end of your CSV file. Should be:

foo,bar,foobar
1,2,3
4,5,6
7,8,9

Combining with Alex's solution:

require 'csv'

CSV.open("./bar.csv", "wb") do |csv|
  CSV.read("./foo.csv").each do |line|
    csv << [line[1], line[0], line[2]]
  end
end

score 1 · Answer 3 · answered Apr 05 '14 at 21:26

1

Try:

require 'csv'

CSV.open('./foo-reversed.csv','wb') do |csv|
  CSV.read('./foo.csv','r').each do |row|
    csv << row.reverse
  end
end

answered Apr 05 '14 at 21:26

Alex Peachey

4,606
22
18

How do you rearrange the order of columns in a csv file?

3 Answers3