CODEWITHSUNDEEP
pythonpandasdateaveragedata-science
Sara
Sara

Reputation: 97

Compare averages of a values corresponding to 2 different dates?

I have a table like this:

Date          Student    Average(for that date)
17 Jan 2020   Alex          40  
18 Jan 2020   Alex          50
19 Jan 2020   Alex          80
20 Jan 2020   Alex          70
17 Jan 2020   Jeff          10
18 Jan 2020   Jeff          50
19 Jan 2020   Jeff          80
20 Jan 2020   Jeff          60

I want to add a column for high and low. The logic for that column should be that it is high as long as the average score for a student for today`s date is greater than the value < 90% of previous days score. Like my comparison would look something like this:

avg(score)(for current date) < ( avg(score)(for previous day) - (90% * avg(score)(for previous day) /100)

I can`t figure how to incorporate the date part in my formula.That it compares averages from current day to the average of the previous date.

I am working with Pandas so i was wondering if there is a way in it to incorporate this.

Upvotes: 0

Views: 70

Answers (1)

Scott Boston
Scott Boston

Reputation: 153500

IIUC,

df['Previous Day'] = df.sort_values('Date').groupby('Student')['Average'].shift()*.90

df['Indicator'] = np.where(df['Average']>df['Previous Day'],'High','Low')

df

Output:

        Date Student  Average  Previous Day Indicator
0 2020-01-17    Alex       40           NaN       Low
1 2020-01-18    Alex       50          36.0      High
2 2020-01-19    Alex       80          45.0      High
3 2020-01-20    Alex       70          72.0       Low
4 2020-01-17    Jeff       10           NaN       Low
5 2020-01-18    Jeff       50           9.0      High
6 2020-01-19    Jeff       80          45.0      High
7 2020-01-20    Jeff       60          72.0       Low

Upvotes: 1

Related Questions