There is a question how to replace the values ​​in the array with the necessary ones? I want if in the array the value is 9999, then replace it with 1, and the rest to replace it with 0. From the Excel file , another file is created.

import pandas as pd file_name = r'C:\Users\fazliakhmetovRV\Documents\Python Scripts\primer1.xlsx' out = r'C:\Users\fazliakhmetovRV\Documents\Python Scripts\result.xlsx' cols = ['metka','x','y','z','gk'] df = pd.read_excel(file_name, sheetname='er', skiprows=4, header=None, parse_cols='C:XFD') dfs = [] for i in range(df.columns.size//5): lbl_col = 5*i # filter each 5-column block x = df.ix[(df[lbl_col] != 0) & (df[lbl_col] != 9999), lbl_col:lbl_col+4] # set custom column names x.columns = cols # set 'metka' column as index (will be used for alignment by `pd.concat()`) x.index = x.metka dfs.append(x) # merge filtered DFs horizontally (aligned by indexes) result = pd.concat(dfs, axis=1) # replace all `metka` columns with the "most complete" list of labels result.ix[:, ::5] = pd.concat([result.index.to_series()] * (len(df.columns)//5), axis=1) # replace all NaN's with `9999` result.fillna(9999, inplace=True) # save resulting DF to Excel result.to_excel(out, index=False)

From result.xlsx, the data is written into arrays:

 n_met=len(result) n_well=result.columns.size/5 x=result.x.values.astype(int) y=result.y.values.astype(int) z=result.z.values.astype(int) gk=result.gk.values.astype(float)

Next, I want to replace the values ​​from 9999 with 1, and the remaining 0. As I understand it, you cannot do this):

 for i in range(1,n_met): for j in range(1,n_well): if x[i][j]==9999: x[i][j]=1 else: x[i][j]=0

Please tell me how to realize this moment?

partial filling of the wedge table

  • Can you lay out an example of the expected result? Do you want to replace only in column x ? PS it's not entirely clear why you created all these variables: x , y , z , gk ... - MaxU
  • @MaxU Get individual arrays from result by x, y, z, gk. And for them you need to make separate arrays with 1 instead of 9999, and 0 instead of other values. Attached to the description of the photo with the algorithm. - Ramil Fazliahmetov
  • those. Do you want to change the values ​​to 1 and 0 in all columns except metka ? - MaxU

1 answer 1

You can make a replacement without any cycles:

 r = result.copy() r.loc[:, r.columns[~r.columns.str.contains('^metka')]] = \ np.where(r[r.columns[~r.columns.str.contains('^metka')]] == 9999, 1, 0) In [53]: r Out[53]: metka xyz gk metka.1 x.1 y.1 z.1 gk.1 ... metka.17 x.17 y.17 z.17 gk.17 metka.18 x.18 y.18 z.18 gk.18 0 1 0 0 0 0 1 0 0 0 0 ... 1 0 0 0 0 1 0 0 0 0 1 2 0 0 0 0 2 0 0 0 0 ... 2 0 0 0 0 2 0 0 0 0 2 3 1 1 1 1 3 0 0 0 0 ... 3 0 0 0 0 3 0 0 0 0 3 4 0 0 0 0 4 0 0 0 0 ... 4 0 0 0 0 4 0 0 0 0 4 5 0 0 0 0 5 0 0 0 0 ... 5 0 0 0 0 5 0 0 0 0 5 6 0 0 0 0 6 1 1 1 1 ... 6 0 0 0 0 6 0 0 0 0 6 7 0 0 0 0 7 0 0 0 0 ... 7 0 0 0 0 7 0 0 0 0 7 8 0 0 0 0 8 0 0 0 0 ... 8 0 0 0 0 8 0 0 0 0 8 9 0 0 0 0 9 0 0 0 0 ... 9 0 0 0 0 9 0 0 0 0 9 10 0 0 0 0 10 0 0 0 0 ... 10 0 0 0 0 10 0 0 0 0 10 11 0 0 0 0 11 0 0 0 0 ... 11 0 0 0 0 11 0 0 0 0 11 12 0 0 0 0 12 0 0 0 0 ... 12 0 0 0 0 12 0 0 0 0 12 13 0 0 0 0 13 0 0 0 0 ... 13 0 0 0 0 13 0 0 0 0 13 14 1 1 1 1 14 0 0 0 0 ... 14 0 0 0 0 14 0 0 0 0 14 15 1 1 1 1 15 0 0 0 0 ... 15 0 0 0 0 15 0 0 0 0 15 16 1 1 1 1 16 0 0 0 0 ... 16 1 1 1 1 16 1 1 1 1 16 17 1 1 1 1 17 1 1 1 1 ... 17 1 1 1 1 17 1 1 1 1 [17 rows x 95 columns]

PS about your sketch, which you have laid out - this is all relatively easily done by Pandas, but for this you need to have test data and imagine what the result should look like (at least a few lines).