pandas pivot to calculate difference between max date and second to max date

user_12226377 0
Viewed 236

I have a situation where I need to pivot the data in order to fetch the most recent and next to recent entry basis the date field. My data frame looks like:

State   country      Date             confirmed deaths  recover
  A         C     1/22/20             1         0       0
  A         C     1/23/20             1         0       0
  A         C     1/24/20             15        0       0
  A         C     1/25/20             39        0       0
  B         C     1/26/20             60        0       0
  B         C     1/27/20             70        0       0
  B         C     1/28/20            106        0       0
  B         C     1/29/20            152        2       0
  B         C     1/30/20            200        2       0

and the output I want should be like this: So new the columns confirmed, deaths and recover should fetch the values basis the Max(Date) which is 1/25/20 for State A and 1/30/20 for State B and newcases , newdeaths, newrecover should be the difference of the Max date values - second to max date values.

For example for state A new cases = 39-15= 24, newdeaths = 0-0=0 , newrecover = 0-0=0

39 cases were at the max date and 24 cases were at the second to the max date as I need to get the daily change. This should be dynamic as it needs to be fetched daily

State   country   confirmed deaths  recover      newcases     newdeaths    newrecover
  A        C           39       0        0       24           0             0
  B        C           200      2        0       48           2             0
pandas pivot-table pivot aggregate python-3.x
2 Answers

sort_values by date ascending and groupby Date and extract last value in each group.Join this another groupby date that subtracts the secnd last value from the very last value in each group.

Chained solution

 df.groupby('State').tail(1).drop(columns=['Date']).merge(df.sort_values(by='Date', ascending=True).groupby('State')\
[['confirmed', 'deaths', 'recover']].apply(lambda s:(s.iloc[-1].sub(s.iloc[-2])))\
    .reset_index().rename(columns={'confirmed':'newcases','deaths':\
            'newdeaths','recover':'newrecover'}), how='left', on='State')

Step by Step Solution

g=df.groupby('State').tail(1).drop(columns=['Date'])

g1=df.sort_values(by='Date', ascending=True).groupby('State')\
[['confirmed', 'deaths', 'recover']].apply(lambda s:(s.iloc[-1].sub(s.iloc[-2])))\
    .reset_index().rename(columns={'confirmed':'newcases','deaths':\
            'newdeaths','recover':'newrecover'})


newdf=g.merge(g1, how='left', on='State')


   State country  confirmed  deaths  recover  newcases  newdeaths  newrecover
0     A       C         39       0        0        24          0           0
1     B       C        200       2        0        48          0           0 

   newrecover  
0           0  
1           0
user_89869750

One method is to create a mask m with the idxmax to return a series of the max index of the date. Then, you can create a groupby object gb as a base that filters for m by passing it to .loc (rows that contain the max date per group) and using .append to m-1 (rows that contain the second highest date per group). This groupby object base can then be used with .diff() on the relevant columns (e.g. df['new cases'] = gb['confirmed'].diff()).

m = df.reset_index().groupby(['State', 'country'])['index'].idxmax()
gb = df.loc[m].append(df.loc[m-1]).sort_index().groupby('State')
df['newcases'] = gb['confirmed'].diff()
df['newdeaths'] = gb['deaths'].diff()
df['newrecover'] = gb['recover'].diff()
df = df.dropna().drop('Date', axis=1)
df

output:

    State   country     confirmed   deaths  recover newcases    newdeaths   newrecover
3   A       C           39          0       0       24.0        0.0         0.0
8   B       C           200         2       0       48.0        0.0         0.0
user_63667700
Related