I have data frame like this and I want to track changes but within every RB group, so I want to add column if there is change set 1, else 0
2 Answers
You can create a new column as change for the difference, but firstly group by them [RB with value].
df.sort_values(['RB'], inplace=True)
df['change'] = (df.value != df.value.shift()).astype(int)
print(df)
Here you go:
import pandas as pd
from calendar import month_abbr
months = [month.lower() for month in month_abbr[1:]]
df = pd.DataFrame({
'RB': [2335] * 4 + [3567] * 4 + [1245] * 4,
'time': months[:4] * 3,
'value': ['good day'] * 2 + ['may be'] * 2 + ['what'] * 4 + ['sure'] * 3 + ['no']
})
df['time'] = pd.Categorical(df['time'], categories=months, ordered=True)
df = df.sort_values(['RB', 'time'])
df['previous'] = df['value'].shift()
counts = df.groupby(['RB'], sort=False).count()['value']
counts.iloc[0] = 0
counts = counts.cumsum()
cols = df.columns.to_list()
df.iloc[counts, cols.index('previous')] = df.iloc[counts, cols.index('value')]
df['change'] = (df['previous'] != df['value']).astype(int)
cols = ['previous', 'value']
change_counts = df[df['change'] == 1][cols].groupby(cols).size().reset_index()
change_counts.columns = ['from', 'to', 'counts']
change_counts = change_counts.sort_values(by=['counts'], ascending=False)
df = df.drop('previous', axis=1)
print(df)
print()
print(change_counts)
Output:
RB time value change
8 1245 jan sure 0
9 1245 feb sure 0
10 1245 mar sure 0
11 1245 apr no 1
0 2335 jan good day 0
1 2335 feb good day 0
2 2335 mar may be 1
3 2335 apr may be 0
4 3567 jan what 0
5 3567 feb what 0
6 3567 mar what 0
7 3567 apr what 0
from to counts
0 good day may be 1
1 sure no 1
Related