Returning rows from a list of indexes in Python Pandas

Question

Context

I have a CSV containing two types of rows, an observation record and on the row below, an observation value which is related to the observation record above. The record line contains a four-letter code which denotes the type of observation. My goal is to create a new CSV containing only those observation records which match a particular list of codes, along with the associated observation values from the row below.

Example from file

OBSERV\LTRC,CL1,0,10.00;
OBVAL\14,,0,V;
OBSERV\LTRC,CL1,10,20.00;
OBVAL\14,,0,V;
OBSERV\LTRC,CL1,20,30.00;
OBVAL\14,,0,V;
OBSERV\LTRC,CL1,30,40.00;
OBVAL\14,,0.5,V;

Code so far

import pandas as pd
data = pd.read_csv(r"CSVFILEPATH")
df = pd.DataFrame(data)
df.set_index
newdf = df.loc[df[0].str.contains('LLRT|LLTX|LRRT|LTRC|LV10|LV3|LEDR|LTRV|LES2|LES1', regex = True)]
# this returns all observation record rows I care about but I still need the associated observation values.

keep_ind = [] #This list will contain all indexes to keep 

observ_ind = ndf.index.values.tolist()#The list of observation record indexes to keep
keep_ind.append(observ_ind)#Added these to the keep_ind list

The Question

How do I take this list of indexes (keep_ind), append a new list which is the same list with 1 added to each item (to get all of the observation value rows beneath the records) and create a new dataframe which contains all of the rows at each of these indexes in this combined list?

So far, I've tried:

keep_ind.append(observ_ind + 1 for i in observ_ind)

But this gives the error:

generator object <genexpr> at 0x0000028057C33648>]

Answer 1

I want to adress two issues. One is how you get your CSV and the other is what probably doesn't work in your code.

how to get the associated values

What you try to do is a nice task for the shift() method

# you probably don't need the semicolons at the end of the line, right?
# if you want to get rid of them, you can do:
df= pd.read_csv(
    r"CSVFILEPATH",   # your file
    engine='python',  # use the python engine instead of C to use a regex as separator
    sep=r'[,;]',      # use ; as an alternative separator
    usecols=range(3), # exclude the last column (after the ;)
    names=range(3))   # assign names, if you like you can also assign a list of more verbose column names here (this just uses numbers)

# create a dataframe that is a version of the original
# which is just one row shifted to the top
df_shifted= df.shift(-1)

# concatenate it with the original data frame and assign unique column names
df_concat=  pd.concat([df, df_shifted], axis='columns')
df_concat.columns= range(6)

# apply your filter
newdf= df.loc[df_concat[0].str.contains('LLRT|LLTX|LRRT|LTRC|LV10|LV3|LEDR|LTRV|LES2|LES1', regex = True)]

This outputs:

In [44]: newdf
Out[44]: 
             0    1     2         3    4    5
0  OBSERV\LTRC  CL1   0.0  OBVAL\14  NaN  0.0
2  OBSERV\LTRC  CL1  10.0  OBVAL\14  NaN  0.0
4  OBSERV\LTRC  CL1  20.0  OBVAL\14  NaN  0.0
6  OBSERV\LTRC  CL1  30.0  OBVAL\14  NaN  0.5

From the following test data you provided:

import io
import pandas as pd

raw=\
r"""OBSERV\LTRC,CL1,0,10.00;
OBVAL\14,,0,V;
OBSERV\LTRC,CL1,10,20.00;
OBVAL\14,,0,V;
OBSERV\LTRC,CL1,20,30.00;
OBVAL\14,,0,V;
OBSERV\LTRC,CL1,30,40.00;
OBVAL\14,,0.5,V;"""

df= pd.read_csv(io.StringIO(raw), engine='python', sep=r'[,;]', usecols=range(3), names=range(3))

Code, that probably doesn't do, what you intend to do

# the following line references a method, but doesn't call it:
df.set_index
# if you execute this line, it outputs:
df.set_index
Out[18]: 
<bound method DataFrame.set_index of              0    1     2
0  OBSERV\LTRC  CL1   0.0
1     OBVAL\14  NaN   0.0
...
# look at the <bound method part, this is the __repr__ string
# of the object (bound methods are objects themselfes)
# if you execute this line inbetween a script, it has no effect
# at all (just maybe slows down execution a very tiny bit)
# because you don't do anything with the returned object

You wrote, you get the message generator object <genexpr> at 0x0000028057C33648>]. This is not an error method. Like the bound method message above, this is also the __repr__ string of an obect. In this cas a generator object. If you call append on a list, it treats the argument passed in append as one object. I guess you rather wanted to add the indexes increased by one to an existing list. That can be done with the following code:

# create a copy of the list to avoid funny results
new_indices=list(observ_ind)
# add the elements of returned by the generator object
# to the list (rather than the generator object itself
new_indices.extend(i + 1 for i in observ_ind)