I am not sure if what I want is achievable this way, but. I have an OTRS report in CSV, giving me bunch of tickets and their states. Records for one ticket look like this:

 ticketnumber type_name              state            Owner           queue customer          create_time  id             subject  time_unit
0  2015071510123456       NaN  closed successful       Admin OTRS       QA      NaN  2018-06-22 11:55:19   1    Welcome to OTRS!        NaN
1  2015071510123456       NaN  closed successful        Igor            QA      NaN  2018-07-02 08:38:28  50  Changing the owner        2.0
2  2015071510123456       NaN  closed successful        Igor            QA      NaN  2018-07-02 08:39:08  51              Closed        1.0

So, pattern is the same, new ticktes have "Admin OTRS" as owner, and their create time is the oldest. Ticket closure is visible from the fact that Owner is not "Admin OTRS", and create_time is newest in selection, plus there is "subject" change to "Close".

What I'm trying to do now is the following:

filtered_df['create_time']=pd.to_datetime(filtered_df['create_time'])
print(filtered_df['create_time'].dtype)
a = filtered_df.groupby('ticketnumber')['create_time'].unique()

Which gives me a list of times per ticket.

What I'd like to do is: - filter that out to get only newest and oldest datetime - compare if Owner of the oldest is "Admin OTRS" or not - compare if the subject of the oldest is "Closed" - Return those that are open and closed in the given period - Return sum of all time_allocated per ticket (I'm currently doing this separately, but if I can reduce the number of iterations, why not)

But, I'm stuck here. Don't know how to make use of "multiple" columns, eg. group by ticketnumber and create_time, but take Owner and Subject in the mix as well?

I'd expect something like this as a result ( or multiple results)

ticketnumber type_name              state            Owner           queue customer          create_time  id             subject  time_unit
0  2015071510123456       NaN  closed successful       Admin OTRS       QA      NaN  2018-06-22 11:55:19   1    Welcome to OTRS!        NaN
1  2015071510123456       NaN  closed successful        Igor            QA      NaN  2018-07-02 08:39:08  51              Closed        1.0

So, for all the tickets (and there can be many lines, not just 3, depending how the communication went and how many back and froths happened), I'd like to get the first one - making sure it is owned by Admin OTRS, and last one, making sure it is the latest and it contains subject "Closed"

0 Answers