CODEWITHSUNDEEP
pythonpandas
toothsie
toothsie

Reputation: 255

Pandas: Is there anyway to fill Nan's in a column with continuous dates

I have a dataframe like so: 

  Date         Day_number
   10/03/2020     1
   11/03/2020     2
   12/03/2020     3
   13/03/2020     4
    Nan           5
    Nan           6
    Nan           7

I want to replace the Nans with a continuation of the dates. There around 100 rows in the dataset.

So I want the df to look like this. 

  Date         Day_number
   10/03/2020     1
   11/03/2020     2
   12/03/2020     3
   13/03/2020     4
   14/03/2020     5
   15/03/2020     6
   16/03/2020     7
      .           .
      .           .

Any help would be greatly appreciated. Thanks in advance.

Upvotes: 0

Views: 113

Answers (2)

Rola
Rola

Reputation: 1936

import pandas as pd
from datetime import datetime, timedelta

data = {'Date':  ['10/03/2020', '11/03/2020','12/03/2020','13/03/2020',np.nan,np.nan],
    'Day_number': [1, 2,3,4,5,6]
    }

df = pd.DataFrame (data, columns = ['Date','Day_number'])
nextDate=datetime.now()
for index,row in df.iterrows():
    date = nextDate
    if not pd.isnull(row['Date']):
        date = datetime.strptime(row['Date'], '%d/%m/%Y').date()
        nextDate = date + timedelta(days=1)
    else :
        df.at[index,'Date']=date.strftime('%d/%m/%Y')
        nextDate = date + timedelta(days=1)
df

And the output should be as per below:

        Date    Day_number
0   10/03/2020  1
1   11/03/2020  2
2   12/03/2020  3
3   13/03/2020  4
4   14/03/2020  5
5   15/03/2020  6

Upvotes: 0

BENY
BENY

Reputation: 323226

IIUC

df.Date=pd.to_datetime(df.Date,errors='coerce',dayfirst=True)
s=df.Date.isnull()
df.loc[s,'Date']=pd.to_timedelta(df.Day_number.diff()[s].cumsum(),unit='day')+df['Date'].ffill()
df
        Date  Day_number
0 2020-03-10           1
1 2020-03-11           2
2 2020-03-12           3
3 2020-03-13           4
4 2020-03-14           5
5 2020-03-15           6
6 2020-03-16           7

Upvotes: 3

Related Questions