How can I count the number of dates per ID for each month of a year?

user_7529656 0
Viewed 73

Task:

Calculate the frequency of each ID for each month of 2021

  • Frequency formula: Count of dates within each month for each ID (e.g. ID: 44; Month: 01-2021; Frequency: 3)

Sample:

times = [
    '2021-02-18',
    '2021-03-02',
    '2021-03-02',
    '2021-04-23',
    '2021-01-18',
    '2021-01-19',
    '2021-01-20',
    '2021-01-03',
    '2021-02-04',
    '2021-02-04'
] 

id = [1, 1, 1, 1, 44, 44, 44, 46, 46, 46]

df = pd.DataFrame({'ID':id, 'Date': pd.to_datetime(times)})

df = df.reset_index(drop=True)

print(df)

   ID       Date
0   1 2021-02-18
1   1 2021-03-02
2   1 2021-03-02
3   1 2021-04-23
4  44 2021-01-18
5  44 2021-01-19
6  44 2021-01-20
7  46 2021-01-03
8  46 2021-02-04
9  46 2021-02-04

Desired Output:

   id  01_2021  02_2021  03_2021  04_2021
0   1        0        1        2        1
1  44        3        0        0        0
2  46        1        2        0        0
pandas date datetime python
1 Answers

Use crosstab with convert datetimes to strings in format MM-YYYY:

df = pd.crosstab(df['ID'], df['Date'].dt.strftime('%m_%Y')).reset_index().rename_axis(None, axis=1)
print(df)
   ID  01_2021  02_2021  03_2021  04_2021
0   1        0        1        2        1
1  44        3        0        0        0
2  46        1        2        0        0

With month periods solution is similar, use Series.dt.to_period:

df = pd.crosstab(df['ID'], df['Date'].dt.to_period('m')).reset_index().rename_axis(None, axis=1)
print(df)
   ID  2021-01  2021-02  2021-03  2021-04
0   1        0        1        2        1
1  44        3        0        0        0
2  46        1        2        0        0

EDIT: For half of month is used SMS Dateoffset in resample:

f = lambda x: x.strftime('first_half-%m-%Y')
              if x.day == 1 
              else x.strftime('second_half-%m-%Y')
df = (df.set_index('Date')
        .groupby('ID')
        .resample('SMS')
        .size()
        .unstack(fill_value=0)
        .sort_index(axis=1)
        .rename(columns=f)
        .reset_index()
        .rename_axis(None, axis=1))

print (df)
   ID  first_half-01-2021  second_half-01-2021  first_half-02-2021  \
0   1                   0                    0                   0   
1  44                   0                    3                   0   
2  46                   1                    0                   2   

   second_half-02-2021  first_half-03-2021  second_half-03-2021  \
0                    1                   2                    0   
1                    0                   0                    0   
2                    0                   0                    0   

   first_half-04-2021  second_half-04-2021  
0                   0                    1  
1                   0                    0  
2                   0                    0
user_29010020
Related