Starting from an imported df from excel like that:
| Lev. | Material | Text | QTY |
|---|---|---|---|
| .1 | X222 | Model3 | 1 |
| .2 | 4027721 | Gruoup1 | 1 |
| .3 | 4647273 | Gruoup1.1 | 4 |
| .3 | 573828 | Gruoup1.2 | 1 |
| .2 | 47883 | Gruoup2 | 1 |
| .3 | 573829 | Gruoup2.1 | 5 |
| .3 | 747458 | Gruoup2.2 | 4 |
I want to add a new column reporting a specific value, obtained after an evaluation of the column Lev. I tried to use a if command like that:
def categorise_row:
if df ["Lev"] == ".1" or ".2"
df ["Material"]
else df.iloc[-1]["Material"]
df ['ColF'] = df.apply (categorise_row)
The result has to be, if Lev = .1 or .2 the value present in column material instead, the result has to be equal to the result present in the new column upper row.
Expected result:
| Lev. | Material | Text | QTY | NewColumn |
|---|---|---|---|---|
| .1 | X222 | Model3 | 1 | X222 |
| .2 | 4027721 | Gruoup1 | 1 | 4027721 |
| .3 | 4647273 | Gruoup1.1 | 4 | 4027721 |
| .3 | 573828 | Gruoup1.2 | 1 | 4027721 |
| .2 | 47883 | Gruoup2 | 1 | 47883 |
| .3 | 573829 | Gruoup2.1 | 5 | 47883 |
| .3 | 747458 | Gruoup2.2 | 4 | 47883. |
CodePudding user response：
You can use where to hide values from other levels and use ffill to broadcast the last valid to bottom rows:
df['ColF'] = df['Material'].where(df['Lev.'].isin(['.1', '.2'])).ffill()
print(df)
# Output
Lev. Material Text QTY ColF
0 .1 X222 Model3 1 X222
1 .2 4027721 Gruoup1 1 4027721
2 .3 4647273 Gruoup1.1 4 4027721
3 .3 573828 Gruoup1.2 1 4027721
4 .2 47883 Gruoup2 1 47883
5 .3 573829 Gruoup2.1 5 47883
6 .3 747458 Gruoup2.2 4 47883