根据另一个CSV中的值提取一个CSV中的值 [英] Pulling a value from one CSV based on a value in another

问题描述

我正在尝试找出基于master.csv中的值从名为lookup.csv的CSV文件中提取值的最佳方法，然后将新文件另存为output.csv.

I am trying to figure out the best way to pull a value from a CSV file called lookup.csv based on a value in master.csv, and then save the new file as output.csv.

在下面的示例中，主文件包含三列数据，最后一列为City.我想用lookup.csv文件中的City Code替换City名称.

In the example below, the master file has three columns of data with the last column being City. I'd like to replace the City name with the City Code from the lookup.csv file.

我没有可以从中查找的数据库，因此必须使用CSV文件.我正在尝试将FasterCSV与Ruby 1.8.7结合使用.

I don't have a DB that I can lookup from so I am having to use the CSV file. I am trying to use FasterCSV with Ruby 1.8.7.

示例文件结构:

master.csv:

master.csv:

First Name | Last Name | City
Joey       | Jello     | Atlanta
Home       | Boy       | Dallas

lookup.csv:

lookup.csv:

City    | City ID
Atlanta | 12345
Dallas  | 12346
Houston | 12347

output.csv:

output.csv:

First Name | Last Name | City
Joey       | Jello     | 12345
Home       | Boy       | 12346

推荐答案

我使用的是1.9，其中FasterCSV在标准库中作为CSV可用.首先，我将从lookup.csv创建一个查找哈希:

I'm using 1.9, where FasterCSV is available as CSV in the standard lib. First I'd create a lookup hash out of lookup.csv:

cities = Hash[CSV.read('lookup.csv', :col_sep => ' | ').to_a[1..-1]]

如果文件很大，则可能要使用CSV.foreach对其进行迭代，并逐行构建哈希:

If the file is very big, you might want to iterate over it with CSV.foreach and build the hash row by row:

cities = {}
CSV.foreach('lookup.csv', :col_sep => ' | ', :headers => true, :return_headers => false) do |line|
  cities[line['City']] = line['City ID']  
end  

然后遍历master.csv，在哈希中查找城市并将其写入output.csv:

Then iterate over master.csv, do a lookup of the city in the hash and write that to output.csv:

CSV.open('output.csv', "w", :headers => ['First Name', 'Last Name', 'City ID'], :write_headers => true) do |output|
  CSV.foreach('master.csv', :col_sep => ' | ', :headers => true, :return_headers => false) do |line|
    output << [line['First Name'], line['Last Name'], cities[line['City']]]
  end  
end

这篇关于根据另一个CSV中的值提取一个CSV中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！