Re: Best way to read in an XbyX table into a Hash{Key}{Key2}[value] structure

(c/p from excel sheet)

Given that, you may want to consider Spreadsheet::ParseExcel to read the data in directly from your Excel spreadsheet.

Edit: Thanks to the OP for helping me understand the requirements. I now believe this will be much more in line with what you're after:

    my (undef, @col) = split /\t/, <>; # Column names
    my %prob_map;
    while (my ($from, @values) = split /\t/, <>) {
        @{$prob_map{$from}}{@col} = @values;
    }
    say "AAC to AAA = " . $prob_map{AAC}{AAA};
    say "AAA to AAC = " . $prob_map{AAA}{AAC};
__END__
AAC to AAA = 0.011
AAA to AAC = 0.01506
[download]

Previous line-based (i.e., row major) suggestion is below.

Otherwise, parsing the plain text you've provided is fairly straightforward as well:

    my @col   = split /\t/, <>; # Column headings
    my @lines = map { my %l; @l{@col} = split /\t/; \%l } <>;
[download]

However, whether that's actually an improvement on your code or not is debatable. :-)

Comment on Re: Best way to read in an XbyX table into a Hash{Key}{Key2}[value] structure Select or Download Code

Replies are listed 'Best First'.
Re^2: Best way to read in an XbyX table into a Hash{Key}{Key2}[value] structure by ZWcarp (Beadle) on Aug 07, 2013 at 19:37 UTC
Thank you for your help! So its not usually from excel sheets, I just listed that in case there was excel markup leftover ..like \r returns or something. Question though....I don't understand how I then access the information following your method. For example, if I wanted the probability of a AAC to AAA (.0011) how do I get to that value? You've created an array where each cell has the ref to a hash....which in turn holds the key value pairs of each row with respect to that column... correct? How do I access the info for individual cases?	[reply]
Re^3: Best way to read in an XbyX table into a Hash{Key}{Key2}[value] structure by rjt (Curate) on Aug 07, 2013 at 20:01 UTC
Thanks for the follow-up. I believe I now understand your requirements. Since my original solution was almost certainly not what you were after, I added a better one to my original node, which will make it possible to do the from/to lookups you need. Mea culpa; it's been a while since I looked at a probability table. :-)	[reply]
Re^2: Best way to read in an XbyX table into a Hash{Key}{Key2}[value] structure by Cristoforo (Curate) on Aug 07, 2013 at 20:17 UTC
`my (undef, @col) = split /\s+/, <DATA>; # Column names` When splitting on `\s+` instead of `\t`, the first header value, 'Amino Acid Switch Probabiities', will be wrongly split into the `@col` array. You need to split on tabs to prevent this from happening.	[reply] [d/l] [select]
Re^3: Best way to read in an XbyX table into a Hash{Key}{Key2}[value] structure by rjt (Curate) on Aug 07, 2013 at 22:09 UTC
Quite right. My local test copy of the sample data was based on the original OP (which had the tabs squashed to spaces), and I forgot to update my `split` pattern when posting. Corrected now, thanks.	[reply] [d/l]


go ahead... be a heretic
	PerlMonks