How to gather all values of specific rows in each sheets?

user_14724837 0
Viewed 177

I am new to python, and I have no idea how to gather all needed value to multiple list. For example, I need all the row values of

df["notes"] == 0.5

Excel

so I write some codes to print specific values(e.g. 1.322921452, 0.838709677,....,0.749, 1.1827...,0.9921, 0.92473...) But the current code print all the sheets of 0.5 values, I need to separate them sheet by sheet into whatever dataframe or list. This is my current code

df_ls = [pd.DataFrame() for i in range(6)]

a = []


for i in range(5):
    for j in (range(40)):
        df = pd.read_excel("norm.xlsx",sheet_name = i) 
        for note in range(0, len(df["notes"])):
            if df["notes"][note] == 0.5:
                value = df.iloc[note, j+1]

I expect the list would be like

sheet1 = [1.322921452, 0.838709677...]
sheet2 = [0.534344,0.9484...]
.
.
.
sheet5 = [0.243,0.3331155]

Thanks a lot!!!

pandas excel python
1 Answers

You can use this pattern: read_csv with sheet_name=None load all sheets into a dictionary. Filter each dataframe with your condition then concat them into a new dataframe:

dfs = pd.read_excel('norm.xlsx', sheet_name=None)
data = {}
for sheet, df in dfs.items():
    data[sheet] = df.loc[df['notes'] == 0.5]
df = pd.concat(data)

Output:

# Sample
>>> df
          notes   0
Sheet1 0    0.5   1
       1    0.5   2
       2    0.5   3
Sheet2 0    0.5  11
       1    0.5  12
       2    0.5  13

How to use the dataframe:

>>> df.loc['Sheet1']
   notes  0
0    0.5  1
1    0.5  2
2    0.5  3
user_152399510
Related