[Python] Groupby dataframe and apply different aggregate functions without knowing column...

I have a dataframe which has a key column, some value columns and some timestamp columns. For some keys, there might be multiple rows with differing values in value and timestamp columns.

I want to find the keys where there are multiple rows, the columns which have distinct values for a particular keys and then I wants to get an aggregate where, for a key, the columns with differing values are summed or averaged and max or min values are chosen for timestamp columns. The sample data is below:

data = [['key1', 10, 10, 10, pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), 'A'],
['key1', 10, 20, 10, pd.Timestamp('2024-05-11'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-06'), 'A'],
['key1', 10, 30, 10, pd.Timestamp('2024-05-11'), pd.Timestamp('2024-05-08'), pd.Timestamp('2024-05-12'), 'A'],
['key2', 10, 10, 10, pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), 'A'],
['key2', 12, 10, 10, pd.Timestamp('2024-05-13'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), 'B'],
['key3', 14, 11, 17, pd.Timestamp('2024-06-09'), pd.Timestamp('2024-05-04'), pd.Timestamp('2024-05-01'), 'C'],
['key4', 10, 10, 12, pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-11'), pd.Timestamp('2024-05-29'), 'B'],
['key5', 10, 10, 10, pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-11'), 'A'],
['key5', 10, 10, 10, pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-11'), 'C'],
['key5', 12, 11, 10, pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-11'), 'B'],
['key5', 10, 11, 10, pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-09'), pd.Timestamp('2024-05-11'), 'D']
]
columns = ['Key', 'Value1', 'Value2', 'Value3', 'Timestamp1', 'Timestamp2', 'Timestamp3', 'ID']
sample_df = pd.DataFrame(data, columns=columns)


the actual file has millions of rows and 100s of columns.

I want to get an output which looks something like this

Key CountOfrows Value1 Value2 Timestamp1 Timestamp2 Timestamp3 Id
Key1 3 10 60 '2024-05-09' '2024-05-09' '2024-05-12' 'A'
Key2 2 22 10 '2024-05-09' '2024-05-09' '2024-05-09' 'B'
Key5 4 42 42 '2024-05-09' '2024-05-09' '2024-05-11' 'B'

Here the values are aggregated by averaging if they are same for a column and summed if they are different. The timestamps are aggregated based on min for timestamp1, max for timestamp2 and timestamp3. for Id column, they are sorted by Value1 column and ID corresponding to max Value1 is taken

since value3 has the same values in all instances of a key, it is not included in the final table.

I have been able to get midway through where i have the keys with only those columns where values change.

multiple_rows_sample = sample_df.groupby(['Key']).size().reset_index(name='counts')
multiple_rows_sample = multiple_rows_sample[multiple_rows_sample['counts']>1]

mult_val_cols_sample = pd.DataFrame()


for index, row in multiple_rows_sample.iterrows():
joined_slice = sample_df[(sample_df['Key']==row['Key'])]
count_slice = row.to_frame().transpose().reset_index(drop=True)
count_slice['key']=1
diff_cols = cols_having_unique(joined_slice)
diff_cols['key']=1
output_df = pd.merge(count_slice, diff_cols, how='outer')
output_df = output_df.drop('key', axis=1)
mult_val_cols_sample = pd.concat([mult_val_cols_sample, output_df], ignore_index=True)



The mult_val_cols_sample table has the key column and only those columns where the values have changed for at least one of the keys. Now how do i run a groupby on these columns when i dont know their name beforehand?

Any help is appreciated.

Continue reading...