4
$\begingroup$

Updated 22 Oct. 2018: I have the following dataset:

data = [('D',1,10,8),
           ('D',2,12,12),
           ('X',1,28,np.NaN),
           ('D',3,np.NaN,np.NaN),
           ('X',2,np.NaN,25),
           ('X',3,32,25),
           ('T',1,220,np.NaN),
           ('X',4,30,np.NaN),
           ('T',2,240,np.NaN),
           ('X',2,38,np.NaN),
           ('T',3,np.NaN,np.NaN),
           ('T',4,200,150)]

labels = ['item', 'month','normal_price','final_price']

df = pd.DataFrame.from_records(data, columns=labels)

    item    month   normal_price    final_price
0   D       1       10.0            8.0
1   D       2       12.0            12.0
2   X       1       28.0            NaN
3   D       3       NaN             NaN
4   X       2       NaN             25.0
5   X       3       32.0            25.0
6   T       1       220.0           NaN
7   X       4       30.0            NaN
8   T       2       240.0           NaN
9   X       2       38.0            NaN
10  T       3       NaN             NaN
11  T       4       200.0           150.0

I want to fill NaN in the 'normal_price', 'final_price' columns for each item with the 'normal_price', 'final_price' of its preceding month (if not available by its succeeding month). I have tried using this:

df[['normal_price','final_price']]=df[['normal_price','final_price']].fillna(method='ffill')

but it gives me this:

    item    month   normal_price    final_price
0   D       1   10.0                8.0
1   D       2   12.0                12.0
2   X       1   28.0                12.0*
3   D       3   28.0*               12.0
4   X       2   28.0                25.0
5   X       3   32.0                25.0
6   T       1   220.0               25.0*
7   X       4   30.0                25.0
8   T       2   240.0               25.0*
9   X       2   38.0                25.0
10  T       3   38.0*               25.0*
11  T       4   200.0               150.0

The problem is with cases with an asterisk (I've also tried 'bfill'). These values should be filled with based on their correct items. Ideally, I should get:

    item    month   normal_price    final_price
0   D       1   10.0                8.0
1   D       2   12.0                12.0
2   X       1   28.0                25.0
3   D       3   12.0                12.0
4   X       2   28.0                25.0
5   X       3   32.0                25.0
6   T       1   220.0               150.0
7   X       4   30.0                25.0
8   T       2   240.0               150.0
9   X       2   38.0                25.0
10  T       3   220.0               150.0
11  T       4   200.0               150.0

I have also tried the followings (from answers offered by date):

df[['normal_price','final_price']].ffill(limit=1).bfill(limit=1)

or

df[['normal_price','final_price']]=df[['normal_price','final_price']].interpolate(method='nearest')

But none of them are giving me reasonable fillna corresponding to each item. I have found this method:

df[['normal_price','final_price']]=df[['normal_price','final_price']].fillna(df.groupby(['item'])[['normal_price','final_price']].transform('mean'))

It works better, BUT it introduces unpredictable values (in this case the 'mean') for NaN values, not with the preceding or following values as I originally wanted. I am trying to combined the df.groupby(['item']) concept with '.ffill' or '.bfill', but so far no success.

Improve this question
$\endgroup$

2 Answers 2

Reset to default
3
$\begingroup$

Simply using the fillna method and provide a limit on how many NA values should be filled. You only want the first value to be filled, soset that it to 1:

df.ffill(limit=1)                                                       

  item  month  normal_price  final_price
0    1      1          10.0          8.0
1    1      2          12.0         12.0
2    1      3          12.0         12.0
3    2      1           NaN         25.0
4    2      2          30.0         25.0
5    3      3          30.0          NaN
6    3      4         200.0        150.0

You can chain together the above with a bfill to then fill the remaining NaN values:

df.ffill(limit=1).bfill(limit=1)

  item  month  normal_price  final_price
0    1      1          10.0          8.0
1    1      2          12.0         12.0
2    1      3          12.0         12.0
3    2      1          30.0         25.0
4    2      2          30.0         25.0
5    3      3          30.0        150.0
6    3      4         200.0        150.0

This would only not be optimal if there are column in your dataframe which you would like to leave unaffected.

In that case you can do them one column at a time - i use the in_place flag so that we do not need to do any of the ugly re-assignments:

df.final_price.ffill(inplace=True, limit=1)                                     

df                                                                     

  item  month  normal_price  final_price
0    1      1          10.0          8.0
1    1      2          12.0         12.0
2    1      3           NaN         12.0
3    2      1           NaN         25.0
4    2      2          30.0         25.0
5    3      3           NaN          NaN
6    3      4         200.0        150.0

The same idea will work the backward-filling the values, using the bfill method instead of the ffill, as I have done above.

Improve this answer
$\endgroup$
5
  • $\begingroup$ Thanks a lot for your time. I have tried this solution, but it was giving weird filled values for certain items. I wanted the 'normal_price','final_price' columns for each item filled with its its preceding or succeeding month. I found the following solution, filling NaN with the mean of 'normal_price',and 'final_price' for each item: df[['normal_price','final_price']].fillna(df.groupby(['item'])[['normal_price','final_price']].transform('mean')) I think if I combine your solution combined with groupby over items it would work just fine too. $\endgroup$
    – TwinPenguins
    Commented Oct 18, 2018 at 14:10
  • $\begingroup$ @MajidMortazavi - Your suggested solution will not fill values with the preceding or following values. It might work in your small example table with few items, but that is just coincidence. My solution wouldn't give weird values, but completely predictable ones! If you edit your post to include these cases, maybe I can help you. $\endgroup$
    – n1k31t4
    Commented Oct 18, 2018 at 14:59
  • $\begingroup$ Fair enough. But it is really hard to demonstrate my actual use case here in a simple example. I will do my best to come up with that use case. I agree the solution I just posted is not actually fill NaN with predictable ones (rather mean), but that is why I was saying at the end that perhaps combining your suggestion with groupby will do what I want. Will keep you posted. $\endgroup$
    – TwinPenguins
    Commented Oct 18, 2018 at 20:36
  • $\begingroup$ I redefined the question, and also tried the offered solutions but none seems to work. You may see what I did I mean when I said it gives me weird values using simple ffill/bfill! The filled values should correspond to specific item in preceding or following month. Thanks a lot for your time. If you have any idea to share, very much appreciated. $\endgroup$
    – TwinPenguins
    Commented Oct 22, 2018 at 9:32
  • $\begingroup$ I think I found a solution offered in stackoverflow over 2 years ago: df.groupby(['id'], as_index=False).apply(lambda group: group.ffill()), I guess I will post that as an answer. $\endgroup$
    – TwinPenguins
    Commented Oct 22, 2018 at 12:40
1
$\begingroup$

You can use pandas interpolate function.

df[['normal_price','final_price']]=df[['normal_price','final_price']].interpolate(method='nearest')
Improve this answer
$\endgroup$
1
  • $\begingroup$ Thanks a lot for your time. I have tried this solution, but it was giving really weird filled values for certain items. For an alternative solution see my comments on the other answer. $\endgroup$
    – TwinPenguins
    Commented Oct 18, 2018 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.