05-24-2017
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
10 More Discussions You Might Find Interesting
1. Programming
Hi,
I have a question about using C to write excel file.
There is a way to write into a sheet into a file excel, like this:
FILE * File;
pMyFile = fopen("test.xls", "w+");
fprintf(File, "\n\n"); /* go to the row 2 of the column A*/
fprintf(File, "%s", "Hi pippo!"); /* write... (7 Replies)
Discussion started by: manceryder
7 Replies
2. HP-UX
Dear Members,
How do I create an eqvivalant of Excel sheet in Hp-UX.
Is there any application like the Microsoft Excel on HP-UX.
How do I invoke it.
Regards,
PrasadKVS (5 Replies)
Discussion started by: KVSPRASAD
5 Replies
3. Shell Programming and Scripting
Kindly help,
Suppose i am having a file that has got the following contents. Is there any way (eg. sed command) to remove the (*) (*any number) pattern.
se.bas
tulf.h
(1)
tuna.c
(1)
tunsim.c
(1)
tus.cpp
(1)
vp.c
(1)
vp.h
(1)
vpi.
(1) (1 Reply)
Discussion started by: frozensmilz
1 Replies
4. Shell Programming and Scripting
I have this script written in csh which I would like to convert to something similar using python script so that I can do more calculations rather than using csh.
set narg = $#argv
while ($iarg < $narg)
MATH iarg = $iarg + 1
set arg = $argv
set opt = `echo $arg | awk... (2 Replies)
Discussion started by: kristinu
2 Replies
5. OS X (Apple)
Hello,
This is my first post so not sure what response I will get
I do have a very small knowledge of UNIX used last over 10 years ago
I have recently move over to an Apple IMAC
I need to copy all my jpg files into excel
Could someone please let me know if this is possible and... (2 Replies)
Discussion started by: Nacnud
2 Replies
6. Shell Programming and Scripting
Hi ,
I am writing a simple excel file and want to create the file under say 'D:\Documents and Settings'. The problem with my code is it is writing in the same directory instead of the specified. Here is a sample code
use Spreadsheet::WriteExcel;
my $workbook =... (1 Reply)
Discussion started by: daptal
1 Replies
7. Shell Programming and Scripting
Hi list,
This is probably something really simple, but I am not particularly familiar with Python so I thought I would ask as I know that python has an excel module.
I have an excel document with multiple tabs of data and graphs.
One of the tabs is just data which I require to have dumped to... (8 Replies)
Discussion started by: landossa
8 Replies
8. Shell Programming and Scripting
Hi,
Anyone can help I am just converting the excel file to csv using python, I can get the conversion output but the date not display exactly.
test.xlsx date format
167 1588 risks/SCS JP CAMPANA & CIE.pdf SCS JP CAMPANA & CIE 2 1 1 0 2015-03-16 16:56:25
167 1146 risks/AirBNB... (1 Reply)
Discussion started by: fspalero
1 Replies
9. Shell Programming and Scripting
Hello
I have a big excel file for Ticket Data Analysis. The idea is to make meaningful insight from Resolution Field. Now as people write whatever they feel like while resolving the ticket it makes quite a task.
1. They may or may not tag it with something like below within the resolution... (1 Reply)
Discussion started by: radioactive9
1 Replies
10. Shell Programming and Scripting
Hi All,
I am new to shell scripting.
I have made a script that can convert an excel file from cvs file.
This excel file contains hundreds of records and i would like the reader to be able to filter.
Is it possible to create an excel file with filters? or that functionality has not been... (3 Replies)
Discussion started by: Marvin159875321
3 Replies