Retain function and creation a of new table in python panda as SAS

Question

Recently, I am converting from SAS to Python pandas. One question I have is that does pandas have a retain like function in SAS.

My SAS code is:

 data df1;
retain col3 "    ";
set df;
by ID  ;
if first.ID  then col3=col1;
else   col3=col3;

the other condition i have is in SAS code is:

 data df1;
retain col3;
set df;
by ID  ;
if first.ID  then col3=1;
else   col3=col3+1;

Next SAS code which i am trying to convert is shown below:

   proc sql;
   create table t1 as
     select 
      c1, c2, c3, c4, c5, flag, max(flag) as MAX_flag
   from t1
    group by c1, c2, c3,  c5;
  run;

I tried it in pandas as below but seems i am making some silly mistakes. If anyone know how to replicate above sas code block3 into pandas

  t1=t1[['c1','c2','c3','c4','c5','c6']]
  t1.loc[:,'Max_flag']=t1['flag'].max()
  t1.groupby(['c1','c2','c3','c5'])

In my eg. col3 is B and col1 is a. The condition on which it should be done is df.groupby(['ID'],as_index=False).first()

I have 2 columns ID, A. my requirement is Input data df1:

 ID    A
  1    a
  1    b
  2    c
  1    p
  2    q

Output dataframe should have one more column name as B. It will group by ID.first(). and copy the data of col A to col B for all the grouped ID.

Output should be df1

 ID    A    B
  1    a    a
  1    b    a
  2    c    c
  1    p    a
  2    q    c

 **My key requirement is to convert above SAS code to Pnadas**

Answer 1

Consider a merge with groupby.first():

df = df.merge(df.groupby('ID').first().reset_index(), on='ID').rename(columns={'A_x':'A', 'A_y':'B'})

#    ID  A  B
# 0   1  a  a
# 1   1  b  a
# 2   1  p  a
# 3   2  c  c
# 4   2  q  c

This can also be generalized with groupby.nth() which is zero based. Below takes the second value in each ID:

df = df.merge(df.groupby('ID').nth(1).reset_index(), on='ID').rename(columns={'A_x':'A', 'A_y':'B'})

#    ID  A  B
# 0   1  a  b
# 1   1  b  b
# 2   1  p  b
# 3   2  c  q
# 4   2  q  q

And as well as groupby.last():

df = df.merge(df.groupby('ID').last().reset_index(), on='ID').rename(columns={'A_x':'A', 'A_y':'B'})

#    ID  A  B
# 0   1  a  p
# 1   1  b  p
# 2   1  p  p
# 3   2  c  q
# 4   2  q  q

Answer 2

Form what you have tried, looks like you want to have 'col4' equal to 'col2' if 'col1' is not blank, otherwise 'col4' should be blank. So you do not need group-by. Here is how you do the case the case I explained here:

In [80]: df = pd.DataFrame([[1,'a','b','c'],[2,'e','r','g'], [3,'BLANK', '', '']], columns=['ID','col1', 'col2','col3'])

In [81]: df
Out[81]: 
   ID   col1 col2 col3
0   1      a    b    c
1   2      e    r    g
2   3  BLANK          

In [82]: df['col4'] = np.where(df['col1'] == 'BLANK', 'BLANK', df['col2'])

In [83]: df
Out[83]: 
   ID   col1 col2 col3   col4
0   1      a    b    c      b
1   2      e    r    g      r
2   3  BLANK            BLANK