Python Resample: How do I keep NaN as NaN?

Question

When resample from monthly data to quarterly, I want my last value NaN to remain as NaN. How should I tweak my code?

Thank you

     HS6P1

Jan1989 69.9
Feb1989 59.3
Mar1989 83.5
Apr1989 100.4
May1989 101.4
Jun1989 100.3
Jul1989 98
Aug1989 91.7
Sep1989 82.4
Oct1989 91.3
Nov1989 72.6
Dec1989 NaN

df=pd.read_excel(input_file, sheet_name='Sheet1', usecols='A:D', na_values='ND', index_col=0, header=0)
df.index.names = ['Period']
df.index = pd.to_datetime(df.index)


q0= pd.Series(df['HS6P1'], index=df.index)

m1 = q0.resample('Q').sum()

Current Output
Period
1989-03-31 212.7
1989-06-30 302.1
1989-09-30 272.1
1989-12-31 163.9

Desired Output
Period
1989-03-31 212.7
1989-06-30 302.1
1989-09-30 272.1
1989-12-31 NaN

why not post the actual dataframe data, not a screenshot. – inquirer Dec 07 '22 at 10:47 — inquirer, Dec 07 '22 at 10:47
@inquirer: Posted the actual data. Pls assist. Thanks! – 123456 Dec 07 '22 at 23:54 — 123456, Dec 07 '22 at 23:54

inquirer · Accepted Answer · 2022-12-08T10:45:29.880

You can try like this. But if you have NaNs elsewhere, then the sums in that part will be NaNs. Here is information on this topic. np.nan + 1, then the output will be nan. nan turns everything it touches into nan.

res = q0.resample('Q').apply(lambda x: np.sum(x.values))

And another option. I don't know if it will fit? Used the min_count=3 parameter. Theoretically, there are three values in a quarter, if some values are missing, then there will be NaN.

m1 = q0.resample('Q').sum(min_count=3)

If you need to return NaN exactly in the last quarter, if there is at least one empty value there.

def my_func(x):
    return [x.sum(), np.isnan(x).any()]

qqq = q0.resample('Q').apply(my_func)

if qqq[-1][1] == True:
    qqq[-1][0] = np.nan

qqq = pd.Series(qqq.str[0], index=qqq.index)

Python Resample: How do I keep NaN as NaN?

1 Answers1