-1

I have a dataframe as shown below

Using pandas dataframe I want to replace empty values in a column from first row in a groupby condition based on previous month last value

till last date exists based on each ID Sector Usage, price column value should be filled.

ID    Sector    Usage     Price   Date 
1     A         R         20      29/08/2022
1     A         R         30      30/08/2022
1     A         R         40      31/08/2022
1     A         R                 01/09/2022
1     A         R                 02/09/2022
.     .         .          .          . 
.     .         .          .          . 
1     A         R                 30/09/2022
.     .         .          .          . 
.     .         .          .          .
1     A         R                 31/10/2022
.     .         .          .          . 
.     .         .          .          .
1     A         R                 30/11/2022
2     B         C         200     31/08/2022
3     B         R         60      31/08/2022

expected_output

ID    Sector    Usage     Price    Date
   
1     A         R         20      29/08/2022
1     A         R         30      30/08/2022
1     A         R         40      31/08/2022
1     A         R                 01/09/2022
1     A         R                 02/09/2022
.     .         .          .          . 
.     .         .          .          . 
1     A         R          40       30/09/2022
.     .         .          .          . 
.     .         .          .          .
1     A         R          40       31/10/2022
.     .         .          .          . 
.     .         .          .          .
1     A         R          40      30/11/2022
2     B         C          200      31/08/2022
2     B         C          200      01/09/2022
.     .         .          .          . 
.     .         .          .          . 
2     B         C          200      31/10/2022
.     .         .          .          . 
.     .         .          .          . 
2     B         C          200      31/12/2022
3     B         R          60       31/08/2022

I have tried below codes but not working

m = df['Price'] == ''
s = df.assign(Price=df['Price'].mask(m)).groupby(['Sector','Usage'])['Price'].ffill()
df['Price'] = np.where(m, s, df['Price']).astype(int)

or

df.replace({'Price': {0:np.NaN}}).ffill()
Rabinzel
  • 7,757
  • 3
  • 10
  • 30
s nandan
  • 83
  • 7
  • Please try to explain a bit more in detail what your goal is. Also, why are there still empty cells in the price column of the expected output? – Rabinzel Sep 02 '22 at 03:21
  • @Rabinzel: actually logic is upto current_month am getting values but future 11months dates will be there so i need to fill those dates with last value of the current_month – s nandan Sep 02 '22 at 03:26
  • ok. Sure, for you it's pretty obvious, but imagine not knowing what's the data about or what it is for, it is pretty hard to understand what you are searching for (maybe it is only me but I can't follow the logic looking at your data and the expected output). My advice would be, try to edit your question and explain a bit more than just one sentence what your goal is, for example explain in words why "in row xy for date ab we fill in this value cd because...." Another thing, why is the expected output data longer than input ? where are the rows coming from? – Rabinzel Sep 02 '22 at 03:32

1 Answers1

0

Assuming your empty values are empty strings:

import pandas as pd
import numpy as np

df = pd.DataFrame.from_dict({"fills": [100, 200, "", 40, "", 5]})

df["fills"].replace("", np.nan, regex=True).fillna(method="ffill")

Output:

  fills
0   100
1   200
2      
3    40
4      
5     5

   fills
0    100.0
1    200.0
2    200.0
3     40.0
4     40.0
5      5.0

And with groupby / transform:

df = pd.DataFrame.from_dict({"fills": [100, 200, "", 40, "", 5], "grps": ["A", "B", "C", "A", "A", "B"]})

df["fills"] = df.groupby(by=["grps"])["fills"].transform(lambda x: x.replace("", np.nan)).fillna(method="ffill")

Output:

  fills grps
0   100    A
1   200    B
2          C
3    40    A
4          A
5     5    B
    
   fills grps
0  100.0    A
1  200.0    B
2  200.0    C
3   40.0    A
4   40.0    A
5    5.0    B
ivanp
  • 340
  • 1
  • 5
  • actually it should fill automatically – s nandan Sep 02 '22 at 04:04
  • it does. if you don't think so give a better explanation with more words. Regarding your original question, please see detail on how to ask better pandas questions here: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples – ivanp Sep 02 '22 at 04:22
  • I voted to close this question, since this question can't be answered as it is. There is no [MRE](https://stackoverflow.com/help/minimal-reproducible-example) and also not enough focus to explain the problem in detail. See his last question, it was the same back then. – Rabinzel Sep 02 '22 at 04:29
  • @Rabinzel : why did you given -1 , actually above ivanp has given my answer rather than you – s nandan Sep 02 '22 at 04:32
  • it will affect my questions in future if you are not aware of question , than please leave – s nandan Sep 02 '22 at 04:32
  • and as you see, the answer is clearly not what you want. Because he can't know what you want. – Rabinzel Sep 02 '22 at 04:33
  • What you missunderstand, I would like to help, I asked kindly to make your question more clear but since you show no effort at all, I voted the question down since it can't be answered like this. [How to ask](https://stackoverflow.com/help/how-to-ask) and [MRE](https://stackoverflow.com/help/minimal-reproducible-example). Please have a look at these links and try to act accordingly to the guidelines of this site....or just get no help at all. your choice. – Rabinzel Sep 02 '22 at 04:35