CODEWITHSUNDEEP
pythonpandasdataframe
hkbgner
hkbgner

Reputation: 331

DataFrame.groupby.apply() with lambda functions

I have a dataframe as follows:

Datetime                 Value
--------------------------------------------
2000-01-01 15:00:00      10
2000-01-01 16:00:00      12
2000-01-01 17:00:00      14
2000-01-01 18:00:00      16
2000-01-02 15:00:00      13
2000-01-02 16:00:00      18
2000-01-02 17:00:00      16
2000-01-02 18:00:00      15
--------------------------------------------

I want to get a column where I can obtain the difference of values from a specific time for each day onwards (let's say 16:00:00), as follows:

Datetime                 Value     NewColumn
--------------------------------------------
2000-01-01 15:00:00      10        -
2000-01-01 16:00:00      12        0
2000-01-01 17:00:00      14        2
2000-01-01 18:00:00      16        4
2000-01-02 15:00:00      13        -
2000-01-02 16:00:00      18        0
2000-01-02 17:00:00      16        -2
2000-01-02 18:00:00      15        -3
--------------------------------------------

I have tried the following code but it shows an error of: 

df['NewColumn'] = df.groupby('Datetime')['Value'].apply(lambda x: x - df.loc[(df['Datetime'].dt.time == dt.time(hour=16)), 'Value'])

ValueError: Buffer dtype mismatch, expected 'Python object' but got 'long long'

How should I write my code instead?

Upvotes: 2

Views: 175

Answers (1)

moys
moys

Reputation: 8033

IIUC, this is what you need.

df['Datetime']=pd.to_datetime(df['Datetime'])
df['NewColumn'] = (df.groupby(pd.Grouper(freq='D', key='Datetime'))['Value']
 .apply(lambda x: x - df.loc[x.loc[df['Datetime'].dt.hour == 16].index[0],'Value']))
df.loc[df['Datetime'].dt.hour < 16, 'NewColumn'] = '-'
print(df)

Output

              Datetime  Value   NewColumn
0   2000-01-01 15:00:00     10  -
1   2000-01-01 16:00:00     12  0
2   2000-01-01 17:00:00     14  2
3   2000-01-01 18:00:00     16  4
4   2000-01-02 15:00:00     13  -
5   2000-01-02 16:00:00     18  0
6   2000-01-02 17:00:00     16  -2
7   2000-01-02 18:00:00     15  -3

Upvotes: 1

Related Questions