Find difference between groupby values for specific categories in Pandas-CodePudding

I'd like to find the difference between values in a Pandas groupby dataframe, but for specific column values. I've read multiple posts about using the diff command, but that applies to subsequent rows regardless of groupings.

In the dataframe below (it's a dictionary), the dataframe has columns for user id trial_id, a condition placebovstreatment, a moderator variable expbin, and a value.

I want to calculate the difference between values within users, but only if they have values for certain condition categories.

For instance, user 1 has values of

correct_placebo_baseline    10.000
correct_treatment   21.000

The difference is 11.

User 2 has values of

0   22.000
correct_placebo_baseline 8.688

The difference is roughly 14.

User 1 has a difference between column categories correct_placebo_baseline and correct_treatment. User 2 has a difference between, correct_placebo_baseline and category '0'.

How do I calculate only if a user has both a correct_placebo_baseline and a 'correct_treatment' groupings? Or, alternatively, how do you create columns where the differences are specific per group per user?

The formula could create columns difference from baseline for correct placebo and 'difference from baseline for 0' for each trial_id.

The challenge is that some users don't have a baseline score. Some users have a baseline score but nothing else. I need difference values only if they have both.

I tried to find a way to run a function when groupby categories meet certain criteria, but couldn't.

Thanks for any help and let me know if I can make this question easier to answer.

{'trial_id': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 3, 6: 3, 7: 4, 8: 4, 9: 5},
 'placebovstreatment': {0: '0',
  1: 'correct_placebo_baseline',
  2: 'correct_treatment',
  3: '0',
  4: 'correct_placebo_baseline',
  5: 'correct_placebo_baseline',
  6: 'incorrect_placebo',
  7: 'correct_placebo_baseline',
  8: 'incorrect_placebo',
  9: '0'},
 'expbin': {0: 1, 1: 1, 2: 1, 3: 2, 4: 2, 5: 2, 6: 2, 7: 1, 8: 1, 9: 1},
 'value': {0: 31.5,
  1: 10.0,
  2: 21.0,
  3: 22.0,
  4: 8.688,
  5: 20.0,
  6: 37.5,
  7: 12.0,
  8: 32.5,
  9: 10.0}}

CodePudding user response：

You can use the groupby method to group the dataframe by the 'user_id' column and then use the transform method to create new columns with the difference of values within each group. To only calculate the difference if the user has both a 'correct_placebo_baseline' and 'correct_treatment' groupings, you can use the any method to check if the group has both values. Here's an example of how you can do this

df = pd.DataFrame(data)
df['difference_placebo_treatment'] = df.groupby('user_id')['value'].transform(lambda x: x.diff())
df['has_both_placebo_treatment'] = df.groupby('user_id')['placebovstreatment'].transform(lambda x: x.eq('correct_placebo_baseline').any() & x.eq('correct_treatment').any())
df['difference_placebo_baseline'] = np.where(df['has_both_placebo_treatment'], df.groupby(['user_id', 'placebovstreatment'])['value'].transform(lambda x: x.diff()), np.nan)

This code first creates a new column 'difference_placebo_treatment' with the difference of values within each 'user_id' group. Then it creates a new column 'has_both_placebo_treatment' that checks if the group has both 'correct_placebo_baseline' and 'correct_treatment' values. Finally, it creates a new column 'difference_placebo_baseline' that only calculates the difference if the 'has_both_placebo_treatment' column is True, otherwise it will be NaN.

CodePudding user response：

You can pivot to get the conditions as columns:

df2 = df.pivot(index=['trial_id', 'expbin'], columns='placebovstreatment', values='value')

Output:

placebovstreatment     0  correct_placebo_baseline  correct_treatment  incorrect_placebo
trial_id expbin                                                                         
1        1          31.5                    10.000               21.0                NaN
2        2          22.0                     8.688                NaN                NaN
3        2           NaN                    20.000                NaN               37.5
4        1           NaN                    12.000                NaN               32.5
5        1          10.0                       NaN                NaN                NaN

You can then easily perform computations:

df2['correct_treatment'] - df2['correct_placebo_baseline']

Output:

trial_id  expbin
1         1         11.0
2         2          NaN
3         2          NaN
4         1          NaN
5         1          NaN
dtype: float64