Count value between datetime and NaT

user_11493668 0
Viewed 161

I have two python pandas dataframes, in simplified form they look like this:

DF1

+---------+---------+------+-------+
| Date_in | Date_out| Group| Item  |
+---------+---------+------+-------+
| 1991-08 | 2000-08 |   A  |   A1  |
| 1992-08 |   NaT   |   A  |   A2  |
| 1997-02 |   NaT   |   B  |   B1  |
| 1998-03 | 2001-03 |   C  |   C1  |
| 1999-02 | 2002-02 |   D  |   D1  |
| 2000-02 |   NaT   |   D  |   D2  |
| 2000-03 | 2001-04 |   D  |   D3  |
| 2001-08 |   NaT   |   D  |   D4  |
+---------+---------+------+-------+

DF2

+---------+-------+
|  Date   | Group | 
+---------+-------+
| 2000-01 |   A   | 
| 2001-02 |   A   | 
| 2001-03 |   B   |
| 2001-04 |   B   | 
| 2001-05 |   C   | 
| 2001-06 |   C   |
| 2001-03 |   D   |
| 2001-07 |   D   |
+---------|-------+

I want to count how many item still existed in Group column DF2 based on date constraint in DF1

Desired output

+---------+-------+-------+
|  Date   | Group | Total |
+---------+-------+-------+
| 2000-01 |   A   |   2   |
| 2001-02 |   A   |   1   |
| 2001-03 |   B   |   1   |
| 2001-04 |   B   |   1   |
| 2001-05 |   C   |   0   |
| 2001-06 |   C   |   0   |
| 2001-03 |   D   |   3   |
| 2001-07 |   D   |   2   |
+---------|-------+-------+
pandas map-function dataframe
1 Answers

You can first convert all datetimes and replace missing NaT to today date in first step:

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

df1['Date_in'] = pd.to_datetime(df1['Date_in'])
df1['Date_out'] = pd.to_datetime(df1['Date_out']).fillna(pd.to_datetime('now').normalize())
print (df1)
     Date_in   Date_out Group Item
0 1991-08-01 2000-08-01     A   A1
1 1992-08-01 2021-02-12     A   A2
2 1997-02-01 2021-02-12     B   B1
3 1998-03-01 2001-03-01     C   C1
4 1999-02-01 2002-02-01     D   D1
5 2000-02-01 2021-02-12     D   D2
6 2000-03-01 2001-04-01     D   D3
7 2001-08-01 2021-02-12     D   D4

Then get all months between Date_in and Date_out and count months with groups by Grouper and GroupBy.size:

L = [pd.Series(r.Group,pd.date_range(r.Date_in, r.Date_out, freq='MS')) 
     for r in df1.itertuples()]
s = (pd.concat(L)
         .reset_index(name='Group')
         .groupby([pd.Grouper(key='index', freq='MS'), 'Group'])
         .size()
         .rename('Total'))

# print (s)

And last add new column with DataFrame.join and replace NaN to 0 for not matching values:

df2 = df2.join(s, on=['Date','Group'])
df2['Total'] = df2['Total'].fillna(0).astype(int)
print (df2)
        Date Group  Total
0 2000-01-01     A      2
1 2001-02-01     A      1
2 2001-03-01     B      1
3 2001-04-01     B      1
4 2001-05-01     C      0
5 2001-06-01     C      0
6 2001-03-01     D      3
7 2001-07-01     D      2

EDIT:

In real data is necessary working with days, not datetimes, so solution is a bit modified:

#remove times
df2['date'] = df2['created_at'].dt.normalize()

#convert date_range by days
L = [pd.Series(r.dept_name,pd.date_range(r.start_date, r.end_date, freq='d')) 
     for r in df1.itertuples()]
s = (pd.concat(L)
    .reset_index(name='dept_name')
    .groupby([pd.Grouper(key='index', freq='D'), 'dept_name'])
    .size()
    .rename('total_member'))

#join by column date (without times)
df2 = df2.join(s, on=['date','dept_name'])
df2['total_member'] = df2['total_member'].fillna(0).astype(int)
user_29010020
Related