0

This is my problem, which may be so simple, but I am a novice. I have a DataFrame, and, in a determinate column, I want to replace a value in a row if this is greater than the previous row.

The steps I am following are:

  1. df1 = pd.read_csv("M10_10.txt") (Reading my CSV)
  2. In that CSV, there is a column named m_Crit200. That is the column which values I want to replace if they match my condition.
  3. I am using this, but it does not working:
for i in range(1,len(df6)):
    if df6.m_Crit200[i] < df6.m_Crit200[i+1]:
        df6.m_Crit200[i]=df6.m_Crit200[i+1]
    else:
        df6.m_Crit200[i]=df6.m_Crit200[i]

This is the "if" code I am using, but does not working. Sorry about my explanation, as I said, I am novice and this is my first time here.

Thanks in advance

This is an example of what I want: I have this

    Value
0   10
1   7
2   6
3   12
4   3
5   2
6   1

I want this

    Value
0   10
1   7
2   6
3   6
4   3
5   2
6   1

I want to replace the value by the value at the previous row, is that is greater.

This is my second error. When I use the methods in the answers below, I get this

0     6.540991
1     6.540991
2     6.971319
3     6.971319
4     6.971319
5     6.971319
6     7.057385
7     6.540991
8     6.540991
9     6.282794
10    6.282794
11    6.540991
12    6.540991
13    7.315582
14    8.176239
15    8.090173
16    7.831976
17    5.594269
18    3.959021
19    3.528693
20    3.528693
21    3.528693
22    3.528693
23    3.528693
24    3.700824
25    3.614758
26    3.614758
27    3.356561
28    3.356561
29    2.926233
30    2.754101
31    2.754101
32    2.754101
33    2.840167
34    2.323773
35    2.323773
36    2.495904
37    2.495904
38    2.323773
39    1.463116
40    1.032788
41    1.032788
Name: m_Crit200, dtype: float64

And here I let you my original

0     6.540991
1     6.971319
2     6.971319
3     6.971319
4     6.971319
5     7.057385
6     7.057385
7     6.540991
8     6.627057
9     6.282794
10    6.713122
11    6.540991
12    7.315582
13    8.348371
14    8.176239
15    8.090173
16    7.831976
17    5.594269
18    3.959021
19    3.528693
20    3.528693
21    3.786890
22    3.528693
23    3.700824
24    3.700824
25    3.614758
26    3.872955
27    3.356561
28    3.356561
29    2.926233
30    2.754101
31    2.754101
32    2.840167
33    3.098364
34    2.323773
35    2.754101
36    2.495904
37    2.495904
38    2.323773
39    1.463116
40    1.032788
41    1.032788
Name: m_Crit200, dtype: float64
OjoTuerto
  • 1
  • 3
  • Hi and welcome to SO, good to have you here. Could you please provide a [mcve] with inputs (as code) and expected outputs? – pythonic833 Oct 18 '21 at 14:48
  • What do you mean by your code is "not working" - are you getting errors or not matching the output you want? By way of an example, if the value in row 2 is greater than row 1, what do you want to replace row 2's value with? – not_speshal Oct 18 '21 at 14:53
  • Hi @pythonic833, I just edited the OP whit my desired values. I hope that be usefull – OjoTuerto Oct 18 '21 at 15:03
  • Hi @not_speshal, I just edited the OP. That is what I would like! Thx both of you – OjoTuerto Oct 18 '21 at 15:04

2 Answers2

1

Since you want a non-increasing Series, you can use an expanding window and choose the minimum value for each window:

df6["m_Crit200"] = df6["m_Crit200"].expanding().apply(min)

