Pandas drop nan in a specific row ('Feb-29') and shift remaining rows up

user_2574074 0
Viewed 117

I have a pandas dataframe containing several years of timeseries data as columns. Each starts in November and ends in the subsequent year. I'm trying to deal with NaN's in non-leap years. The structure can be recreated with something like this:

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

ndays = 151
sdates = [(datetime(2019,11,1) + timedelta(days=x)).strftime("%b-%d") for x in range(ndays)]
columns=list(range(2016,2021))
df = pd.DataFrame(np.random.randint(0,100,size=(ndays, len(columns))), index=sdates, columns=columns)
df.loc["Feb-29",2017:2019] = np.nan
df.loc["Feb-28":"Mar-01"]

Out[61]: 
        2016  2017  2018  2019  2020
Feb-28    36  59.0  74.0  19.0    24
Feb-29    85   NaN   NaN   NaN     6
Mar-01    24  75.0  49.0  99.0    82

What I want to do is remove the "Feb-29" NaN data only (in the non-leap years) and then shift teh data in those columns up a row, leaving the leap-years as-is. Something like this, with Mar-01 and subsequent rows shifted up for 2017 through 2019:

        2016  2017  2018  2019  2020
Feb-28    36  59.0  74.0  19.0    24
Feb-29    85  75.0  49.0  99.0     6
Mar-01    24  42.0  21.0  41.0    82

I don't care that "Mar-01" data will be labelled as "Feb-29" as eventually I'll be replacing the string date index with an integer index.

Note that I didn't include this in the example but I have NaN's at the start and end of the dataframe in varying rows that I do not want to remove (i.e., I can't just remove all NaN data, I need to target "Feb-29" specifically)

pandas dataframe nan numpy python
3 Answers

It sounds like you don't actually want to shift dates up, but rather number them correctly based on the day of the year? If so, this will work:

First, make the DataFrame long instead of wide:

df = pd.DataFrame(
    {
        "2016": {"Feb-28": 36, "Feb-29": 85, "Mar-01": 24},
        "2017": {"Feb-28": 59.0, "Feb-29": None, "Mar-01": 75.0},
        "2018": {"Feb-28": 74.0, "Feb-29": None, "Mar-01": 49.0},
        "2019": {"Feb-28": 19.0, "Feb-29": None, "Mar-01": 99.0},
        "2020": {"Feb-28": 24, "Feb-29": 6, "Mar-01": 82},
    }
)


df = (
    df.melt(ignore_index=False, var_name="year", value_name="value")
    .reset_index()
    .rename(columns={"index": "month-day"})
)
df
   month-day  year  value
0     Feb-28  2016   36.0
1     Feb-29  2016   85.0
2     Mar-01  2016   24.0
3     Feb-28  2017   59.0
4     Feb-29  2017    NaN
5     Mar-01  2017   75.0
6     Feb-28  2018   74.0
7     Feb-29  2018    NaN
8     Mar-01  2018   49.0
9     Feb-28  2019   19.0
10    Feb-29  2019    NaN
11    Mar-01  2019   99.0
12    Feb-28  2020   24.0
13    Feb-29  2020    6.0
14    Mar-01  2020   82.0

Then remove rows containing an invalid date and get the day of the year for remaining days:

df["date"] = pd.to_datetime(
    df.apply(lambda row: " ".join(row[["year", "month-day"]]), axis=1), errors="coerce",
)
df = df[df["date"].notna()]
df["day_of_year"] = df["date"].dt.dayofyear
df
   month-day  year  value       date  day_of_year
0     Feb-28  2016   36.0 2016-02-28           59
1     Feb-29  2016   85.0 2016-02-29           60
2     Mar-01  2016   24.0 2016-03-01           61
3     Feb-28  2017   59.0 2017-02-28           59
5     Mar-01  2017   75.0 2017-03-01           60
6     Feb-28  2018   74.0 2018-02-28           59
8     Mar-01  2018   49.0 2018-03-01           60
9     Feb-28  2019   19.0 2019-02-28           59
11    Mar-01  2019   99.0 2019-03-01           60
12    Feb-28  2020   24.0 2020-02-28           59
13    Feb-29  2020    6.0 2020-02-29           60
14    Mar-01  2020   82.0 2020-03-01           61
user_54964330

I think this would do the trick?

is_leap = ~np.isnan(df.loc["Feb-29"])
leap = df.loc[:, is_leap]
nonleap = df.loc[:, ~is_leap]
nonleap = nonleap.drop(index="Feb-29")
df2 = pd.concat([
    leap.reset_index(drop=True),
    nonleap.reset_index(drop=True)
], axis=1).sort_index(axis=1)
user_119223950

This should move all the nulls to the end by sorting on pd.isnull, which I believe it what you want:

df = df.apply(lambda x: sorted(x, key=pd.isnull),axis=0)

Before:

        2016  2017  2018  2019  2020
Feb-28    35  85.0  88.0  46.0    19
Feb-29    41   NaN   NaN   NaN    52
Mar-01    86  92.0  29.0  44.0    36

After:

        2016  2017  2018  2019  2020
Feb-28    35  85.0  88.0  46.0    19
Feb-29    41  92.0  29.0  44.0    52
Mar-01    86  32.0  50.0  27.0    36
user_47183500
Related