Add missing dates with multiple rows per date to dataframe

Question

Rephrased Question:

I am actually extracting data from several excel files monitoring my daily calorie intake. I managed to use list comprehension to generate the dates.

My Input:


        Date        Time         calories   duration
# 0     22/5/2021   Morning      420        50
# 1     22/5/2021   Afternoon    380        40
# 2     24/5/2021   Morning      390        45
# 3     24/5/2021   Afternoon    400        50
# 4     26/5/2021   Morning      350        45
# 5     26/5/2021   Afternoon    280        50
# 6     27/5/2021   Morning      300        44
# 7     27/5/2021   Afternoon    430        58

The output should look something like this:

         Date       Time calories duration
0   22/5/2021    Morning      420       50
1   22/5/2021  Afternoon      380       40
2   23/5/2021    Morning      Nan      Nan
3   23/5/2021  Afternoon      Nan      Nan
4   24/5/2021    Morning      390       45
5   24/5/2021  Afternoon      400       50
6   25/5/2021    Morning      Nan      Nan
7   25/5/2021  Afternoon      Nan      Nan
8   26/5/2021    Morning      350       45
9   26/5/2021  Afternoon      280       50
10  27/5/2021    Morning      300       44
11  27/5/2021  Afternoon      430       58

Please read this and update your question: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — Be Chiller Too, May 28 '21 at 16:12
It's impossible to know the missing dates with your input dataframe neither the start and the end dates. — Corralien, May 28 '21 at 16:23
Hi sorry, I have updated my Dataframe! This should be the input dataframe! :) — helloitsme, May 28 '21 at 18:30

Corralien · Answer 1 · 2021-05-28T19:26:52.653

2

Build 2 DatetimeIndex: one from the first and the last date of your original dataframe (full index) and the other from the existing Date / Time columns (sparse index). Finally, you can merge two dataframes and keep data from calories and duration column.

# full index from first and last dates
dti = pd.date_range(df["Date"].min(),
                    df["Date"].max() + pd.DateOffset(hours=12),
                    freq="12H")

# new dataframe with the full index
df1 = pd.DataFrame({"Date": dti.date,
                    "Time": dti.map(lambda dt: "Afternoon" if dt.hour == 12 else "Morning")},
                    index=dti)

# set index from existing Date / Time columns
df2 = df.set_index(pd.to_datetime(df["Date"].astype(str) + " " + df["Time"] 
                     .replace({"Morning": "00:00:00", "Afternoon": "12:00:00"})))

# merge dataframes and keep data
out = df1.join(df2[["calories", "duration"]]).reset_index(drop=True)

>>> out
          Date       Time  calories  duration
0   2021-05-22    Morning     420.0      50.0
1   2021-05-22  Afternoon     380.0      40.0
2   2021-05-23    Morning       NaN       NaN
3   2021-05-23  Afternoon       NaN       NaN
4   2021-05-24    Morning     390.0      45.0
5   2021-05-24  Afternoon     400.0      50.0
6   2021-05-25    Morning       NaN       NaN
7   2021-05-25  Afternoon       NaN       NaN
8   2021-05-26    Morning     350.0      45.0
9   2021-05-26  Afternoon     280.0      50.0
10  2021-05-27    Morning     300.0      44.0
11  2021-05-27  Afternoon     430.0      58.0

edited May 28 '21 at 19:26

answered May 28 '21 at 19:13

Corralien

109,409
8
28
52

Hi Corralien, I used np.repeat to repeat the dates 2 times and now my column title is '0'. How do I change it to 'Date'? pd.DataFrame(np.repeat(dates.values,2,axis=0) – helloitsme May 29 '21 at 05:08
I have updated my question! Please help :D – helloitsme May 29 '21 at 05:39
@Leon, you can't change your question like that! Our answers is now off-topic. Please reopen a question (I have a solution). Now the question is closed so nobody will read the answers. – Corralien May 29 '21 at 06:16
Ok ill do that!! – helloitsme May 29 '21 at 06:21
Post a comment here with the link to the new answer. – Corralien May 29 '21 at 06:24
https://stackoverflow.com/questions/67748409/fill-other-columns-with-missing-dates-nan-pandas-datafrane – helloitsme May 29 '21 at 06:26
I answered to your new question. – Corralien May 29 '21 at 06:51

score 2 · Answer 2 · answered May 28 '21 at 19:50

A solution using .stack() and .unstack() methods:

Code to create your sample dataframe:

import numpy as np
import pandas as pd

from io import StringIO
data = StringIO("""
     Date       Time  calories  duration
22/5/2021    Morning       420        50
22/5/2021  Afternoon       380        40
24/5/2021    Morning       390        45
24/5/2021  Afternoon       400        50
26/5/2021    Morning       350        45
26/5/2021  Afternoon       280        50
27/5/2021    Morning       300        44
27/5/2021  Afternoon       430        58
""")

df = pd.read_table(data, sep='\s+')
df
        Date        Time         calories   duration
# 0     22/5/2021   Morning      420        50
# 1     22/5/2021   Afternoon    380        40
# 2     24/5/2021   Morning      390        45
# 3     24/5/2021   Afternoon    400        50
# 4     26/5/2021   Morning      350        45
# 5     26/5/2021   Afternoon    280        50
# 6     27/5/2021   Morning      300        44
# 7     27/5/2021   Afternoon    430        58

Solution:

# convert date column to datetime
df['Date'] = pd.to_datetime(df.Date, format="%d/%m/%Y")


(df
    .set_index(['Date', 'Time'])
    .unstack(fill_value=np.nan)
    .asfreq('D', fill_value=np.nan)
    .stack(dropna=False)
    .sort_index(ascending=[True, False])
    .reset_index()
)
#       Date        Time        calories    duration
# 0     2021-05-22  Morning     420.0       50.0
# 1     2021-05-22  Afternoon   380.0       40.0
# 2     2021-05-23  Morning     NaN         NaN
# 3     2021-05-23  Afternoon   NaN         NaN
# 4     2021-05-24  Morning     390.0       45.0
# 5     2021-05-24  Afternoon   400.0       50.0
# 6     2021-05-25  Morning     NaN         NaN
# 7     2021-05-25  Afternoon   NaN         NaN
# 8     2021-05-26  Morning     350.0       45.0
# 9     2021-05-26  Afternoon   280.0       50.0
# 10    2021-05-27  Morning     300.0       44.0
# 11    2021-05-27  Afternoon   430.0       58.0

I have updated my question! Please help :D – helloitsme May 29 '21 at 05:39 — helloitsme, May 29 '21 at 05:39

Add missing dates with multiple rows per date to dataframe

2 Answers2