# Merging partial, incomplete data files

**URL:** <https://www.ruby-forum.com/t/merging-partial-incomplete-data-files/177683>\
**Category:** Ruby\
**Created:** [October 21, 2009, 11:20pm UTC](https://www.ruby-forum.com/t/merging-partial-incomplete-data-files/177683 "2009-10-21T23:20:21Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bterkuile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e68b1a/32.png) [@bterkuile](https://www.ruby-forum.com/u/bterkuile)\
**Post date:** [October 21, 2009, 11:20pm UTC](https://www.ruby-forum.com/t/merging-partial-incomplete-data-files/177683/1 "2009-10-21T23:20:21Z")

</div>

Hello all,

I write scripts for fun and am still a noob.

I’d like to use ruby to solve a problem for me but find it difficult  
this time so I thought I’d ask for help.

It’s very basic in fact.

I have 2 csv files I’d like to merge such as :

File1 File2

* * *

ref;qty ref;price  
A;10 A;100  
B;20 D;150  
C;30 C;200  
E;5 B;75

outputs to File3 =\>

## File3

ref;qty;price;total  
A;10;100;1000  
B;20;75;1500  
C;30;200;6000  
D;“missing\_data”;150;“missing\_data”  
E;5;“missing\_data”;“missing\_data”

Here is the code I have so far:

###########################################################################  
def process\_file(file)  
processed = []  
opened = File.open(file, “r”).readlines

opened.each {|line| processed \<\< line.strip.split(";")}  
return processed  
end

arr1 = process\_file(“file1”)  
arr2 = process\_file(“file2”)

p arr1, arr2  
###########################################################################

==\> [[“ref”, “qty”], [“A”, “10”], [“B”, “20”], [“C”, “30”], [“E”, “5”]]  
==\> [[“ref”, “price”], [“A”, “100”], [“D”, “150”], [“C”, “200”], [“B”,  
“75”]]

* * *

So I have 2 arrays which is great but I’m not sure on how to go about  
merging all this since data is sometimes missing from file1, some other  
times from file2. Of course those 2 files are more complex (and  
bigger!), and I’d like to setup a mechanism that would allow me to  
select which columns to filter and merge, but that’s the basic idea  
anyway.

Thanks for your input and direction.

---

<div class="post-metadata">

**Author:** ![bterkuile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e68b1a/32.png) [@bterkuile](https://www.ruby-forum.com/u/bterkuile)\
**Post date:** [October 21, 2009, 11:55pm UTC](https://www.ruby-forum.com/t/merging-partial-incomplete-data-files/177683/2 "2009-10-21T23:55:48Z")

</div>

On 10/21/2009 11:20 PM, Benjamin T. wrote:

> File3  
> ###########################################################################
> 
> times from file2. Of course those 2 files are more complex (and  
> bigger!), and I’d like to setup a mechanism that would allow me to  
> select which columns to filter and merge, but that’s the basic idea  
> anyway.
> 
> Thanks for your input and direction.

I’d say, you should look into classes Hash and Set. If you have both  
files in a Hash each you can do

keys = f1.keys.to\_set | f2.keys

Then you can do

keys.each do |key|

# fetch from f1 and f2

end

Kind regards

robert

---

<div class="post-metadata">

**Author:** ![bterkuile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e68b1a/32.png) [@bterkuile](https://www.ruby-forum.com/u/bterkuile)\
**Post date:** [October 22, 2009, 12:09am UTC](https://www.ruby-forum.com/t/merging-partial-incomplete-data-files/177683/3 "2009-10-22T00:09:51Z")

</div>

Hi Benjamin,

This can be done in ruby, and my inline responses will hopefully point  
you in  
the right direction. However, depending on the size of the data or the  
amount  
of available memory, doing this in ruby may not be the best choice.  
If I were doing this, I would probably import both files into a database  
like sqlite3, using a script, of course, and generate the output with  
sql.  
The front end script would accept parameters for the input files, the  
columns  
to output (or a list of columns to suppress, if any) and produce the  
desired  
results.

Rather than using generating two arrays, I would read one file  
into a hash, keyed by the common key, ‘ref’, then read the second file  
and match up its record, if any, in in other file, and output the  
combined  
set. You’d want to keep track of which lines from file1 have a match so  
you  
can output all the non-matching values. However, I would strongly  
suggest  
that rather than doing the work yourself, generate code to script sqlite  
or other database system.

HTH

-Gyepi

---

<div class="post-metadata">

**Author:** ![bterkuile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e68b1a/32.png) [@bterkuile](https://www.ruby-forum.com/u/bterkuile)\
**Post date:** [October 21, 2009, 11:57pm UTC](https://www.ruby-forum.com/t/merging-partial-incomplete-data-files/177683/4 "2009-10-21T23:57:34Z")

</div>

On Oct 21, 2009, at 4:20 PM, Benjamin T. wrote:

> Hello all,

Hello.

> I write scripts for fun and am still a noob.

I don’t think I wrote code as good as you when I was new. 😉

> outputs to File3 =\>
> 
> ## File3
> 
> ref;qty;price;total  
> A;10;100;1000  
> B;20;75;1500  
> C;30;200;6000  
> D;“missing\_data”;150;“missing\_data”  
> E;5;“missing\_data”;“missing\_data”

> So I have 2 arrays which is great but I’m not sure on how to go about  
> merging all this since data is sometimes missing from file1, some  
> other  
> times from file2.

It’s a tricky question.

There are a few ways I can’t of to do things.

First, you could load one giant Hash (indexed by ref) of Arrays or  
Hashes with the data as you read it, then just dump the Hash to disk.  
This is the easiest, but it requires you to have all data in memory at  
once. Are the files small enough that you can do that?

James Edward G. II

---

<div class="post-metadata">

**Author:** ![bterkuile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e68b1a/32.png) [@bterkuile](https://www.ruby-forum.com/u/bterkuile)\
**Post date:** [October 23, 2009, 12:42am UTC](https://www.ruby-forum.com/t/merging-partial-incomplete-data-files/177683/5 "2009-10-23T00:42:37Z")

</div>

James Edward G. II wrote:

> First, you could load one giant Hash (indexed by ref) of Arrays or  
> Hashes with the data as you read it, then just dump the Hash to disk.  
> This is the easiest, but it requires you to have all data in memory at  
> once. Are the files small enough that you can do that?

Yes, I don’t think that would be a problem. This is what I have so far :

#############################################################################  
def process\_file(file)  
header = []  
processed = []  
opened = File.open(file, “r”).readlines # store in  
array – index=line

opened.each {|line| processed \<\< line.strip.split(";")} # remove  
separator and “\n”  
header = processed.shift  
return header, processed # the array  
is clean  
end

def build\_hash(header, arr, col\_pos)  
hash = {}  
arr.size.times {|counter|  
hash["#{arr[counter][0]}\_#{header[col\_pos]}"]=arr[counter][col\_pos] }  
return hash  
end

hdr1, arr1 = process\_file(“file1”)  
hdr2, arr2 = process\_file(“file2”)

p hdr1, arr1  
puts  
p hdr2, arr2

hash1 = build\_hash(hdr1, arr1, 0)  
hash2 = build\_hash(hdr1, arr1, 1)

hash3 = build\_hash(hdr2, arr2, 0)  
hash4 = build\_hash(hdr2, arr2, 1)  
puts

p hash1, hash2  
puts  
p hash3, hash4

puts  
hash = {}  
merge1 = hash1.merge(hash2)  
merge2 = hash3.merge(hash4)  
hash = merge1.merge(merge2)

p hdr1 + hdr2  
p hash  
#########################################################################  
Outputs to :  
[“ref”, “qty”]  
[[“A”, “10”], [“B”, “20”], [“C”, “30”], [“E”, “5”]]

[“ref”, “price”]  
[[“A”, “100”], [“D”, “150”], [“C”, “200”], [“B”, “75”]]

{“A\_ref”=\>“A”, “C\_ref”=\>“C”, “B\_ref”=\>“B”, “E\_ref”=\>“E”}  
{“A\_qty”=\>“10”, “C\_qty”=\>“30”, “B\_qty”=\>“20”, “E\_qty”=\>“5”}

{“A\_ref”=\>“A”, “C\_ref”=\>“C”, “B\_ref”=\>“B”, “D\_ref”=\>“D”}  
{“B\_price”=\>“75”, “A\_price”=\>“100”, “D\_price”=\>“150”, “C\_price”=\>“200”}

[“ref”, “qty”, “ref”, “price”]  
{“B\_price”=\>“75”, “A\_ref”=\>“A”, “A\_price”=\>“100”, “C\_ref”=\>“C”,  
“D\_ref”=\>“D”, “B\_ref”=\>“B”, “A\_qty”=\>“10”, “D\_price”=\>“150”,  
“C\_price”=\>“200”, “C\_qty”=\>“30”, “E\_ref”=\>“E”, “B\_qty”=\>“20”,  
“E\_qty”=\>“5”}  
############################################################################  
Robert K. wrote:

> I’d say, you should look into classes Hash and Set. If you have both  
> files in a Hash each you can do…

Thanks, I will look into “Set” which I don’t know about.

Gyepi SAM wrote:

> However, I would strongly suggest that rather than doing the work yourself, \> generate code to script sqlite or other database system.

Yes, I have thought about that. I would guess it would be the most  
efficient solution in terms of ressources and speed. However I don’t  
mind taking the time and making an effort to understand data  
manipulation.

> Rather than using generating two arrays, I would read one file  
> into a hash, keyed by the common key, ‘ref’, then read the second file  
> and match up its record, if any, in in other file, and output the  
> combined

Is what I’ve done similar to what you had in mind ?

Thanks all for the input.