Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Table Manipulation

by aartist (Pilgrim)
on Aug 25, 2012 at 14:20 UTC ( [id://989730]=perlquestion: print w/replies, xml ) Need Help??

aartist has asked for the wisdom of the Perl Monks concerning the following question:

Dears,
I have Table 1 and Table 2.
Table:1
A	B	C
1	0	2
0	1	3

Table:2
A	P	Q	R
1	1	0	1
1	0	1	0
0	0	0	1

Table:3
B	C	P	Q	R
0	2	1	0	1
0	2	0	1	0
1	3	0	0	1
Each Table has columns. I am looking to produce Table 3 from Table 1 and Table 2. See that Column A is listed in Table 1 as well as in Table 2.
Column A in Table 1, have "values" in Table 2 ( columns P,Q,R). I like to merge these values and produce table 3, replacing entries in column A. An entry in column A can have multiple values as seen in Table 2 for A=1. In Table 3, columns B and C are almost same as that in Table 1, but they take care of 'multiple values for column A'.

Please also note that column A can have multiple entries for same number (Here A=1) in Table 1 as well.
example:

Table:1
A	B	C
1	1	1
1	0	2
0	1	3

Table:2
A	P	Q	R
1	1	0	1
1	0	1	0
0	0	0	1

Table:3
B	C	P	Q	R
1	1	1	0	1
1	1	0	1	0
0	2	1	0	1
0	2	0	1	0
1	3	0	0	1

How should I go about this? Any Data/Table/Matrix modules can help?
Thanks.

Replies are listed 'Best First'.
Re: Table Manipulation
by BillKSmith (Monsignor) on Aug 26, 2012 at 02:57 UTC

    Array of arrays works well. Build a line of table_3 (using array slices) when a match is found.

    use strict; use warnings; use Data::Dumper; my @table_1 = ( # A B C [ 1, 1, 1 ], [ 1, 0, 2 ], [ 0, 1, 3 ], ); my @table_2 = ( # A P Q R [ 1, 1, 0, 1 ], [ 1, 0, 1, 0 ], [ 0, 0, 0, 1,], ); my @table_3; foreach my $row_of_1 (@table_1) { foreach my $row_of_2 (@table_2) { if ( $row_of_1->[0] == $row_of_2->[0] ) { push @table_3, [@{$row_of_1}[1..2],@{$row_of_2}[1..3]]; } } } print Dumper \@table_3;
    Bill
      Helpful solution. Thanks.
      I have 2 more requirement. I need the column names in the answer. 2. I have more table_2 types tables for specified columns of in table_1, that I need to merge. For example I have table_2 that also start with 'B' or 'C' in addition to what is already there (table_2 that starts with 'A').

        Try to do the second new requirement yourself. Make a second program exactly the same as the first except for the subscripts. We can look into generalizing later.

        Do you just need column names in the output or do you need a data structure which includes the names?

        Bill
Re: Table Manipulation
by frozenwithjoy (Priest) on Aug 26, 2012 at 02:15 UTC
    Are you familiar with the statistical programming language R? You can use Statistics::R to take advantage of the merge function of R to merge two tables based on a specific column(s). If you don't already know R, this might be a time-sink.

    Alternatively, you could make two hashes of arrays (one for each of tables 1 & 2) and iterate through them to output Table 3. Here is the hash structure for the second example you gave:

    my %table_1 = ( 0 => [ [ 1, 1 ], [ 0, 2 ] ], 1 => [ [ 1, 3 ] ], ); my %table_2 = ( 0 => [ [ 0, 0, 1 ] ], 1 => [ [ 1, 0, 1 ], [ 0, 1, 0 ] ], );

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://989730]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others admiring the Monastery: (3)
As of 2024-03-29 06:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found