Convert pandas dataframe from cumulative time series to absolute change time series

user_10138766 0
Viewed 64

I have imported a data table from an SQL database. Each row of this table contains the response of a query to the twitter api, which is run quite frequently. The stats are cumulative. So if on the first call there are 20 impressions, and by the time we make another call the impressions have increased by 5, the response will return [20, 25].

What I want to do is group this data by day, taking the last entry for that day (this makes sense since the table is auto-incremented). However, for each day, I want to find the absolute change in the number of impressions for each post. Therefore I can get a global picture of how the account is performing over time. I have found a solution, but it seems very cumbersome. I am wondering if there is a better way to achieve this. Here is a sample of my initial dataset:

social_post_id  twitter_impression_count    created
0   52          6.0                         2022-04-14 15:40:07
1   52          11.0                        2022-04-14 16:00:08
2   52          11.0                        2022-04-14 16:20:08
3   52          11.0                        2022-04-14 16:40:05
4   52          11.0                        2022-04-14 17:00:08
... ... ... ...
945 142         8.0                         2022-05-19 09:20:17
946 142         8.0                         2022-05-19 09:40:10
947 142         8.0                         2022-05-19 10:00:12
948 142         8.0                         2022-05-19 10:20:11
949 142         8.0                         2022-05-19 10:40:12

This is the code I have used to transform my data:

day_list = sorted(list(set(df['created'].dt.date)))

current_day = df[df['created'].dt.date==day_list[0]].drop_duplicates(subset='social_post_id', keep='last').set_index('social_post_id')

for day in day_list[1:]:
    next_day = df[df['created'].dt.date==day].drop_duplicates(subset='social_post_id', keep='last').set_index('social_post_id')
    for index in next_day.index:
        if index in current_day.index:
            next_day.loc[index, 'twitter_impression_count'] = next_day.loc[index, 'twitter_impression_count'] - current_day.loc[index, 'twitter_impression_count'].sum()
    current_day = pd.concat([current_day, next_day])

This outputs my table in the correct format, but as I said, I don't feel like this is a very optimum solution...

current_day.reset_index()

social_post_id  twitter_impression_count    created
0   52          18.0                        2022-04-14 23:40:08
1   52          4.0                         2022-04-15 23:20:07
2   55          8.0                         2022-04-15 23:40:07
3   52          1.0                         2022-04-16 15:20:08
4   60          7.0                         2022-04-16 23:40:08
... ... ... ...
182 122         48.0                        2022-05-18 12:20:11
183 82          5.0                         2022-05-18 17:40:13
184 72          2.0                         2022-05-18 21:00:11
185 142         6.0                         2022-05-18 23:40:16
186 142         2.0                         2022-05-19 10:40:12
pandas python-3.x python
1 Answers

Try the following:

  • first, i understand that the date column is already sorted (no need for your first line)
  • drop_duplicates supports multiple column values, so make a day column and use it with social_post_id to drop your duplicates
  • group by social_post_id to compute the inverse of the cumulative sum
  • compute the absolute change using diff method in pandas
  • sort by date and drop day column, if necessary

Here is the resulting code:

df["day"] = df.created.dt.date
df = df.drop_duplicates(subset=["social_post_id", "day"], keep='last')

grps = list()
for _, grp in df.groupby("social_post_id", sort=False): # sort=False, for performance
    # if we have one row only, we don't need to compute anything
    if grp.shape[0] > 1:
        first_i = grp.index[1]
        grp.loc[first_i:, "twitter_impression_count"] = grp.twitter_impression_count.diff().iloc[1:]
    grps.append(grp)
df = pd.concat(grps)

df.sort_values("created", inplace=True)
#df.drop("day", axis=1, inplace=True)
user_178746070
Related