Mdthbs

I have a data frame like this

ID      col1           col2 

1    Abc street       2017-07-27 

1    None             2017-08-17 

1    Def street       2018-07-15 

1    None             2018-08-13 

2    fbg street       2018-01-07 

2    None             2018-08-12 

2    trf street       2019-01-15 

I want to filter all the 'None' from col1 and add the corresponding col2 value into a new column col3. My output look like this

ID      col1           col2              col3 

1    Abc street       2017-07-27     2017-08-17          

1    Def street       2018-07-15     2018-08-13             

2    fbg street       2018-01-07     2018-08-12             

2    trf street       2019-01-15     

Can anyone help me to achieve this.

Today's edition of Over Engineered with Numpy

Though admittedly very little obvious Numpy

i, rows = pd.factorize([*zip(df.ID, df.col1.replace('None'))])

k, cols = pd.factorize(df.groupby(i).cumcount())



dleft = pd.DataFrame(dict(zip(['ID', 'col1'], zip(*rows))))

drigt = pd.DataFrame(index=dleft.index, columns=np.arange(len(cols)) + 2).add_prefix('col')

drigt.values[i, k] = df.col2.values



dleft.join(drigt)



   ID        col1        col2        col3

0   1  Abc street  2017-07-27  2017-08-17

1   1  Def street  2018-07-15  2018-08-13

2   2  fbg street  2018-01-07  2018-08-12

3   2  trf street  2019-01-15         NaN

Using ffill + pivot_table. This assumes that None follows the proper value, which it appears to from your data.

u = df.assign(col1=df.col1.replace('None'))

g = ['ID', 'col1']

idx = u.groupby(g).cumcount()



(u.assign(idx=idx)

    .pivot_table(index=g, columns='idx', values='col2', aggfunc='first')

    .reset_index())  

idx   ID        col1           0           1

0      1  Abc street  2017-07-27  2017-08-17

1      1  Def street  2018-07-15  2018-08-13

2      2  fbg street  2018-01-07  2018-08-12

3      2  trf street  2019-01-15         NaN

I am using cumcount with merge

df1=df.loc[df.col1.ne('None'),:].copy()

df2=df.loc[df.col1.eq('None'),:].copy()

df1['Key']=df1.groupby('ID').cumcount()

df2['Key']=df2.groupby('ID').cumcount()

df1.merge(df2.drop('col1',1),on=['ID','Key'],how='left')

Out[816]: 

   ID       col1      col2_x  Key      col2_y

0   1  Abcstreet  2017-07-27    0  2017-08-17

1   1  Defstreet  2018-07-15    1  2018-08-13

2   2  fbgstreet  2018-01-07    0  2018-08-12

3   2  trfstreet  2019-01-15    1         NaN

Try:

filters = df['col1'].isna()

s = df.loc[filters, 'col2'].copy()

df = df[~filters]

df['col3'] = s.values

Edit: as you mentioned, the filter you want is 'None', not None, then:

filters = df['col1'].eq('None')

Yet another attempt:

f=df['col1']=='None'

c3=df.loc[f].col2.reset_index(drop=True)

df=df[~f]

df2=pd.concat([df.reset_index(drop=True),c3], axis=1, ignore_index=True)

df2.columns=['ID', 'col1', 'col2', 'col3']

   ID        col1        col2        col3

0   1  Abc street  2017-07-27  2017-08-17

1   1  Def street  2018-07-15  2018-08-13

2   2  fbg street  2018-01-07  2018-08-12

3   2  trf street  2019-01-15         NaN