Creating a row based on conditions in pandas dataframe

Question

I have a pandas dataframe as below:

import pandas as pd
df = pd.DataFrame({'group':['A','A','A', 'B', 'B'],'date1':['12/1/2019','12/1/2019','12/1/2019', '12/1/2022', '12/1/2021'], 'nb_months':[11,11,12, 23, 15], 'col1':[1,1,2, 3, 5]})
df['date1'] = pd.to_datetime(df['date1'], format='%m/%d/%Y', errors='coerce').dropna()
df


   group    date1      nb_months    col1
0   A       2019-12-01  11          1
1   A       2019-12-01  11          1
2   A       2019-12-01  12          2
3   B       2022-12-01  23          3
4   B       2021-12-01  15          5

I want to perform below operation,

My expected output

   group    date1       nb_months   col1  date2
0   A                                     2019-11-01
1   A       2019-12-01   11         1     2019-12-01
2   A       2019-12-01   11         1     2019-12-01
3   A       2019-12-01   12         2     2019-12-01
4   B                                     2022-11-01
5   B       2022-12-01   23         3     2022-12-01
6   B       2021-12-01   15         5     2021-12-01

jezrael · Accepted Answer · 2020-08-10 13:57:11Z

1

Use DataFrame.drop_duplicates for duplicated rows, add column with subtract 1 month, add to original by concat and last sorting with reindex for original order of columns:

df1 = (df.drop_duplicates('group')
         .assign(date2 = lambda x: x['date1'] - pd.offsets.DateOffset(months=1)))

df = (pd.concat([df1[['group', 'date2']],  
                 df.assign(date2 = lambda x: x['date1'])], sort=False)
       .sort_values('group')
       .reindex(columns=df.columns.tolist() + ['date2'])
       .reset_index(drop=True))
print (df)
  group      date1  nb_months  col1      date2
0     A        NaT        NaN   NaN 2019-11-01
1     A 2019-12-01       11.0   1.0 2019-12-01
2     A 2019-12-01       11.0   1.0 2019-12-01
3     A 2019-12-01       12.0   2.0 2019-12-01
4     B        NaT        NaN   NaN 2022-11-01
5     B 2022-12-01       23.0   3.0 2022-12-01
6     B 2021-12-01       15.0   5.0 2021-12-01

edited Aug 10, 2020 at 13:57

answered Aug 10, 2020 at 13:51

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a row based on conditions in pandas dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related