Analyzing workflow status, add sum, write workflow process in data

Question

I have a csv file that contains a record of a workflow. It contains for each timestamp the status. So I do have time and day when something was done, however I sorted it in ascending order and this is sufficient for the next step, therefore this is not included in this sample data. My sample data looks like this (csv files are attached, Example1.csv and Example2.csv, the preview in google looks wrong, as the decimal "," separator is not properly recognized):

So as I said these files are already sorted in ascending order and the status could be imagined as something like a workflow. So work started, proceed, finished, clean up. Like this:

Now I want to detect suspicious entries. For example if someone finished work without actually started it, or other unusual "patterns". What I would like to have is an overview of all the different workflows.

1. I would like to have the counts / number of occurences per unique workflow. I achieved to implement this. My code is as follows:

import pandas as pd
from collections import OrderedDict

df=pd.read_csv(r'C:\Users\PC\Desktop\Example2.csv', sep=";", decimal=",", encoding="utf-8-sig")

df['Status']=df['Status'].astype(str)
df['Status'].fillna('No', inplace=True)

df=df.groupby(['Worker'])['Status'].apply('|'.join).reset_index()
df=df.groupby(['Status']).count()
df = df.rename(columns={'Worker': 'Count'})
#df['Sum']=df.groupby(['Amount']).sum()
df.to_csv(r'C:\Users\PC\Desktop\outtest.csv', sep=';', encoding="utf-8-sig")

Which works. I get the following output:

or in case of using numbers:

Which is exactly what I want. Here I can see for example that two workers started work and then directly cleaned up.

2. Now I would like to have the sum of the amounts too. The amount per worker is always the same, so this number does not vary for a worker, so for example as shown in the sample data, worker 1 always has 2500,24. What I would like to have is this output:

I tried to implement it with adding a simple line:

df['Sum']=df.groupby(['Amount']).sum()

But this throws an error. Reason is that the Amount in this step is simply not available. I could not figure out how to get this working.

How can I add the sum?

3. I would like to "write the type of workflow which was counted for this worker" back to my original data file. So in my original data it should look like this (for simplicity reasons lets take the version where the status is represented with numbers):

How can I implement this?

(I thought about this and it actually does not need to be combined with the results from my previous code. I just basically need to expand/transpose the status for each worker and write this to a new variable/column. However here the problem is that I do not know in advance how many status/steps a worker has. So somehow I need to implement that "if the next entry belongs to the same worker than attach the value from status with a "|" to an existing variable" and this is my new column. But maybe I am wrong here and there is another implementation.)

user_6792743 · Answer

To calculate sum of amounts we can first groupby the Worker column to get the workflow and the amount for each worker (I'm taking first for the amount since it's the same for all rows for the same worker). Then we groupby again on the workflow (which is in Status column after the first groupby), and calculate counts and sums:

df = pd.read_csv('Example2.csv', sep=';', decimal=',')
df['Status'] = df['Status'].astype(str)

z = df.groupby('Worker').agg({
    'Status': '|'.join,
    'Amount': 'first',
}).groupby('Status')['Amount'].agg(['count', 'sum']).reset_index()

# save and output
z.to_csv('outtest.csv', sep=';')
z

Output:

                                              Status  count      sum
0                                       Started work      1  2900.00
1                              Started work|Clean up      2  3600.18
2      Started work|Continued work|Finished|Clean up      2  6700.74
3  Started work|Continued work|Finished|Clean up|...      1  4200.98

To add workflow as a column, we can use transform:

df = pd.read_csv('Example1.csv', sep=';', decimal=',')
df['Status'] = df['Status'].astype(str)

# add workflow column
df['workflow'] = df.groupby('Worker')['Status'].transform('|'.join)

# save and output
df.to_csv('Example1_with_workflow.csv', sep=';', decimal=',')
df

Output (using the numeric Example1.csv here to make it more readable, but will work with either of them, of course):

    Worker Status   Amount   workflow
0        1      1  2500.24    1|2|3|4
1        1      2  2500.24    1|2|3|4
2        1      3  2500.24    1|2|3|4
3        1      4  2500.24    1|2|3|4
4        2      1  2400.00        1|4
5        2      4  2400.00        1|4
6        3      1  4200.98  1|2|3|4|5
7        3      2  4200.98  1|2|3|4|5
8        3      3  4200.98  1|2|3|4|5
9        3      4  4200.98  1|2|3|4|5
10       3      5  4200.98  1|2|3|4|5
11       4      1  1200.18        1|4
12       4      4  1200.18        1|4
13       5      1  4200.50    1|2|3|4
14       5      2  4200.50    1|2|3|4
15       5      3  4200.50    1|2|3|4
16       5      4  4200.50    1|2|3|4
17       6      1  2900.00          1

P.S. If I read correctly, in (1) there was no question as everything worked as expected, right?

Analyzing workflow status, add sum, write workflow process in data

1 Answers