Take the following as an example to filter a data frame based on certain conditions:

There are 2 users and some of their corresponding bill dates and bill amounts, we want to only keep one record for each user with their billing's starting date and the average bill amount.

Dataframe

import pandas as pd

raw_data = {'User':[1,1,1,2,2,2,1], 
        'Time':['2014-01-01','2014-02-01','2014-03-01','2018-02-01','2018-01-01','2018-03-01','2014-04-01'],
        'Amount':[32,13,48,98,64,23,27]}
data = pd.DataFrame(raw_data)
data
Python
User Time Amount
0 1 2014-01-01 32
1 1 2014-02-01 13
2 1 2014-03-01 48
3 2 2018-02-01 98
4 2 2018-01-01 64
5 2 2018-03-01 23
6 1 2014-04-01 27

Solution 1

Package: Pandas
Function: dataframe.groupby()
Docs: pandas.DataFrame.groupby

# generate new columns for grouped results
data_amount = round(data.groupby(['User'], as_index=False)['Amount'].mean(),2)
data_time = data.groupby('User', as_index=False)['Time'].min()
Python
# merge columns to main dataframe
data_merge1 = pd.merge(data,data_time,on='User',how='left')
data_merge2 = pd.merge(data_merge1,data_amount,on='User',how='left')
data_merge2
Python
User Time_x Amount_x Time_y Amount_y
0 1 2014-01-01 32 2014-01-01 30.00
1 1 2014-02-01 13 2014-01-01 30.00
2 1 2014-03-01 48 2014-01-01 30.00
3 2 2018-02-01 98 2018-01-01 61.67
4 2 2018-01-01 64 2018-01-01 61.67
5 2 2018-03-01 23 2018-01-01 61.67
6 1 2014-04-01 27 2014-01-01 30.00 
# drop old columns and rename new columns
data_new = data_merge2.drop(columns=['Time_x','Amount_x'])
data_new = data_new.drop_duplicates(['User'])
data_new = data_new.rename(columns={'Amount_y': 'Amount_mean','Time_y':'Date'})
data_new
Python
User Date Amount_mean
0 1 2014-01-01 30.00
3 2 2018-01-01 61.67

Alternative

Alternatively, we could also drop_duplicates first then merge new columns:

data2 = data.drop_duplicates(['User'])
data2_merge1 = pd.merge(data2,data_time,on='User',how='left')
data2_merge2 = pd.merge(data2_merge1,data_amount,on='User',how='left')
data2_merge2
Python
User Time_x Amount_x Time_y Amount_y
0 1 2014-01-01 32 2014-01-01
1 2 2018-02-01 98 2018-01-01 
data2_new = data2_merge2.drop(columns=['Time_x','Amount_x'])
data2_new = data2_new.drop_duplicates(['User'])
data2_new = data2_new.rename(columns={'Amount_y': 'Amount_mean','Time_y':'Date'})
data2_new
Python
User Date Amount_mean
0 1 2014-01-01 30.00
1 2 2018-01-01 61.67

Solution 2

Here we sort the value by User and Time ascendingly, then drop_cuplicates. We get a dataframe ordered by User then Time.

data3 = data.sort_values(by=['User','Time'],ascending=True).drop_duplicates(['User'])
data3
Python
User Time Amount
0 1 2014-01-01 32
4 2 2018-01-01 64

Now we only need to merge the Amount_mean to the dataframe:

data3 = pd.merge(data3,data_amount,on='User',how='left')
data3
Python
User Time Amount_x Amount_y
0 1 2014-01-01 32 30.00
1 2 2018-01-01 64 61.67

Lastly, drop old column and rename new column:

data3_new = data3.drop(columns=['Amount_x'])
data3_new = data3_new.rename(columns={'Amount_y': 'Amount_mean','Time':'Date'})
data3_new
Python
User Date Amount_mean
0 1 2014-01-01 30.00
1 2 2018-01-01 61.67

Source Code