pandas: add week dates with dataframe

user_15822886 0
Viewed 152

I have a df like, which has such rows:

    p_id    m_id    x_id    g_id    u_id
0   2       NaN     1408    7       121
1   3       1259    117     23      315
2   3       1259    221     9       718
3   3       1259    397     76      367

and two datetime objects:

start_date:

datetime.datetime(2021, 5, 25, 0, 0)

end_date:

datetime.datetime(2021, 5, 29, 0, 0)

how do I get a df like, basically (adding the week-dates from start_date to end_date with each row):

    p_id    m_id    x_id    g_id    u_id  s_date
0   2       NaN     1408    7       121   2021-05-25
1   2       NaN     1408    7       121   2021-05-26
2   2       NaN     1408    7       121   2021-05-27
3   2       NaN     1408    7       121   2021-05-28
4   2       NaN     1408    7       121   2021-05-29

5   3       1259    117     23      315   2021-05-25
6   3       1259    117     23      315   2021-05-26
7   3       1259    117     23      315   2021-05-27
8   3       1259    117     23      315   2021-05-28
9   3       1259    117     23      315   2021-05-29
.
.
15  3       1259    397     76      367   2021-05-25
16  3       1259    397     76      367   2021-05-26
17  3       1259    397     76      367   2021-05-27
18  3       1259    397     76      367   2021-05-28
19  3       1259    397     76      367   2021-05-29
pandas python-3.x date python
3 Answers

Generate date_range and cross merge

  1. In pandas version >= 1.2x, to perform a cross merge we can now pass an optional parameter how='cross' to the merge function
dates = pd.date_range(start_date, end_date)
df.merge(dates.to_series(name='s_date'), how='cross')
  1. For pandas version < 1.2x we have to create a temporary merge key in order to perform the cross merge
dates = pd.date_range(start_date, end_date)
df.assign(k=1).merge(dates.to_frame(name='s_date').assign(k=1), on='k').drop('k', 1)

    p_id    m_id  x_id  g_id  u_id     s_date
0      2     NaN  1408     7   121 2021-05-25
1      2     NaN  1408     7   121 2021-05-26
2      2     NaN  1408     7   121 2021-05-27
3      2     NaN  1408     7   121 2021-05-28
4      2     NaN  1408     7   121 2021-05-29
5      3  1259.0   117    23   315 2021-05-25
6      3  1259.0   117    23   315 2021-05-26
7      3  1259.0   117    23   315 2021-05-27
8      3  1259.0   117    23   315 2021-05-28
9      3  1259.0   117    23   315 2021-05-29
10     3  1259.0   221     9   718 2021-05-25
11     3  1259.0   221     9   718 2021-05-26
12     3  1259.0   221     9   718 2021-05-27
13     3  1259.0   221     9   718 2021-05-28
14     3  1259.0   221     9   718 2021-05-29
15     3  1259.0   397    76   367 2021-05-25
16     3  1259.0   397    76   367 2021-05-26
17     3  1259.0   397    76   367 2021-05-27
18     3  1259.0   397    76   367 2021-05-28
19     3  1259.0   397    76   367 2021-05-29
user_128331660

The way I would do is to first create a list of all the dates between the two dates and add this as a new column to the dataframe and then use explode to explode into rows:

Here is an example:

df['s_date'] = [pd.date_range(datetime(2021, 5, 25, 0, 0),datetime(2021, 5, 29, 0, 0),freq='d')] * len(df)
df = df.explode('s_date')

Output:


    id  start   score   date
0   id1     NaN     3   2021-05-25
0   id1     NaN     3   2021-05-26
0   id1     NaN     3   2021-05-27
0   id1     NaN     3   2021-05-28
0   id1     NaN     3   2021-05-29
1   id2     12.0    1   2021-05-25
1   id2     12.0    1   2021-05-26
1   id2     12.0    1   2021-05-27
1   id2     12.0    1   2021-05-28
1   id2     12.0    1   2021-05-29
2   id3     11.0    8   2021-05-25
2   id3     11.0    8   2021-05-26
2   id3     11.0    8   2021-05-27
2   id3     11.0    8   2021-05-28
2   id3     11.0    8   2021-05-29
...
...
user_65738890

the steps in my solution:

  1. create a list of all the dates
  2. convert the list into DataFrame
  3. add 'key' column for merge
  4. pd.merge the two DataFrame (outer join)
  5. remove 'key' column
import pandas as pd
from datetime import datetime, timedelta
# example to your df
a = [{'a': 1, 'b': 2}, {'a': 3, 'b': 4}]
a_df = pd.DataFrame(a)

start_date = datetime.strptime('2021-05-01', '%Y-%m-%d')
end_date = datetime.strptime('2021-06-01', '%Y-%m-%d')
num_of_days = (end_date - start_date).days

date_df = pd.DataFrame([start_date + timedelta(days=x) for x in range(num_of_days)], columns=['date'])
a_df = pd.DataFrame(a)

a_df['key'] = 0
date_df['key'] = 0

a_df = a_df.merge(date_df, on='key', how='outer')
a_df = a_df.drop('key', 1)

a_df
user_125572350
Related