Average of columns with values of other column with same name
I have a lot of input files that have the following form:
I need to read each column, get the row values from the column next to it that have same name in the first column and compute the average of their values and output in a new file. They are usually triplicates of the same name, i need to get all three of its values and compute the average of these . The output should look like this in a new file:
I print only one of the triplicates and next to it the average of the three values that each of the triplicates had.
Is it possible to be done in awk? I am trying with perl.
With PERL:
I thought of converting the columns into rows, read each line and create a hash having as KEY each Sample name and adding the value of each triplicate every time i find the same name to its VALUE. At the end I will divide the value/3 and output the key->value in the output file.
To keep the records organised and in order I will read the input file again and print key->value from the hash for each line.
Is there an easier way to do this? Because with hashes I will lose the ordering of the columns in the file and it will get a bit messy I'm afraid.
Hi All,
I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Hi guys,
Couldn't find the solution of this problem. Please Help!
I have a file-
Input_File
TC200232 92 30
TC215306 2 74
TC210135 42 14
I want an output file in which if column2>column3, the values are swapped and an additional column with value Rev_Com is... (4 Replies)
Hi i have data with two columns like below.
I want to find average of column values like if the value in column 2 is between 0-250000 the average of column 1 is some xx and average of column2 is ww then if value is 250001-5000000 average of column 1 is yy and average of column 2 is zz.
And my... (5 Replies)
Hi,
I have a space delimited text file that looks like the following:
Aa 100 200
Bb 300 100
Cc X 500
Dd 600 X
Basically, I want to take the average of columns 2 and 3 and print it in column 4. However if there is an X in either column 2 or 3, I want to print the non-X value. Therefore... (11 Replies)
Hi All,
I have a pipe seperated file. I need to add the values in second and third columns with group by on first column.
MYFILE_28012012_1115|47|173.90
MYFILE_28012012_1115|4|0.00
MYFILE_28012012_1115|6|22.20
MYFILE_28012012_1116|47|173.90
MYFILE_28012012_1116|4|0.00... (3 Replies)
Dear Experts,
Kindly help me please,
I have a big file where there is duplicate values in col 11 till col 23, every 2 rows appers a new numbers, but in each row there is different coordinates x and y in col 57 till col 74.
Please i will like to get a single value and average of the x and y... (8 Replies)
cat sample.csv
ID,Name,no
1,AAA,1
2,BBB,1
3,AAA,1
4,BBB,1
cut -d',' -f2 sample.csv | sort | uniq
this gives only the 2nd column values
Name
AAA
BBB
How to I get all the columns of CSV along with this? (1 Reply)
How could i take an input file and split the numeric values from the alpha values (123 vs abc) to distinc columns, and if the source is blank to keep it blank (null) in both of the new columns:
So if the source file had a column like:
Value:
|1 |
|2.3|
| |
|No|
I would... (7 Replies)
I have the need to match the first two columns and when they match, calculate the percent of average for the third columns. The following awk script does not give me the expected results.
awk 'NR==FNR {T=$3; next} $1,$2 in T {P=T/$3*100; printf "%s %s %.0f\n", $1, $2, (P>=0)?P:-P}' diff.file... (1 Reply)
I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv
abc.csv-
UTF-8,,,,,,,,,,,,,,,,,,,,,,,,,
... (6 Replies)
Discussion started by: Tahir_M
6 Replies
LEARN ABOUT DEBIAN
spreadsheet::writeexcel::chart::area
Spreadsheet::WriteExcel::Chart::Area(3pm) User Contributed Perl Documentation Spreadsheet::WriteExcel::Chart::Area(3pm)NAME
Area - A writer class for Excel Area charts.
SYNOPSIS
To create a simple Excel file with a Area chart using Spreadsheet::WriteExcel:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new( 'chart.xls' );
my $worksheet = $workbook->add_worksheet();
my $chart = $workbook->add_chart( type => 'area' );
# Configure the chart.
$chart->add_series(
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
);
# Add the worksheet data the chart refers to.
my $data = [
[ 'Category', 2, 3, 4, 5, 6, 7 ],
[ 'Value', 1, 4, 5, 2, 1, 5 ],
];
$worksheet->write( 'A1', $data );
__END__
DESCRIPTION
This module implements Area charts for Spreadsheet::WriteExcel. The chart object is created via the Workbook "add_chart()" method:
my $chart = $workbook->add_chart( type => 'area' );
Once the object is created it can be configured via the following methods that are common to all chart classes:
$chart->add_series();
$chart->set_x_axis();
$chart->set_y_axis();
$chart->set_title();
These methods are explained in detail in Spreadsheet::WriteExcel::Chart. Class specific methods or settings, if any, are explained below.
Area Chart Methods
There aren't currently any area chart specific methods. See the TODO section of Spreadsheet::WriteExcel::Chart.
EXAMPLE
Here is a complete example that demonstrates most of the available features when creating a chart.
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new( 'chart_area.xls' );
my $worksheet = $workbook->add_worksheet();
my $bold = $workbook->add_format( bold => 1 );
# Add the worksheet data that the charts will refer to.
my $headings = [ 'Number', 'Sample 1', 'Sample 2' ];
my $data = [
[ 2, 3, 4, 5, 6, 7 ],
[ 1, 4, 5, 2, 1, 5 ],
[ 3, 6, 7, 5, 4, 3 ],
];
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write( 'A2', $data );
# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'area', embedded => 1 );
# Configure the first series. (Sample 1)
$chart->add_series(
name => 'Sample 1',
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$B$2:$B$7',
);
# Configure the second series. (Sample 2)
$chart->add_series(
name => 'Sample 2',
categories => '=Sheet1!$A$2:$A$7',
values => '=Sheet1!$C$2:$C$7',
);
# Add a chart title and some axis labels.
$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Test number' );
$chart->set_y_axis( name => 'Sample length (cm)' );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D2', $chart, 25, 10 );
__END__
AUTHOR
John McNamara jmcnamara@cpan.org
COPYRIGHT
Copyright MM-MMX, John McNamara.
All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.
perl v5.10.1 2010-02-02 Spreadsheet::WriteExcel::Chart::Area(3pm)