CODEWITHSUNDEEP
pythonpandas
Martin Yordanov Georgiev
Martin Yordanov Georgiev

Reputation: 477

Insert rows based on some condition, fill in data

I have the following dataframe:

data={"Date":["8/4/2020","8/4/2020","8/4/2020","8/4/2020","8/4/2020"],
      "Weight":[35,35,30,50,50],
      "Subportfolio":["Portfolio1","Portfolio2","Portfolio3","Portfolio4","Portfolio5"],
      "Portfolio Code":[1,1,1,2,2],
      "Instrument":["","","","",""],
      "Amount":["","","","",""]}
df=pd.DataFrame(data)

It looks like this:

    Date      Weight    Subportfolio    Portfolio Code   Instrument   Amount
0   8/4/2020    35      Portfolio 1           1
1   8/4/2020    35      Portfolio 2           1
2   8/4/2020    30      Portfolio 3           1
3   8/4/2020    50      Portfolio 4           2
4   8/4/2020    50      Portfolio 5           2

I want to insert a row before a new portfolio code starts. In the meantime the newly created rows should contain some data - always the same. I want to practically achieve this:

    Date      Weight    Subportfolio    Portfolio Code     Instrument    Amount
0 8/4/2020     35       Portfolio 1          1      
1 8/4/2020     35       Portfolio 2          1      
2 8/4/2020     30       Portfolio 3          1      
3 8/4/2020                                   1             Exposure      1000000
4 8/4/2020     50       Portfolio 4          2      
5 8/4/2020     50       Portfolio 5          2      
6 8/4/2020                                   2             Exposure      1000000

I have not actually tried anything as I don't know how to tackle this. I was thinking something with a groupby (by: Portfolio code) but am not sure how to proceed.

Upvotes: 0

Views: 25

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150745

One general approach is concat then sort:

(pd.concat([df,df[['Date','Portfolio Code']].drop_duplicates()
                 .assign(Instrument='Exposure',Amount=1000000)
          ])
   .sort_values('Portfolio Code', kind='mergesort')
)

Output:

       Date  Weight Subportfolio  Portfolio Code Instrument   Amount
0  8/4/2020    35.0   Portfolio1               1                    
1  8/4/2020    35.0   Portfolio2               1                    
2  8/4/2020    30.0   Portfolio3               1                    
0  8/4/2020     NaN          NaN               1   Exposure  1000000
3  8/4/2020    50.0   Portfolio4               2                    
4  8/4/2020    50.0   Portfolio5               2                    
3  8/4/2020     NaN          NaN               2   Exposure  1000000

Upvotes: 1

Related Questions