>>> df6
0     6.540991
1     6.540991
2     6.540991
3     6.540991
4     6.540991
5     6.540991
6     6.540991
7     6.540991
8     6.540991
9     6.282794
10    6.282794
11    6.282794
12    6.282794
13    6.282794
14    6.282794
15    6.282794
16    6.282794
17    5.594269
18    3.959021
19    3.528693
20    3.528693
21    3.528693
22    3.528693
23    3.528693
24    3.528693
25    3.528693
26    3.528693
27    3.356561
28    3.356561
29    2.926233
30    2.754101
31    2.754101
32    2.754101
33    2.754101
34    2.323773
35    2.323773
36    2.323773
37    2.323773
38    2.323773
39    1.463116
40    1.032788
41    1.032788
Name: m_Crit200, dtype: float64
not_speshal
  • 22,093
  • 2
  • 15
  • 30
  • Thanks for the answer. I really do not know how `diff` and `ffill´ work. I´ll research it – OjoTuerto Oct 18 '21 at 15:13
  • @OjoTuerto - I've linked the appropriate docs in the answer. It is usually a bad idea to iterate over dataframes (see [here](https://stackoverflow.com/a/55557758/9857631)), especially if there are vectorized solutions (like my answer) readily available. These will be *much* faster with larger dataframes. – not_speshal Oct 18 '21 at 15:16
  • Thank you so much. Yes, as you comment, I think is better work this with arrays instead with dataframe. I will read the documentation you´ve linked. BTW, may I ask you another question? I think this is easier. How can I remove the rows wich contains a concret value (Zero in my case)? Thx again – OjoTuerto Oct 18 '21 at 15:44
  • You can do: `df6 = df6[df6["m_Crit200"]!=0]`. It's easier to keep what you want instead of dropping what you don't want – not_speshal Oct 18 '21 at 15:56
  • Thank you so much, yes, it is much easier! – OjoTuerto Oct 18 '21 at 16:01
  • Sure, but I have a new problem whit my OP question. The problem comes when I have several rows with the same values, they do not change whit the value which is smaller. Is just the value in the position m+1 which changes if is greater than the value in the position m, but if the value in the position (m+2) is equal to the value in the position (m+1), this does not change. – OjoTuerto Oct 18 '21 at 16:16
  • Please update your example. Not sure what you mean. – not_speshal Oct 18 '21 at 16:18
  • Hi @not_speshal, I have just edit my OP. Here you can see my real problem. Sorry if I can´t explain myself propperly – OjoTuerto Oct 18 '21 at 16:28
  • So you want the column to be non-increasing? Not just comparing with the value above? – not_speshal Oct 18 '21 at 16:41
  • Yes @not_speshal, hat is pretty much what I want. And keep the value which is before in the next (or next ones) till the value of the row be smaller. Sorry if I did not explain my self correctly – OjoTuerto Oct 18 '21 at 16:45
  • Yes, that is what I want. Thank yoy so myuch for your time! – OjoTuerto Oct 18 '21 at 16:54
1

You can use shift for this:

df.Value.where(df.Value.shift(periods=1).fillna(np.inf)>df.Value, df.Value.shift(periods=1))

#output
0    10
1     7
2     6
3     6
4     3
5     2
6     1
j__carlson
  • 1,346
  • 3
  • 12
  • 20
  • Thx for the answer. Ive tried that, but a problem comes when I have several rows with the same value. That does not change them... – OjoTuerto Oct 18 '21 at 15:51
  • You can try replacing `>` with `>=` in `df.Value.shift(periods=1).fillna(np.inf)>df.Value`. – j__carlson Oct 18 '21 at 15:56
  • Hi @j_carlson, I think I get the same by replacing that. I don´t know how deal whith that... – OjoTuerto Oct 18 '21 at 16:02
  • If you add the section of data where you get the problem, to the question I can likely fix it. – j__carlson Oct 18 '21 at 16:05
  • Thx, I will try to make it. My problem is, in words, that. The problem comes when I have several rows with the same values, they do not change whit the value which is smaller. Is just the value in the position m+1 which changes if is greater than the value in the position m, but if the value in the position (m+2) is equal to the value in the position (m+1), this does not change. But I will try to add it to the OP – OjoTuerto Oct 18 '21 at 16:16