How to capture last snapshot on each day in a dataframe python

user_15178375 0
Viewed 223

I have a dataframe like this:

                            A         B        C
2015-01-01 00:00:00     597.5259   376.1830    a
2015-01-01 00:00:00     461.2014   244.0453    a
2015-01-01 00:00:00     283.9003   166.5772    b
2015-01-01 09:04:00      93.5099    50.7761    a
2015-01-01 09:04:00       1.1559     0.2784    b
2015-01-01 09:31:00      95.0285    29.1006    a
2015-01-01 09:31:00     241.8822   120.1049    b
2015-01-01 12:16:00     363.8040   196.0568    a
2015-01-01 14:04:00     438.2264   274.3733    a
2015-01-01 16:30:00     456.3396   330.6650    a
2015-01-01 16:30:00     368.3211     0.2617    a
2015-01-01 16:30:00     456.3396     0.1939    b

2015-01-02 09:28:00     241.8822   120.1049    b
2015-01-02 09:28:00     212.3111   389.2911    b
2015-01-02 10:27:00     363.8040   196.0568    a
2015-01-02 13:02:00     438.2264   274.3733    a
2015-01-02 16:27:00     456.3396   330.6650    a
2015-01-02 16:27:00     368.3211     0.2617    a
2015-01-02 16:27:00     456.3396     0.1939    b
    ...                   ...        ...       ...

I want to get the last snapshot for each day in the dataframe, i.e.

                            A         B        C
2015-01-01 16:30:00     456.3396   330.6650    a
2015-01-01 16:30:00     368.3211     0.2617    a
2015-01-01 16:30:00     456.3396     0.1939    b

2015-01-02 16:27:00     456.3396   330.6650    a
2015-01-02 16:27:00     368.3211     0.2617    a
2015-01-02 16:27:00     456.3396     0.1939    b

I am a new learner of python and my train of thought is to define a function to get the list of timestamp and select the last element of the list. But this is very complicated to me and I don't know how to perform that.

I tried

df = df.groupby(df.index).last()

or

df = df.groupby(df.index.timestamp).apply(lambda x: x.iloc[[0, -1]])

These methods are not working because the number of rows for the last snapshot can be different for each day. Can you help me out? Many thanks.

pandas dataframe python
3 Answers

You can group by day and compare with last index:

print (df.groupby(pd.Grouper(freq="D"), as_index=False).apply(lambda d: d[d.index==d.index[-1]]))

Or compare using transform:

print (df[df.index==df.reset_index().groupby(pd.Grouper(key="index", freq="D"))["index"].transform("max")])

                              A         B  C
0 2015-01-01 16:30:00  456.3396  330.6650  a
  2015-01-01 16:30:00  368.3211    0.2617  a
  2015-01-01 16:30:00  456.3396    0.1939  b
1 2015-01-02 16:27:00  456.3396  330.6650  a
  2015-01-02 16:27:00  368.3211    0.2617  a
  2015-01-02 16:27:00  456.3396    0.1939  b
user_92844230

You can also try:

s = df.index.date
i = pd.DataFrame({"date":s,"index":df.index}).groupby("date")['index'].max()
latest_snapshot = df[df.index.isin(i)]

                            A         B  C
2015-01-01 16:30:00  456.3396  330.6650  a
2015-01-01 16:30:00  368.3211    0.2617  a
2015-01-01 16:30:00  456.3396    0.1939  b
2015-01-02 16:27:00  456.3396  330.6650  a
2015-01-02 16:27:00  368.3211    0.2617  a
2015-01-02 16:27:00  456.3396    0.1939  b
user_98406370

Floor the index values, then use duplicated to create a boolean mask which helps us find the last index value(snapshot) per date

m = df.index.floor('D').duplicated(keep='last')
df.loc[df.index[~m]]

                            A         B  C
2015-01-01 16:30:00  456.3396  330.6650  a
2015-01-01 16:30:00  368.3211    0.2617  a
2015-01-01 16:30:00  456.3396    0.1939  b
2015-01-02 16:27:00  456.3396  330.6650  a
2015-01-02 16:27:00  368.3211    0.2617  a
2015-01-02 16:27:00  456.3396    0.1939  b
user_128331660
Related