Transfom table with dates

Question

df = pd.DataFrame({'SKU': ['10001', '10002', '10003', '10004', '10004', '10001', '10002', '10003', '10004', '10004'],
                 'shop': ['s001', 's001', 's001', 's001', 's001', 's002', 's002', 's002', 's002', 's002'],
                 'date_start': ['01.02.2022', '21.02.2022', '15.03.2022', '20.03.2022', '20.03.2022', '01.02.2022', '21.02.2022', '15.03.2022', '20.03.2022', '20.03.2022'],
                 'date_end': ['02.05.2022', '03.03.2022', '23.03.2022', '30.03.2022', '22.03.2022', '02.05.2022', '03.03.2022', '23.03.2022', '30.03.2022', '22.03.2022']
                  })

I have table like "1" on the image, and i want transforme it to the "2".

I want get date range between date_start and date_end in my dataframe, for every SKU, shop and other data.

enter image description here

I think I should make some date generate in new column and after that open up.

score 1 · Accepted Answer · answered Mar 22 '22 at 17:30

Create a date range using pd.date_range for each row, then explode them:

df['Список дат'] = df.apply(lambda x: pd.date_range(pd.to_datetime(x['date_start'], dayfirst=True), pd.to_datetime(x['date_end'], dayfirst=True)), axis=1)
df = df.drop(['date_start', 'date_end'], axis=1).explode('Список дат').reset_index(drop=True)

Output:

>>> df
       SKU  shop  Список дат
0    10001  s001  2022-02-01
1    10001  s001  2022-02-02
2    10001  s001  2022-02-03
3    10001  s001  2022-02-04
4    10001  s001  2022-02-05
..     ...   ...         ...
245  10004  s002  2022-03-29
246  10004  s002  2022-03-30
247  10004  s002  2022-03-20
248  10004  s002  2022-03-21
249  10004  s002  2022-03-22

[250 rows x 3 columns]

score 0 · Answer 2 · answered Mar 22 '22 at 17:37

Try:

fmt='%d.%m.%Y'
df['dates'] = [pd.date_range(pd.to_datetime(s, format = fmt),
                             pd.to_datetime(e, format = fmt), 
                             freq='D') 
               for s, e in zip(df['date_start'], df['date_end'])]
df[['SKU','shop','dates']].explode('dates')

Output:

      SKU  shop      dates
0   10001  s001 2022-02-01
0   10001  s001 2022-02-02
0   10001  s001 2022-02-03
0   10001  s001 2022-02-04
0   10001  s001 2022-02-05
..    ...   ...        ...
8   10004  s002 2022-03-29
8   10004  s002 2022-03-30
9   10004  s002 2022-03-20
9   10004  s002 2022-03-21
9   10004  s002 2022-03-22

[250 rows x 3 columns]

Transfom table with dates

2 Answers2