rewritng a column cell values in a dataframe based on when the value change without using if statment

user_19007427 0
Viewed 36

i have a column with faulty values as it is supposed to count cycles, but the device where the data from resets the count after 50 so i was left with exmalple [1,1,1,1,2,2,2,,3,3,3,3,...,50,50,50,1,1,1,2,2,2,2,3,3,3,...,50,50,.....,50] My solution is and i cannt even make it work:(for simplicity i made the data resets from 10 cycles

 data = {'Cyc-Count':[1,1,2,2,2,3,4,5,6,7,7,7,8,9,10,1,1,1,2,3,3,3,3,
               4,4,5,6,6,6,7,8,8,8,8,9,10]}
df = pd.DataFrame(data)
x=0
count=0
old_value=df.at[x,'Cyc-Count']
for x in range(x,len(df)-1):
    if df.at[x,'Cyc-Count']==df.at[x+1,'Cyc-Count']:
        old_value=df.at[x+1,'Cyc-Count']
        df.at[x+1,'Cyc-Count']=count
       
    else:
        old_value=df.at[x+1,'Cyc-Count']
        count+=1
        df.at[x+1,'Cyc-Count']=count

i need to fix this but preferably without even using if statments the desired output for the upper example should be

data = {'Cyc-Count':[1,1,2,2,2,3,4,5,6,7,7,7,8,9,10,11,11,11,12,13,13,13,13,
               14,14,15,16,16,16,17,18,18,18,18,19,20]}

hint" my method has a big issue is that the last indexed value will be hard to change since when comparing it with its index+1 > it dosnt even exist

pandas dataframe if-statement python-3.x python
2 Answers

IIUC, you want to continue the count when the counter decreases.

You can use vectorial code:

s = df['Cyc-Count'].shift()
df['Cyc-Count2'] = (df['Cyc-Count']
                   + s.where(s.gt(df['Cyc-Count']))
                      .fillna(0, downcast='infer')
                      .cumsum()
                   )

Or, to modify the column in place:

s = df['Cyc-Count'].shift()
df['Cyc-Count'] +=  (s.where(s.gt(df['Cyc-Count']))
                      .fillna(0, downcast='infer').cumsum()
                     )

output:

    Cyc-Count  Cyc-Count2
0           1           1
1           1           1
2           1           1
3           1           1
4           2           2
5           2           2
6           2           2
7           3           3
8           3           3
9           3           3
10          3           3
11          4           4
12          5           5
13          5           5
14          5           5
15          1           6
16          1           6
17          1           6
18          2           7
19          2           7
20          2           7
21          2           7
22          3           8
23          3           8
24          3           8
25          4           9
26          5          10
27          5          10
28          1          11
29          2          12
30          2          12
31          3          13
32          4          14
33          5          15
34          5          15

used input:

l = [1,1,1,1,2,2,2,3,3,3,3,4,5,5,5,1,1,1,2,2,2,2,3,3,3,4,5,5,1,2,2,3,4,5,5]
df = pd.DataFrame({'Cyc-Count': l})
user_163434640

You can use df.loc to access a group of rows and columns by label(s) or a boolean array.

syntax: df.loc[df['column name'] condition, 'column name or the new one'] = 'value if condition is met'

for example:

import pandas as pd

numbers = {'set_of_numbers': [1,2,3,4,5,6,7,8,9,10,0,0]}
df = pd.DataFrame(numbers,columns=['set_of_numbers'])
print (df)

df.loc[df['set_of_numbers'] == 0, 'set_of_numbers'] = 999
df.loc[df['set_of_numbers'] == 5, 'set_of_numbers'] = 555

print (df)

before: ‘set_of_numbers’: [1,2,3,4,5,6,7,8,9,10,0,0]

After: ‘set_of_numbers’: [1,2,3,4,555,6,7,8,9,10,999,999]

user_99913950
Related