Comparing two rows in Pandas Data Frame

Question

I'm relatively new to working with Python. That said I've gotten to the point where I am working with an API (thanks https://www.alphavantage.co/documentation/). I'm trying to do some stock analysis and have constructed a simple data frame. I have arrived at a problem that I have not been able to "articulate" correctly for Google to help out. So here goes...

I want to compare the MACD value of a stock to "yesterday's" value so given this example table

date   MACD
2/3/19 2.546
2/4/19 2.456
2/5/19 2.645

I would want to have another column that returned a simple boolean value

date   MACD  MACD_Greater MACD_Smaller
2/3/19 2.546 NaN          NaN
2/4/19 2.456 False        True
2/5/19 2.645 True         False

I already have my data frame set up and have added the necessary columns. I have even been able to perform the first bit which is the np.where... lines. However, now I need to compare the row prior.

#Build MACD API URL for specific stock ticker
api_url = 'https://www.alphavantage.co/query?function=MACD&symbol=' + symbol + '&interval=' + interval + '&series_type=' + seriestype + '&datatype='
api_url = api_url + datatype + '&fastperiod=' + fastperiod + '&slowperiod=' + slowperiod + '&signalperiod=' + signalperiod + '&apikey=' + key + '.' + datatype

response = req.get(api_url)
if response.status_code == 200:
  #Read data into data frame
  stockdata = pd.read_csv (api_url)
  df = pd.DataFrame (stockdata)

  #Set index in data frame to the 'time' field
  df.set_index('time', inplace=True)

  #Sort df by date of stock price
  df = df.sort_values(by='time', ascending=True)

  #Filter data frame based on time period
  days_n = 45
  start_date = date.today() - timedelta(days=days_n)
  start_date = start_date.strftime('%Y-%m-%d')
  end_date = date.today()
  end_date = end_date.strftime('%Y-%m-%d')
  df_filtr = df.loc[start_date:end_date]

  #Add MACD Cross flag column to data frame
  df_filtr['MACD_Bull'] = np.where(df_filtr['MACD'] > df_filtr['MACD_Signal'],True, False)
  df_filtr['MACD_Bear'] = np.where(df_filtr['MACD'] < df_filtr['MACD_Signal'],True, False)

Resulting output:

              MACD  MACD_Hist  MACD_Signal  MACD_Bull  MACD_Bear
time                                                            
2019-11-22  2.5641     0.1718       2.3923       True      False
2019-11-25  2.6195     0.1817       2.4378       True      False
2019-11-26  2.6968     0.2072       2.4896       True      False
2019-11-27  2.7498     0.2082       2.5416       True      False
2019-11-29  2.6850     0.1147       2.5703       True      False
2019-12-02  2.4576    -0.0901       2.5477      False       True
2019-12-03  2.2323    -0.2523       2.4847      False       True
2019-12-04  2.0735    -0.3290       2.4024      False       True
2019-12-05  1.9317    -0.3765       2.3083      False       True
2019-12-06  1.9439    -0.2915       2.2354      False       True
2019-12-09  1.9001    -0.2682       2.1683      False       True
2019-12-10  1.8258    -0.2740       2.0998      False       True
2019-12-11  1.7923    -0.2460       2.0383      False       True
2019-12-12  1.8685    -0.1359       2.0044      False       True
2019-12-13  2.0097     0.0043       2.0054       True      False
2019-12-16  2.1773     0.1375       2.0398       True      False
2019-12-17  2.2167     0.1415       2.0752       True      False
2019-12-18  2.1969     0.0973       2.0995       True      False
2019-12-19  2.2632     0.1309       2.1323       True      False
2019-12-20  2.4249     0.2341       2.1908       True      False
2019-12-23  2.5240     0.2666       2.2574       True      False
2019-12-24  2.5705     0.2504       2.3200       True      False
2019-12-26  2.6805     0.2884       2.3921       True      False
2019-12-27  2.7593     0.2937       2.4656       True      False
2019-12-30  2.6803     0.1718       2.5085       True      False
2019-12-31  2.5966     0.0705       2.5261       True      False
2020-01-02  2.7344     0.1666       2.5678       True      False
2020-01-03  2.6517     0.0671       2.5846       True      False

Comparing two rows in Pandas Data Frame

Answers (1)

Related Questions