How get row index of matching row in column 0

[Sri]

Hi,

I have a csv file as below:

inter value,70				
time interval[min],20				
dose_trigger value,-23				
					
					
warning_linit1,36				
warning limit2,15				
cooling time [hrs],2				
cooling number[#],30				
					
					
					
Trail_number,initila,final,middle,max,min
Trial_20157832,1,23,14,28,1
Standard,0,5,2.6,5,0
Trial_20037924,	2,13,17,20,0
Trial_20637924,6,18,19,17,3

I want to get the row index of "Trail_number" in column zero (here row8).

1. Extract the data from row zero to row7 (rows befor Trail_number row),and write to 'intial.csv'
2. Extract the data from row Trail_number(here row8) to end in to a 'final.csv'


I use the below code, but I am getting error in getteing row index.

import pandas as pd
import numpy as np
rw_data=pd.read_csv(r'D:\Mekala_Backupdata\PythonCodes\raw_WEHP02.csv')

idx1=~(rw_data.where(rw_data[0]=='Trail_number')).isnull().all()

error:

KeyError: 0

[perfringo]

I want to get the row index of "Trail_number" in column zero (here row8).

1. Extract the data from row zero to row7 (rows befor Trail_number row),and write to 'intial.csv'
2. Extract the data from row Trail_number(here row8) to end in to a 'final.csv'

I observe that row8 is cooling time [hrs],2 and 'Trail_number' is on row 13

Why use pandas, numpy if task is to write rows from one file into two separate files?

[Sri]

There are 5 empty rows before "Trail_number"
if remove empty rows, the "Trail_number" will become row8.

I forgot to mention, I also want to delete empty rows.
I import pandas because I am reading CSV file using pd.read_csv.

[perfringo]

One way is to take advantage of iterator protocol. This is based on list, but file objects are also iterable and therefore might give some ideas.

s = ['inter value,70',
     'time interval[min],20',               
     'dose_trigger value,-23',              
     'warning_linit1,36',               
     'warning limit2,15',               
     'cooling time [hrs],2',                
     'cooling number[#],30',                
     'Trail_number,initila,final,middle,max,min',
     'Trial_20157832,1,23,14,28,1',
     'Standard,0,5,2.6,5,0',
     'Trial_20037924, 2,13,17,20,0',
     'Trial_20637924,6,18,19,17,3']

first = list()
second = list()

rows = iter(s)                          # create iterator

for row in rows:
    if row.startswith('Trail_number'):  # consume iterator until sentinel and break
        second.append(row)
        break
    else:
        first.append(row)

for row in rows:                         # consume remaining iterator
    second.append(row)

# first
['inter value,70',
 'time interval[min],20',
 'dose_trigger value,-23',
 'warning_linit1,36',
 'warning limit2,15',
 'cooling time [hrs],2',
 'cooling number[#],30']

# second
['Trail_number,initila,final,middle,max,min',
 'Trial_20157832,1,23,14,28,1',
 'Standard,0,5,2.6,5,0',
 'Trial_20037924, 2,13,17,20,0',
 'Trial_20637924,6,18,19,17,3']

[Sri]

My original data is in csv and I read using

import pandas as pd
rw_data=pd.read_csv('raw_WEHP02.csv')

and now catch data before "Trail_number" in once csv and from "Trail_number" to end of the file write in another csv file.

as per your above suggestion I use the below code still does not work.

import pandas as pd
rw_data=pd.read_csv('aw_WEHP02.csv')

#=========================================

first = list()
second = list()
 
rows = iter(rw_data)                          # create iterator
 
for row in rows:
    if row.startswith('Trail_number'):  # consume iterator until sentinel and break
        second.append(row)
        break
    else:
        first.append(row)
 
for row in rows:                         # consume remaining iterator
    second.append(row)

[perfringo]

No need for pandas. Iterate over file object directly and instead of append write into appropriate file.

[Sri]

No data catch in to first or second.

[perfringo]

As I explained, there is no need for pandas or any csv module. It's plain and simple reading from file and writing to file.

My example was with lists, but this principle works with files as well. However, implementing principle on files requires modification of code (no append but write).

with open('dummy_data.csv', 'r', encoding='UTF-8') as outf, \
     open('out_1.csv', 'w') as out_1, \
     open('out_2.csv', 'w') as out_2:
    
    for row in outf:
        if row.startswith('Trail_number'):
            out_2.write(row)
            break
        else:
            out_1.write(row)

    for row in outf:           # loop can be replace by: out_2.writelines(outf)
        out_2.write(row)

dummy_data.csv contained data provided and two new files contain required data:

Output:
$ cat out_1.csv
inter value,70              
time interval[min],20               
dose_trigger value,-23              
warning_linit1,36               
warning limit2,15               
cooling time [hrs],2                
cooling number[#],30
$ cat out_2.csv
Trail_number,initila,final,middle,max,min
Trial_20157832,1,23,14,28,1
Standard,0,5,2.6,5,0
Trial_20037924, 2,13,17,20,0
Trial_20637924,6,18,19,17,3