CODEWITHSUNDEEP
pythonpandas
Kimchi
Kimchi

Reputation: 97

Read excel sheet starting from specific col

I have an excel workbook with multiple sheets and I am trying to import/read the data starting from any empty col before col C.

the row data look like this

A C
One Two Three

and I am trying to get the data

C
Two Three

I can't use usecols as the position of this empty col changes in each sheet I have in the workbook. So it will optimal to start always from whatever empty col located before col C

I have tried this but didn't work out for me.

df = df[df.columns[list(df.columns)[-1]]

I would appreciate any suggestions or hints. Many thanks in advance!

Upvotes: 2

Views: 97

Answers (3)

jezrael
jezrael

Reputation: 863541

If need solution for return all values if not exist empty string use:

m1 = df.columns.str.startswith('Unnamed')
m2 = (df.columns == 'C').cumsum() == 0

m = m1 & m2

print (df.iloc[:, m[::-1].argmax():])

EDIT:

Pandas solution for get column before C:

df = pd.DataFrame({'B':[8, 1,2],
                   'A':[8, 1,2],
                   'C':[1,2,0],
                   'D':[1,2,0]})


df = df.iloc[:, df.columns.get_loc('C')-1:]
print (df)
   A  C  D
0  8  1  1
1  1  2  2
2  2  0  0

If column C is first, need max for select all columns:

df = pd.DataFrame({'C':[8, 1,2],
                   'D':[1,2,0]})
    
df = df.iloc[:, max(0, df.columns.get_loc('C')-1):]
print (df)
   C  D
0  8  1
1  1  2
2  2  0

Solution working well in first DataFrame:

df = df.iloc[:, max(0, df.columns.get_loc('C')-1):]
print (df)
   A  C  D
0  8  1  1
1  1  2  2
2  2  0  0

Upvotes: 1

Kimchi
Kimchi

Reputation: 97

Thanks for garagnoth to help with this issue, I have ended up doing the following:

df = df[df[list(df.columns).index('C') - 1:]]

Upvotes: 0

U13-Forward
U13-Forward

Reputation: 71610

Assuming your column name is an empty string(''), try slicing:

df.loc[:, '':]

Upvotes: 0

Related Questions