Pandas: merge two rows within group by complex conditions

user_12136878 0
Viewed 214

I have a df as below; import pandas as pd

df = pd.DataFrame({
    "ID": ['company A', 'company A', 'company A', 'company B','company B', 'company B', 'company C', 'company C','company C','company C', 'company D', 'company D','company D'],
    'Sender': [28, 'delete', 'flag_source', 56, 28, 312, 'delete', 'flag_source', 78, 102, 26, 101, 96],
    'Receiver': [129, 28, 'delete', 172, 56, 28, 61, 'delete', 12, 78, 98, 26, 101],
    'Date': ['2020-04-12', '2020-03-20', '2020-03-20', '2019-02-11', '2019-01-31', '2018-04-02', '2020-06-29', '2020-06-29', '2019-11-29', '2019-10-01', '2020-04-03', '2020-01-30', '2019-10-18'],
    'Sender_type': ['house', 'temp', 'house', 'house', 'house', 'house', 'temp', 'house', 'house','house','house', 'temp', 'house'],
    'Receiver_type': ['house', 'house', 'temp', 'house','house','house','house', 'temp', 'house','house','house','house','temp'],
    'Price': [32, 50, 47, 21, 23, 19, 52, 39, 12, 22, 61, 53, 19]
})

And it is like this:

           ID       Sender Receiver        Date Sender_type Receiver_type  Price  
0   company A           28      129  2020-04-12       house         house  32 
1   company A       delete       28  2020-03-20        temp         house  50 # combine this row with below
2   company A  flag_source   delete  2020-03-20       house          temp  47 # combine this row with above
3   company B           56      172  2019-02-11       house         house  21 
4   company B           28       56  2019-01-31       house         house  23 
5   company B          312       28  2018-04-02       house         house  19 
6   company C       delete       61  2020-06-29        temp         house  52 # combine this row and below
7   company C  flag_source   delete  2020-06-29       house          temp  39 # combine this row with above
8   company C           78       12  2019-11-29       house         house  12 
9   company C          102       78  2019-10-01       house         house  22 
10  company D           26       98  2020-04-03       house         house  61 
11  company D          101       26  2020-01-30        temp         house  53 
12  company D           96      101  2019-10-18       house          temp  19

I wish to combine/merge two rows for each group 'ID' (company x) by the following rule: combine the row in 'Sender' that contains a'flag_source' and its above row into one new row. In this new row: the Sender is the flag_source, 'Revceiver' is its above value (remove the two 'delete' values), Date is the above date, Sender_type and Receiver_type are 'house', and 'Price' is the previous above value. Then remove the two rows. For example, for company A, it will combine line 1 and line 2 to generate the new row below:

ID        Sender        Receiver  Date        Sender_type  Receiver_type  Price
company A flag_source   28        2020-03-20  house        house          50

Then use this new row to replace the previous two lines. Same rules for the other groups(in this case only apply to company A and C). In the end, I wish to have a result like this:

           ID       Sender  Receiver        Date Sender_type Receiver_type  Price
0   company A           28       129  2020-04-12       house         house   32
1   company A  flag_source        28  2020-03-20       house         house   50 # new row
2   company B           56       172  2019-02-11       house         house   21
3   company B           28        56  2019-01-31       house         house   23
4   company B          312        28  2018-04-02       house         house   19
5   company C  flag_source        61  2020-06-29       house         house   52 # new row
6   company C           78        12  2019-11-29       house         house   12
7   company C          102        78  2019-10-01       house         house   22
8   company D           26        98  2020-04-03       house         house   61
9   company D          101        26  2020-01-30        temp         house   53
10  company D           96       101  2019-10-18       house          temp   19

Hopefully my explanation for the question is clear.

As this is a brief sample, the real case has many data like this, I wrote a loop but very slow and unproductive, so please help if you have any ideas and effective way. Many many thanks for help!

pandas dataframe group-by python loops
3 Answers
import pandas as pd

df = pd.DataFrame({
    "ID": ['company A', 'company A', 'company A', 'company B','company B', 'company B', 'company C', 'company C','company C','company C', 'company D', 'company D','company D'],
    'Sender': [28, 'delete', 'flag_source', 56, 28, 312, 'delete', 'flag_source', 78, 102, 26, 101, 96],
    'Receiver': [129, 28, 'delete', 172, 56, 28, 61, 'delete', 12, 78, 98, 26, 101],
    'Date': ['2020-04-12', '2020-03-20', '2020-03-20', '2019-02-11', '2019-01-31', '2018-04-02', '2020-06-29', '2020-06-29', '2019-11-29', '2019-10-01', '2020-04-03', '2020-01-30', '2019-10-18'],
    'Sender_type': ['house', 'temp', 'house', 'house', 'house', 'house', 'temp', 'house', 'house','house','house', 'temp', 'house'],
    'Receiver_type': ['house', 'house', 'temp', 'house','house','house','house', 'temp', 'house','house','house','house','temp'],
    'Price': [32, 50, 47, 21, 23, 19, 52, 39, 12, 22, 61, 53, 19]
})

flaggedData = (df[df["Sender"] == "flag_source"])

for i,row in flaggedData.iterrows():  # Row variable contains row having sender as flag_source

    deleteRow = df[df.index == i-1].values[0]   # delete variable contains row having sender as delete

    combined = [row[0],  # ID
                row[1],  # Sender
                deleteRow[2],  # Receiver
                deleteRow[3],  # Date
                row[4],  # Sender_type
                deleteRow[5],  # Receiver_type
                deleteRow[6]]  # Price

    df.loc[i-1] = combined  # replace with new values
    df = df.drop(index=i)  # drop old values

df = df.reset_index()  # resent index for better access on future.
print(df.loc[1])

I am assuming that every "delete" row is above on "flag_source" row. Read the comments if you still don't understand, comment your doubt.

user_91030200

It seems you only need to delete the second row of each pair and replace some values in the remaining rows.

df = df[dd.Receiver == 'delete']
df.Sender = df.Sender.str.replace('delete', 'flag_source')
df.Sender_type = df.Sender_type.str.replace('temp', 'house')
user_66928980

If the delete/flag_source are always on the same Date, and if there's no other rows on that Date + ID, you can use an groupby aggregate function on the ID and Date to avoid using a long loop. If your data isn't in the correct order you can always sort_values it beforehand.

cols = df.columns

new_df = df.groupby(['ID', 'Date']).aggregate({
    'Sender': 'last', 
    'Receiver': 'first', 
    'Sender_type': 'last', 
    'Receiver_type': 'first', 
    'Price': 'first'
    }).reset_index()

# Reorder as per original data
new_df[cols].sort_values(['ID', 'Date'], ascending=[1, 0])
user_141519410
Related