How do I label data based on the values of the previous row?

user_13465034 0
Viewed 123

I want to label the data "1" if the current value is higher than that of the previous row and "0" otherwise.

Lets say I have this DataFrame:

df = pd.DataFrame({'date': [1,2,3,4,5], 'price': [50.125, 45.25, 65.857, 100.956, 77.4152]})

and I want the output as if the DataFrame is constructed like this:

df = pd.DataFrame({'date': [1,2,3,4,5], 'price': [50.125, 45.25, 65.857, 100.956, 77.4152], 'label':[0, 0, 1, 1, 0]})

*I don't know how to post a DataFrame

These code is my attempt:

df['label'] = 0
i = 0
for price in df['price']:
    i = i+1
    if price in i > price: #---> right now I am stuck here. i=It says argument of type 'int' is not iterable
        df.append['label', 1]
    elif price in i <= price:
        df.append['label', 0]

I think there are also other logical mistakes in my codes. What am I doing wrong?

pandas python
3 Answers

Create boolean mask by Series.ge (>=) with Series.shift and convert to integers for True/False to 1/0 mapping by Series.view:

df['label'] = df['price'].ge(df['price'].shift()).view('i1')

Or by Series.astype:

df['label'] = df['price'].ge(df['price'].shift()).astype(int)
user_29010020

IIUC np.where with a boolean shift to see the change in the row price and if it's greater than the row above.

df['label'] = np.where(df['price'].ge(df['price'].shift()),1,0)

print(df)

   date     price  label
0     1   50.1250      0
1     2   45.2500      0
2     3   65.8570      1
3     4  100.9560      1
4     5   77.4152      0

Explanation:

print(df['price'].ge(df['price'].shift()))

returns a boolean of True and False statements that we can use in our where clause 

0    False
1    False
2     True
3     True
4    False
user_93751020

To explain what is happening in your code:

  • df['label'] should be initiated to an empty list, not "0". If you want to set the first value of the list to 0, you can do df['label'] = [0].
  • i is just the index value (0, 1, 2, 3...) and not the value of the price at a specific index (50.125, 45.25, 65.857...) , so it is not what you want to compare.
  • price in is used to check if the value of the price variable exists in a list that follows. The in statement isn't followed with a list, so you will get an error. You want to instead get the price value at a specific index and compare if it is greater or less than the value at the previous index.
  • The append method uses () and not [].

If you want to continue along your method of using a loop, the following can work:

df['label'] = []
for i in range(len(df['price'])):
    if df['price'][i] > df['price'][i - 1]:
        df['label'].append(1)
    else:
        df['label'].append(0)

What this does is loop through the range of the length of the price list. It then compares the values of the price at position i and position i - 1.

You can also further simplify the if/else statement using a ternary operator to:

df['label'].append(1 if df['price'][i] > df['price'][i - 1] else 0)

Working fiddle: https://repl.it/repls/HoarseImpressiveScientists

user_46608970
Related