groupby function based off multiple rows

user_6673799 0
Viewed 49

Here's my data:

id date period score
1 2015-01-01 start 5
1 2015-01-15 end 10
2 2015-01-01 start 2
2 2015-01-07 mid_1 7
2 2015-01-14 end 10

These are records of scores over time. I have encoded the period column so its easy for you to interpret.

How can I find difference or percent change in scores from start to end?

If I do pd.groupby, I could find the min or max date, but how would I match that to the score on min or max date?

I don't care if the calculation uses date or period, I just need to figure out how to match.

I want data that looks like this:

id change_in_score_start_to_end
1 100%
2 400%
pandas-groupby pandas python
2 Answers

Here is another approach:

Code:

import pandas as pd

df = pd.read_csv('test.csv')
df = df.drop('date', axis=1).set_index(['id', 'period']).unstack(1).droplevel(0 , axis=1)

df['change_in_score_start_to_end'] = ((df['end']/df['start'] -1)*100).map('{:.0f}%'.format)
print(df)

Output:

period   end  mid_1  start change_in_score_start_to_end
id                                                     
1       10.0    NaN    5.0                         100%
2       10.0    7.0    2.0                         400%
user_153478050

One option would be to pivot:

new_df = df.pivot(index='id', columns='period', values='score')

new_df:

period   end  mid_1  start
id                        
1       10.0    NaN    5.0
2       10.0    7.0    2.0

Then computation can be done in row on end and start columns:

out = (
    (((new_df['end'] - new_df['start']) / new_df['start']) * 100)
        .map('{:.0f}%'.format)
        .reset_index(name='change_in_score_start_to_end')
)

out:

   id change_in_score_start_to_end
0   1                         100%
1   2                         400%

Complete Working Example:

import pandas as pd

df = pd.DataFrame({
    'id': [1, 1, 2, 2, 2],
    'date': ['2015-01-01', '2015-01-15', '2015-01-01', '2015-01-07',
             '2015-01-14'],
    'period': ['start', 'end', 'start', 'mid_1', 'end'],
    'score': [5, 10, 2, 7, 10]
})

new_df = df.pivot(index='id', columns='period', values='score')
out = (
    (((new_df['end'] - new_df['start']) / new_df['start']) * 100)
        .map('{:.0f}%'.format)
        .reset_index(name='change_in_score_start_to_end')
)

print(out)
user_154978880
Related