pythondataframepython-datetime

Iteration column with Day's name


I Would like to Compare the Nasdaq's Monday's daily performance with the PREVIOUS Friday's performance, if they are the same record event in a counter. I create a column that specifies if Nasdaq went Down (RIBASSO) or up (RIALZO), then i think to extract only Monday and Friday rows. The desired output is a counter that record every time this event occur.

import yfinance as yf
import pandas as pd
import numpy as np

database = yf.download('NQ=F')
df = pd.DataFrame(database)

# Day's name column
df["Day's name"] = df.index.to_series().dt.day_name()
df.drop(columns= ['Adj Close', 'Volume'])

# Daily performance column 
conditions = [ df['Close'] - df['Open'] > 0,
             df['Close'] - df['Open'] < 0,
             df['Close'] - df['Open'] == 0]
categories = ['RIALZO', 'RIBASS0', 'FLAT']
df['Daily Performance'] = np.select(conditions, categories, default='Unknown')


# Monday/Friday Musk
monday_perf = df["Day's name"] == 'Monday' 
friday_perf = df["Day's name"] == 'Friday'
df_mask = df[monday_perf | friday_perf]


Solution

  • Use shift along with cumsum to achieve the desired results.

    import yfinance as yf
    import pandas as pd
    import numpy as np
    
    database = yf.download('NQ=F')
    df = pd.DataFrame(database)
    
    # Day's name column
    df["Day's name"] = df.index.to_series().dt.day_name()
    df.drop(columns= ['Adj Close', 'Volume'])
    
    # Daily performance column 
    conditions = [ df['Close'] - df['Open'] > 0,
                 df['Close'] - df['Open'] < 0,
                 df['Close'] - df['Open'] == 0]
    categories = ['RIALZO', 'RIBASS0', 'FLAT']
    df['Daily Performance'] = np.select(conditions, categories, default='Unknown')
    
    # shift daily performance by 1 to get previous daily performance
    df['Previous Daily Performance'] = df['Daily Performance'].shift(1)
    # assign 1/0 to cases where current matches previous
    df['Monday Equals Previous Friday'] = df[['Daily Performance', 'Previous Daily Performance']] \
        .apply(lambda x: 1 if x[0] == x[1] else 0, axis=1)
    
    # Keep only Monday rows since previous Friday value is available in a separate column
    df_mondays = df[df["Day's name"] == 'Monday']
    
    # add a counter to count instances of match
    df_mondays['Counter'] = df_mondays['Monday Equals Previous Friday'].cumsum()
    
    # use this to see the total count of matches
    print(df_mondays['Counter'].max())