Split Excel worksheet using Python

Question

I have excel files (hundreds of them) that look like this (sensor output):

Column1          Column2    Column3

Serial Number:
10004
Ref. Temp:
25C
Ref. Pressure:
1KPa
Time              Temp.     Pres.
1                 21        1
2                 22        1.1
3                 23        1.2
.                 .         .
.                 .         .
.                 .         .

I want to split this into two parts, the information section (top part) and data section (the rest), something like this:

Information section

Column1          Column2    Column3

Serial Number:
10004
Ref. Temp:
25C
Ref. Pressure:
1KPa

Data section:

Column1      Column2    Column3
Time          Temp.     Pres.
1              21       1
2              22       1.1
3              23       1.2
.              .            .
.              .            .
.              .            .

if it converts to data frame I don't want the first row and column become header and index of the data frame. I am using python 2.7 and numpy.

Vityata · Accepted Answer · 2017-11-13 16:50:57Z

1

Make two copies of the worksheet.
In copy A, start a loop, going on the first column looking for the word Time. Once it finds it, let it delete anything before it.
Remember the row in a variable.
In copy B, delete anything after the remembered row to row number 2^20.

answered Nov 13, 2017 at 16:50

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob Over a year ago

I have hundreds of files with several sheets like this. I want to do it automatically. I wonder how I can do it on the fly without doing manually or creating additional files.

Vityata Over a year ago

@Bob - there is nothing manual in the solution that I have offered - these are the automatization steps.

Collectives™ on Stack Overflow

Split Excel worksheet using Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related