Add Missing Dates to Pandas Dataframe

Add missing dates to pandas dataframe

You could use Series.reindex:

import pandas as pd

idx = pd.date_range('09-01-2013', '09-30-2013')

s = pd.Series({'09-02-2013': 2,
               '09-03-2013': 10,
               '09-06-2013': 5,
               '09-07-2013': 1})
s.index = pd.DatetimeIndex(s.index)

s = s.reindex(idx, fill_value=0)
print(s)

yields

2013-09-01     0
2013-09-02     2
2013-09-03    10
2013-09-04     0
2013-09-05     0
2013-09-06     5
2013-09-07     1
2013-09-08     0
...

Fill missing dates in a pandas DataFrame

You could create a date range and use "Fecha" column to set_index + reindex to add missing months. Then fillna + reset_index fetches the desired outcome:

df['Fecha'] = pd.to_datetime(df['Fecha'])
df = (df.set_index('Fecha')
      .reindex(pd.date_range('2020-01-01', '2021-12-01', freq='MS'))
      .rename_axis(['Fecha'])
      .fillna(0)
      .reset_index())

Output:

        Fecha  unidades
0  2020-01-01       2.0
1  2020-02-01       0.0
2  2020-03-01       0.0
3  2020-04-01       0.0
4  2020-05-01       0.0
5  2020-06-01       0.0
6  2020-07-01       0.0
7  2020-08-01       0.0
8  2020-09-01       4.0
9  2020-10-01      11.0
10 2020-11-01       4.0
11 2020-12-01       2.0
12 2021-01-01       0.0
13 2021-02-01       0.0
14 2021-03-01       9.0
15 2021-04-01       2.0
16 2021-05-01       1.0
17 2021-06-01       0.0
18 2021-07-01       1.0
19 2021-08-01       0.0
20 2021-09-01       0.0
21 2021-10-01       0.0
22 2021-11-01       0.0
23 2021-12-01       0.0

Dataframe: Add new rows for missing dates

You can use .reindex + .ffill():

min_date = df.index.min()
max_date = df.index.max()
date_list = pd.date_range(min_date, max_date, freq="D")

df = df.reindex(date_list).ffill()
print(df)

Prints:

              S&P500    Europe     Japan
2002-12-23  0.247683  0.245252  0.203916
2002-12-24  0.241855  0.237858  0.200971
2002-12-25  0.241855  0.237858  0.200971
2002-12-26  0.237095  0.230614  0.197621
2002-12-27  0.241104  0.250323  0.191855

---

OR: Use method= parameter

df = df.reindex(date_list, method="ffill")

How to add missing dates in pandas

Use DataFrame.reindex, working also if need some custom start and end datimes:

df = df.reindex(pd.date_range(start, end, freq ='D'))

Or DataFrame.asfreq for add missing datetimes between existing data:

df = df.asfreq('d')

Pandas: Add missing dates up to current date

Use DataFrame.reindex with unique codes and date_range for all combinations of pairs dates and codes:

df['Date'] = pd.to_datetime(df['Date'])

idx = pd.date_range('01-01-2021', pd.to_datetime('now').normalize())
codes = df['Code'].unique()

df1 = (df.set_index(['Date','Code'])
         .reindex(pd.MultiIndex.from_product([idx, codes], names=['Date','Code']))
         .reset_index())
print (df1)
          Date Code  Count_1  Count_2
0   2021-01-01    A      5.0      4.0
1   2021-01-01    B      NaN      NaN
2   2021-01-02    A      NaN      NaN
3   2021-01-02    B      NaN      NaN
4   2021-01-03    A      NaN      NaN
..         ...  ...      ...      ...
345 2021-06-22    B      NaN      NaN
346 2021-06-23    A      NaN      NaN
347 2021-06-23    B      NaN      NaN
348 2021-06-24    A      NaN      NaN
349 2021-06-24    B      NaN      NaN

[350 rows x 4 columns]

Adding missing dates to a time series from a csv file

I load using:

df = pd.read_csv(io.StringIO('''date        GWL
15/01/2001  9.73
15/08/2001  10.55
15/11/2001  11.65
15/01/2002  9.72
15/04/2002  9.92'''), sep='\s{2,}', engine='python', parse_dates=['date'])

What you need to do in your code is just pass the parse_dates=['date'] parameter to your pd.read_csv. Don't pass the other stuff. I need to use io.StringIO because you won't provide your data in a constructor format.

This yields:

        date    GWL
0 2001-01-15   9.73
1 2001-08-15  10.55
2 2001-11-15  11.65
3 2002-01-15   9.72
4 2002-04-15   9.92

Construct an Ides-centred monthly date range:

months = df['date'] - pd.offsets.MonthBegin()
d_range = pd.date_range(months.min(), months.max(), freq='M')
d_range = d_range - pd.offsets.MonthBegin() + pd.offsets.Day(14)

Reindex:

>>> df.set_index('date').reindex(d_range)
              GWL
2001-01-15   9.73
2001-02-15    NaN
2001-03-15    NaN
2001-04-15    NaN
2001-05-15    NaN
2001-06-15    NaN
2001-07-15    NaN
2001-08-15  10.55
2001-09-15    NaN
2001-10-15    NaN
2001-11-15  11.65
2001-12-15    NaN
2002-01-15   9.72
2002-02-15    NaN
2002-03-15    NaN

Including the missing dates in the date column of pandas dataframe for a specific timespan

Set Date as index and reindex it with df_date:

df_date = pd.date_range(start='1/1/2019', end='11/1/2020', freq='MS')
df = df.set_index('Date').reindex(df_date)

Output:

>>> df
                 Value
2019-01-01         NaN
2019-02-01         NaN
2019-03-01         NaN
2019-04-01         NaN
2019-05-01         NaN
2019-06-01         NaN
2019-07-01         NaN
2019-08-01         NaN
2019-09-01         NaN
2019-10-01  46486868.0
2019-11-01  36092742.0
2019-12-01  32839185.0
2020-01-01         NaN
2020-02-01         NaN
2020-03-01         NaN
2020-04-01         NaN
2020-05-01         NaN
2020-06-01         NaN
2020-07-01         NaN
2020-08-01         NaN
2020-09-01         NaN
2020-10-01         NaN
2020-11-01         NaN

---

Related Topics