Title: dataframe transformation to summarize information about users
Tags: python;pandas
Question: How can I transform this dataframe:
userid date        code
y  2020-01-02    56
y  2020-01-03    00
x  2020-01-02    00
x  2020-02-02    07
x  2020-02-05    07
x  2020-02-07    07
To this one:
userid  code  retries  approved
y     56        1       yes
x     07        3       no
The approved column should show 'yes' is after some code different from '00' the code '00' appears again. When the code '00' does not appear the approved column should show 'no'. The retries column show how many times the code appears.
Comment: what is the logic for the approved column? can you briefly explain that in the body of the question? Thanks

Comment: and why is the `retires` 3 for x? is it no of code apart from 00?

Comment: to be approved is it enough and suffient to have last code 00 and one non zero code? that is is approved z with codes 22 00 33, or u with codes 00 00

Comment: sure! It's done in the end of the question

Comment: The retries column shows how many times the code appears. The approved column shows no because there is no code 00 after the code 07

Here is the accepted answer: Here is a vectorized way to do this:
&gt;&gt;&gt; df = pd.DataFrame({'userid':['y', 'y', 'x','x', 'x', 'x'], 'code': ['56', '00', '00', '07', '07', '07']})
&gt;&gt;&gt; df
userid code
0      y   56
1      y   00
2      x   00
3      x   07
4      x   07
5      x   07
&gt;&gt;&gt; df['retries'] = ~(df['code']=='00')
&gt;&gt;&gt; df
userid code  retries
0      y   56     True
1      y   00    False
2      x   00    False
3      x   07     True
4      x   07     True
5      x   07     True
&gt;&gt;&gt; df['cumsum'] = df.groupby('userid')['retries'].transform(pd.Series.cumsum)
&gt;&gt;&gt; df
userid code  retries  cumsum
0      y   56     True       1
1      y   00    False       1
2      x   00    False       0
3      x   07     True       1
4      x   07     True       2
5      x   07     True       3
&gt;&gt;&gt; df.loc[(df['code'] == '00') &amp; (df['cumsum']!=0), 'approved'] = 1
&gt;&gt;&gt; df
userid code  retries  cumsum  approved
0      y   56     True       1       NaN
1      y   00    False       1       1.0
2      x   00    False       0       NaN
3      x   07     True       1       NaN
4      x   07     True       2       NaN
5      x   07     True       3       NaN
Now you can groupby userid and aggregate the data:
&gt;&gt;&gt; final_df = df.groupby('userid').agg({'approved': sum, 'code': max, 'retries': sum})
&gt;&gt;&gt; final_df.loc[final_df['approved']!=0, 'approved'] = 'no'
&gt;&gt;&gt; final_df.loc[final_df['approved']==0, 'approved'] = 'yes'
&gt;&gt;&gt; final_df
approved code  retries
userid
x           yes   07      3.0
y            no   56      1.0
To summarize, these are only a few lines of code:
df['retries'] = ~(df['code']=='00')
df['cumsum'] = df.groupby('userid')['retries'].transform(pd.Series.cumsum)
df.loc[(df['code'] == '00') &amp; (df['cumsum']!=0), 'approved'] = 1
final_df = df.groupby('userid').agg({'approved': sum, 'code': max, 'retries': sum})
final_df.loc[final_df['approved']!=0, 'approved'] = 'no'
final_df.loc[final_df['approved']==0, 'approved'] = 'yes'

Here is another answer: The retries part is easy and vectorizable:
tmp = df[df['code'] != '00'].groupby(['userid', 'code']).count().reset_index(
).rename(columns={'date': 'retries'})
gives immediately:
userid code  retries
0      x   07        3
1      y   56        1
But I could not find a vectorized way for the approved part. This gives the expected result, but is quadratic:
x = pd.Series(index=tmp.index)
for ix, i in tmp.iterrows():
started = False
for j in df.loc[(df['code'].isin([i['code'], '00']))
&amp;(df['userid']==i['userid']), 'code']:
if started and (j == '00'):
x[ix] = 'yes'
break
elif j != '00':
started = True
else:
x[ix] = 'no'
tmp['approved'] = x
It gives as expected:
userid code  retries approved
0      x   07        3       no
1      y   56        1      yes

Here is another answer: Based on the example from your question, you are looking for something like the following code. It is a version avoiding for-loops.
Hope this help!
import pandas as pd
data = {'user_id': ['y', 'y', 'x', 'x', 'x', 'x'],
'date': ['2020-01-02', '2020-01-03', '2020-01-02', '2020-02-02', '2020-02-05', '2020-02-07'],
'code': [56, 0, 0, 7, 7, 7]}
df = pd.DataFrame(data=data)
def fun(data):
# get the first index that there are non-zero code
first_diff_zero = data[~(data.code == 0)].index[0]
# create a sub-dataframe
aux = data.loc[first_diff_zero:, :]
# count the number of time the code appears
count_code = aux.loc[~(aux.code == 0), :].shape[0]
# if zero appears in the sub-dataframe
approved = 'yes' if 0 in aux.code else 'no'
# return a Series with the information
return pd.Series(data=[aux.loc[first_diff_zero, 'code'],
approved,
count_code], index=['code', 'approved', 'retries'])
result = df.groupby('user_id').apply(fun)
And the result is what you are expecting...
code approved  retries
user_id
x           7       no        3
y          56      yes        1

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 5, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:04/12/20', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><pandas>', 'Title': 'dataframe transformation to summarize information about users', 'ViewCount': 50, 'id': 61172211, 'user_AboutMe': '', 'user_AccountId': 6497166.0, 'user_DisplayName': 'Thabra', 'user_Reputation': 317.0, 'user_Views': 24.0, 'user_id': 5029404.0}}