Etydhv

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