如何在Pandas DataFrame中按state分组由累计值计算日增量？

Hey there! Let's break down how to compute that incremental_count column you need. The core idea is to calculate the difference between the current day's cumulative count and the previous day's cumulative count for each state. For the first day of each state, the incremental count is just the cumulative count itself since there's no prior data.

Here's how you can do this with pandas:

Step 1: Import pandas and create your sample DataFrame #

import pandas as pd

# Your original DataFrame
df1 = pd.DataFrame({
    'date': ['2020-01-03','2020-01-03','2020-01-03','2020-01-04','2020-01-04','2020-01-04','2020-01-05','2020-01-05','2020-01-05'],
    'state': ['NJ','NY','CT','NJ','NY','CT','NJ','NY','CT'],
    'cumulative_count': [1,3,5,3,6,7,19,15,20]
})

Step 2: Compute the incremental count #

We'll use groupby() to group the data by state, then apply diff() to calculate the day-over-day change in cumulative_count. For the first entry in each group (which will return NaN from diff()), we'll fill those values with the original cumulative_count since that's the initial increment.

# Calculate incremental count
df1['incremental_count'] = df1.groupby('state')['cumulative_count'].diff().fillna(df1['cumulative_count'])

# Convert to integer (since diff() returns float, and we want whole numbers)
df1['incremental_count'] = df1['incremental_count'].astype(int)

Step 3: Verify the result #

If you print df1, you'll get exactly the df2 you provided:

date state  cumulative_count  incremental_count
0  2020-01-03    NJ                 1                  1
1  2020-01-03    NY                 3                  3
2  2020-01-03    CT                 5                  5
3  2020-01-04    NJ                 3                  2
4  2020-01-04    NY                 6                  3
5  2020-01-04    CT                 7                  2
6  2020-01-05    NJ                19                 16
7  2020-01-05    NY                15                  9
8  2020-01-05    CT                20                 13

How this works: #

• groupby('state'): Ensures we only calculate differences within each state's data, so we don't mix values from NJ with NY or CT.
• diff(): Computes the difference between the current row and the previous row in each group. For the first row of a group, there's no prior data, so this returns NaN.
• fillna(df1['cumulative_count']): Replaces those NaN values with the original cumulative count (since day 1's increment is the total count recorded that day).
• astype(int): Converts the float result from diff() back to integer to match your example's whole-number output.

内容的提问来源于stack exchange，提问作者Han