根据另一个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屋!