how to split columns in database, and transpose the columns and rows

[domanicap]

This is really 2 separate questions. I'm trying to write a PERL script that can do this:

1) take the following spreadsheet
ID 3423_3499 2324-5839
1 A_A B_B
2 A_B A_A
3 A_B A_B

and convert it to this:
ID 3423 3499 2324 5839
1 A A B B
2 A B A A
3 A B A B

(basically it would split all the columns except the first ID column, based on a delimiter such as _ or even a space)


2) transpose the data set to flip the axes


Thanks!

 

[stevexff]

What do you have so far? Post it, and we'll have a look at it...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[travs69]

assuming that the data is in an array
for my $line (@data) {
my @tmp = split /\_/, $line;
for my $line2 (@tmp) {
print "$line2\t";
}
print "\n";
}



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[KevinADC]

or just replace spaces and underscores with however many spaces you need between the columns.

s/[ _]/ /g;

or whatever....

------------------------------------------
- Kevin, perl coder unexceptional!

 

[domanicap]

excellent, thanks! how about a simple line to transpose the data?

thanks

 

[rharsh]

Since you want to transpose the rows and the columns, you might as well read the info into an Array of Arrays. Here, see if this gets you going in the right direction.

my @AoA;
my $maxCols = 0;

while (<DATA>) {
	chomp;
	my @temp = split /[\s_-]+/, $_;
	$maxCols = $maxCols > scalar @temp ? $maxCols : scalar @temp;
	push @AoA, \@temp;
}

# Print with original orientation
foreach my $row (@AoA) {
	print join("\t", @{$row}), "\n";
}
print '-' x 20, "\n";

# Print Transposed
for (my $col=0; $col <= $maxCols-1; $col++) {
	my @temp = map {$_->[$col] || ""} @AoA;
	print join("\t", @temp), "\n";
}

__DATA__
ID 3423_3499 2324-5839
1  A_A       B_B
2  A_B       A_A
3  A_B       A_B