select columns in a dataframe via indexing notation and not column names

user_9106985 0
Viewed 217

I would like to create a df_D from the given input df using the entry 'Start' as my reference for both the row and column indexing.

I do not want to use the column names A,B, C etc.

Instead, I wish to use indexing as the sequence is always the first 3 columns from 'START' and the last 3 columns, ie something like n,n+1,n+2, n+5,n+6,n+7

Input:

df = pd.DataFrame({'A':['jfgh',23,'Ndfg',34,0,56],'B':['jfgh',23,'START',34,0,56], 'C':['cvb',7,'dsfgA',65,47,3],'D':['rrb',7,'gfd',3,0,7],'E':['dfg',7,'gfd',5,12,1],'F':['dfg',7,'sdfA',5,0,4],'G':['dfg',7,'sdA',5,8,9],'H':['dfg',7,'gfA',5,0,8],'I':['dfg',7,'sdfA',5,7,23]})

Output:

      A      B      C    D    E     F    G    H     I
0  jfgh   jfgh    cvb  rrb  dfg   dfg  dfg  dfg   dfg
1    23     23      7    7    7     7    7    7     7
2  Ndfg  START  dsfgA  gfd  gfd  sdfA  sdA  gfA  sdfA
3    34     34     65    3    5     5    5    5     5
4     0      0     47    0   12     0    8    0     7
5    56     56      3    7    1     4    9    8    23

Desired Output: df_D Created manually

    B   C  D  G  H   I
0   0  47  0  8  0   7
1  56   3  7  9  8  23

Attempt 1:

for index in range(len(df)):
    if str(df.loc[index,'C']).startswith('START'):
        df_D = df.iloc[index+1:len(df), [1,2,3,6,7,8]]
        break

Resulting Output:

Empty DataFrame
Columns: [B, C, D, G, H, I]
Index: []

Where have I gone wrong?

pandas python
3 Answers

We can use np.where to find the start index. Then use iloc with np._r to create our slices:

start_col = np.where(df.eq("START"))[1][0]
cols = df.shape[1]
col_select = np.r_[start_col: start_col+3, cols-3: cols]

df.iloc[-2:, col_select]

    B   C  D  G  H   I
4   0  47  0  8  0   7
5  56   3  7  9  8  23
user_90812670

Here's how I did it:

df = pd.DataFrame({'A':['jfgh',23,'Ndfg',34,0,56],'B':['jfgh',23,'START',34,0,56], 'C':['cvb',7,'dsfgA',65,47,3],'D':['rrb',7,'gfd',3,0,7],'E':['dfg',7,'gfd',5,12,1],'F':['dfg',7,'sdfA',5,0,4],'G':['dfg',7,'sdA',5,8,9],'H':['dfg',7,'gfA',5,0,8],'I':['dfg',7,'sdfA',5,7,23]})

for i, column in enumerate(df.columns):
    if df[column].str.startswith('START').sum() > 0:
        idx = i
        break
    
df.iloc[[-2, -1], [idx, idx+1, idx+2, -3, -2, -1]]

First find the column with the string that begins with "START" (by the way, if you are sure that the string not just starts with "START", but is "START", this can be simplified by directly using == instead of .str.startswith).

Then, as we know the index of first column, simly use iloc.

The problem with you solution, as I see, is that for whatever reason you hardcode the 'C' column and search "START" in it. You'd have to also iterate over columns, not just rows.

user_51256670

Step 0 : Initialize an empty list k

Step 1 : Iterate through all the columns using for loop on df.shape[1] "START"

Step 2 : Iterate through all rows in each column i did this using df. shape[0]

Step 3: search for "START"

Step 4: once found store the column no. and row number in variables.

Step 5: use those variables to index all rows and columns you want. so you use row+1 since you want everything below START and col ,col+ 1 so on.

step 6: add the dataframe to the list k

final step : you can see k[0] gives first instance of start, k[1] giving second instance of start, you can use this as more general code. If you don't want all instances use break as soon as first dataframe is found.

k=[]
for i in range(df.shape[0]):
    for y in range(df.shape[0]):
        if df.iloc[y,i] == 'START':
            col = i
            row = y
            k.append(df.iloc[row+1:,[col,col+1,col+2,-3,-2,-1]])    
print("first START")
print(k[0])
print("\n Second START")
print(k[1])
user_113884210
Related