Unix/Linux Go Back    


Programming Post questions about C, C++, Java, SQL, and other programming languages here.

Creating filters with Python on excel

Programming


Tags
excel, filter data, python

Closed    
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 05-24-2017   -   Original Discussion by nans
nans's Unix or Linux Image
nans nans is offline
Registered User
 
Join Date: Mar 2013
Last Activity: 30 October 2017, 6:30 AM EDT
Posts: 74
Thanks: 39
Thanked 0 Times in 0 Posts
Creating filters with Python on excel

Hello,

I have an excel sheet with 11 tabs. I need to take data from the first tab and write the output to the second tab. The first tab looks like this, starting from Row 3

Quote:
FID IID LOCUS CHROM POS REF ALT ZG freq type subtype consequence percent type1 type2 type3 type4 type5 type6 type7 type8 Score type9 type10 type11 type12 type13

A_1 B1 ABC 7 1000 A G yes 150 snp ts Random 68 546 3532 253 4000 5 123 200 0E0
A_2 B2 APP 21 23 C T yes 36 snp ts Random 2 547 5435 353 6000 6 345 200 0E0
A_3 B3 APOE 19 200 A G yes 0 snp ts Random 99 234 6456 5235 26994 9 1 200 0E0
The filters that needs to be created are
1) keep anything greater than 'POS' 5 and less than 160 AND
2) From an external csv file, read the file with headers CHROM/POS/REF/ALT/SCORE and create a new column on the this sheet called SCORE to write only the ones with score 0 or 1 or 2 (out of 6) matching CHROM/POS/REF/ALT/
3) Keep the ones with 'percent' more than 5% for those with score 0

I have written several one liners in awk for these filters with a lot of intermediate files and also converting excel sheets to csv. Would Python be a better way ? Thank you
Sponsored Links
    #2  
Old Unix and Linux 05-24-2017   -   Original Discussion by nans
Corona688's Unix or Linux Image
Corona688 Corona688 is offline Forum Staff  
Mead Rotor
 
Join Date: Aug 2005
Last Activity: 8 December 2017, 4:34 PM EST
Location: Saskatchewan
Posts: 22,541
Thanks: 1,158
Thanked 4,285 Times in 3,954 Posts
There's an excellent Perl library for .xls spreadsheets (not xlsx afaik), SpreadSheet::ParseExcel. It can import, edit, and export. It's the usual thing used when UNIX scripts are forced to speak Microsoft. As a very rough cut to start with, perhaps:


Code:
#!/usr/bin/perl

use Spreadsheet::ParseExcel;

my $infile=shift || die("No input filename given");
my $outfile=shift || die("No output filename given");

my $e=new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filein);

my $sheet1=$eBook->{Worksheet}[0];
my $sheet2=$eBook->{Worksheet}[1];
my $row=0;

foreach my $row ($sheet1->{MinRow} .. $sheet1->{MaxRow}) {
        $sheet2->write_row(0, $row++, ["value1", "value2", "value3"]);
}

$eBook->SaveAs($outfile);

Sponsored Links
    #3  
Old Unix and Linux 05-25-2017   -   Original Discussion by nans
drl's Unix or Linux Image
drl drl is offline Forum Advisor  
Registered Voter
 
Join Date: Apr 2007
Last Activity: 11 December 2017, 9:29 AM EST
Location: Saint Paul, MN USA / BSD, CentOS, Debian, OS X, Solaris
Posts: 2,212
Thanks: 250
Thanked 417 Times in 358 Posts
Hi, nans.
Quote:
Originally Posted by nans
... I have written several one liners in awk for these filters with a lot of intermediate files and also converting excel sheets to csv. Would Python be a better way ?
My take on this:

As usual, it depends.

Are you knowledgeable in languages/scripts other than awk?

Do you have time to acquire such skill if not?

This is kind of an optimization question:

Guideline 0: does it absolutely need to be faster, more efficient, easier to use, etc?

Best wishes ... cheers, drl
Sponsored Links
Closed

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Convert excel to csv in python date not display exactly fspalero Shell Programming and Scripting 1 08-30-2016 07:24 AM
Converting specific Excel file tabs to CSV in Python landossa Shell Programming and Scripting 8 02-29-2012 04:31 AM
Help required in creating a shell script that filters the unwanted pattern frozensmilz Shell Programming and Scripting 1 12-03-2008 07:20 AM
Creating Excel Sheet in Hp-UX KVSPRASAD HP-UX 5 02-07-2007 11:16 PM
creating more than 2 excel sheets in C manceryder Programming 7 12-20-2005 06:10 PM



All times are GMT -4. The time now is 11:05 AM.