I want to create new columns and add that to original data-frame that is calculated by groups using multiple columns from current data frame. Basically something like this in python:
I want to achieve this using transform, apply, group-by all used at once
Group by: << 'c_uid','s_uid','sender'>>
Columns to aggreagte: << text, Number>>
New Columns : <<merged_text, Number_sum>>
Condition: Aggregate only if <<Sender>> value is consecutive. Like agent, agent or cust, cust in group by.
Original Data-frame
c_uid s_uid m_uid(unique) sender text Number
1 a ab agent How may I help you? 10.8
1 a ac agent Please let me know, how can I help you? 0.2
1 a ad cust My account is blocked. 12
1 a ae agent Please let me look into the same 11.3
1 b af cust Ok. I am waiting 11.28
1 b ag cust Are you connected? 4.2
2 c ah agent How may I help you? 5
2 c ai cust My internet is not working? 8
2 d af agent Your user acccount number? 9
2 e ag cust Here is my account number. 10
2 f ah cust I have given account number, please revert. 11.5
I want to create intermediate table group by <<c_uid, s_uid, sender >> and group text and add Numbers columns if there is consecutive text by sender in that group.
Intermediate Table
c_uid s_uid m_uid(unique) sender text merged_text Number_sum
1 a ab agent How may I help you? [How may I help you?,Please let me know, how can I help you?]. 11
1 a ac agent Please let me know, how can I help you? [How may I help you?,Please let me know, how can I help you?] 11
1 a ad cust My account is blocked. [My account is blocked.] 12
1 a ae agent Please let me look into the same [Please let me look into the same] 11.3
1 b af cust Ok. I am waiting [Ok. I am waiting, Are you connected?] 15.48
1 b ag cust Are you connected? [Ok. I am waiting, Are you connected?] 15.48
2 c ah agent How may I help you? [How may I help you?] 5
2 c ai cust My internet is not working? [My internet is not working?] 8
2 d af agent Your user acccount number? [Your user acccount number?] 9
2 e ag cust Here is my account number. [Here is my account number.] 10
2 f ah cust I have given account number, please revert.[I have given account number, please revert.] 11.5
I want to save this above table and pivot in this way
I want to create it like agent and customer response pair within same group by that I did earlier table
Pivotted_final Table. This will also have Numbers_sum column.
c_uid s_uid m_uid(unique) agent ust
1 a ab [How may I help you?,Please let me know, how can I help you?] [My account is blocked.]
1 a ac [How may I help you?,Please let me know, how can I help you?] [My account is blocked.]
1 a ad [How may I help you?,Please let me know, how can I help you?] [My account is blocked.]
1 a ae [Please let me look into the same] NA
1 b af NA [Ok. I am waiting, Are you connected?]
1 b ag NA [Ok. I am waiting, Are you connected?]
2 c ah [How may I help you?] [My internet is not working?]
2 c ai [How may I help you?] [My internet is not working?]
2 d af [Your user acccount number?]
2 e ag NA [Here is my account number.]
2 f ah NA [I have given account number, please revert.]
Code:
I tried something like this but it did not work. It groups all agents and customers text in one segment, but I want only consecutive text to get aggregated.
aggregated_data = data.groupby(['c_uid','s_uid','sender'])
aggregated_data=aggregated_data.agg({'text' : lambda x: list(x)}).reset_index()
It also changes the original data-frame and I am not able to sum numbers in same code.