I am trying to read every kth column of an Excel file using pandas.read_excel. From the documentation usecols option with a callable seems useful:
If callable, then evaluate each column name against it and parse the column if the callable returns True.
Is there a way for the callable to take in the column number rather than column name? Something like:
pd.read_excel('file.xls', usecols=lambda col_number: not col_number % k)
This is the best way that I know of - read the first lines, figure out the number of columns then create an array with every kth column integer index. (And the callable only receives the column name.)
import numpy as np
dfe = pd.read_excel(r'D:\jchfiles\excel\jch\house\Rats.xlsx', nrows=1)
k = 3
print(dfe.shape)
(1, 9)
nb_sel_cols = dfe.shape[1]//k
print(nb_sel_cols)
3
sel_cols = np.arange(nb_sel_cols)*k
print(sel_cols)
[0 3 6]
df_rats = pd.read_excel(r'D:\jchfiles\excel\jch\house\Rats.xlsx', usecols=sel_cols)
df_rats.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 16 entries, 0 to 15
Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Unnamed: 0 3 non-null datetime64[ns]
1 Inside 3 non-null float64
2 Where 3 non-null object
dtypes: datetime64[ns](1), float64(1), object(1)
memory usage: 512.0+ bytes