Read random rows and columns from every excel sheet pandas and set one cell as index_col

user_17192986 0
Viewed 41

I have an excel workbook with multiple sheets that look like this: enter image description here

I want to grab cell B14 and the data from columns C:N rows 19-27. My current code looks like this:

data = pd.read_excel(f, sheet_name=None, index_col=None, usecols="C:N", skiprows=18, nrows=8)
identifier = pd.read_excel(f, sheet_name=None, index_col=None, header=None, usecols="B", skiprows=13)

but I want to either combine this code to read B14 and the data at the same time, or read both separately and concatonate that data so I can set the Identifier as the index_col for the data frame.

pandas excel python
1 Answers

With the following file.xlsx in my current working directory:

enter image description here

Here is one way to do it:

import pandas as pd

df = (
    pd.read_excel("file.xlsx", skiprows=13)
    .dropna(axis=0, how="all")
    .dropna(axis=1, how="all")
    .dropna(axis=0, how="any")
    .pipe(lambda df_: df_.set_index(df_.columns[0]))
    .pipe(
        lambda df_: df_.rename(
            index={idx: i for i, idx in enumerate(df_.index)},
            columns={f"Unnamed: {i+2}": i for i in range(df_.shape[1])},
        )
    )
)

print(df)
# Output
             0     1     2     3    4     5     6     7     8
Sample 1
0          1.0   1.0   1.0   1.0  1.0   2.0   5.0   6.0   3.0
1          4.0   4.0   5.0   6.0  2.0   6.0   8.0   2.0   6.0
2          7.0   7.0   9.0  11.0  3.0  10.0  11.0  -2.0   9.0
3         10.0  10.0  13.0  16.0  4.0  14.0  14.0  -6.0  12.0
4         13.0  13.0  17.0  21.0  5.0  18.0  17.0 -10.0  15.0
user_112460560
Related