Filter a data-frame and add a new column according to the given condition
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
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')
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
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