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.
You can create a conditional column in pandas DataFrame by using np. where() , np. select() , DataFrame. map() , DataFrame.
You can extract a column of pandas DataFrame based on another value by using the DataFrame. query() method. The query() is used to query the columns of a DataFrame with a boolean expression.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With