Groupby calculation for only one part of dataframe - Pandas

user_11396737 0
Viewed 73

I am trying to make some calculations using two dataframes and a groupby code. However, I am not able to find the way to make these calculations only when my variable "date_int" is larger or equal than an specific number (e.g., 20180501; equivalent to date "2018-05-01").

In other words the groupby code that I am using does not consider only the relevant combinations (the ones starting on 2018-05-01) and does all the calculations for previous combinations. My pourpose is to save time and to have a code that only calculates for the combinations that I am looking for starting from 2018-05-01.

Below I give the two dataframes, the calculation (conflicting part of the code), and the expected result.

Dataframe 1 (df):

idx = [np.array(['Jan-18', 'Jan-18', 'Feb-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'May-18', 'Jun-18', 'Jun-18', 'Jun-18','Jul-18', 'Aug-18', 'Aug-18', 'Sep-18', 'Sep-18', 'Oct-18','Oct-18', 'Oct-18', 'Nov-18', 'Dec-18', 'Dec-18',]),np.array(['A', 'B', 'B', 'A', 'B', 'C', 'D', 'E', 'B', 'A', 'B', 'C','A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'A', 'B', 'C'])]
data = [{'place': 1}, {'place': 5}, {'place': 3}, {'place': 2}, {'place': 7}, {'place': 3},{'place': 1}, {'place': 6}, {'place': 3}, {'place': 5}, {'place': 2}, {'place': 3},{'place': 1}, {'place': 9}, {'place': 3}, {'place': 2}, {'place': 7}, {'place': 3}, {'place': 6}, {'place': 8}, {'place': 2}, {'place': 7}, {'place': 9}]
df = pd.DataFrame(data, index=idx, columns=['place'])
df.index.names=['date','name']
df=df.reset_index()
df['date'] = pd.to_datetime(df['date'],format = '%b-%y') # http://strftime.org/
#df=df.set_index(['date','type'])
df.reset_index(inplace=True)
df['place'] = df.place.astype('float')
df['date_int'] = df['date'].astype('str').str.replace('-','').astype('int64')
df.set_index(['date_int','name'], inplace = True)

Dataframe 2 (df2):

idx = [np.array(['Jan-18', 'Jan-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18','Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Aug-18',  'Aug-18', 'Sep-18', 'Sep-18', 'Oct-18','Oct-18', 'Oct-18','Oct-18', 'Oct-18','Oct-18', 'Dec-18', 'Dec-18',]),
       np.array(['A', 'B', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C','C', 'C', 'D', 'D', 'D', 'D', 'E', 'E', 'E', 'E', 'A', 'A', 'B', 'B', 'C', 'C', 'B', 'C', 'A', 'B', 'C', 'C', 'A', 'A', 'B', 'B', 'B', 'C']),
       np.array(['B', 'A', 'B', 'C', 'D', 'E', 'A', 'C', 'D', 'E', 'A', 'B','D', 'E', 'A', 'B', 'C', 'E', 'A', 'B', 'C', 'D', 'B', 'C', 'A', 'C', 'A', 'B', 'C', 'B', 'B', 'A', 'A', 'B', 'C', 'B', 'C', 'A', 'C', 'B'])]
data = [{'xx': -4, 'win': -1}, {'xx': 4, 'win': 1}, {'xx': -5, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': 1, 'win': 1}, {'xx': -4, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': 4, 'win': 1}, {'xx': 6, 'win': 1}, {'xx': 1, 'win': 1}, {'xx': 1, 'win': 1}, {'xx': -4, 'win': -1},{'xx': 2, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': -6, 'win': -1}, {'xx': -2, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 4, 'win': 1}, {'xx': -1, 'win': -1}, {'xx': 3, 'win': 1}, {'xx': 5, 'win': 1}, {'xx': 3, 'win': 1}, {'xx': 2, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': -2, 'win': -1}, {'xx': 1, 'win': 1}, {'xx': 6, 'win': 1}, {'xx': -6, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 3, 'win': 1}, {'xx': -2, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': 2, 'win': 1}, {'xx': -2, 'win': -1}, {'xx': 2, 'win': 1}]
df2 = pd.DataFrame(data, index=idx, columns=['xx','win'])
df2.index.names=['date','i1', 'i2']
df2=df2.reset_index()
df2['date'] = pd.to_datetime(df2['date'],format = '%b-%y') # http://strftime.org/
#df=df.set_index(['date','type'])
df2.reset_index(inplace=True)
df2['xx'] = df2.xx.astype('float')
df2['date_int'] = df2['date'].astype('str').str.replace('-','').astype('int64')
df2=df2.drop(['date','index'], axis=1)
df2['date_int2']=df2['date_int']
df2.set_index(['date_int','i1','i2'], inplace = True)

The conflicting code (which doesn't do it for values of the variable "date_int" greater or equal than the number 20180501)

df10=df.copy()
if (df10['date']>='2018-05-01').any():
    df10['output'] = (df2.assign(to_this = df2['xx'][df2['date_int2']>=20180501])).groupby(level=[1,2]).to_this.cumcount().sum(level=[0,1])
    df10['output'].fillna(0,inplace=True)

The expected outcome:

               index       date  place  output
date_int name                                 
20180501 B         8 2018-05-01    3.0     0.0
20180601 A         9 2018-06-01    5.0     3.0
         B        10 2018-06-01    2.0     3.0
         C        11 2018-06-01    3.0     2.0
20180701 A        12 2018-07-01    1.0     0.0
20180801 B        13 2018-08-01    9.0     2.0
         C        14 2018-08-01    3.0     2.0
20180901 A        15 2018-09-01    2.0     3.0
         B        16 2018-09-01    7.0     3.0
20181001 C        17 2018-10-01    3.0     5.0
         A        18 2018-10-01    6.0     6.0
         B        19 2018-10-01    8.0     7.0
20181101 A        20 2018-11-01    2.0     0.0
20181201 B        21 2018-12-01    7.0     4.0
         C        22 2018-12-01    9.0     4.0

If you could elaborate on the code to make it work only when my variable "date_int" is larger or equal than a specific value it would be useful, as it will save me a lot of time.

pandas-groupby pandas dataframe python
2 Answers
df['date'].apply(pd.Timestamp.timestamp).astype(int)

Or

pd.to_datetime(df['date']).values.astype(int)
user_138133650

Explanation:

The updated code below uses a different approach. Rather than remove data from the DataFrame after it is created, it does not use that data at all. This is done by a mask_it() function. This function can be used to create a boolean mask based on the date_int. This mask can be applied to all the arrays/lists such as idx, data etc. to keep only those datapoints that are on or after the date_int. Next, these subset of indices/data are used to create the DataFrame.

Notes:

  1. Code-1 below seems to be correct, but does not match the ouptut posted in the question. Here, df1 and df2 are created from already masked list of indices before any grouping. So, only dates of on or after the date_int are used. Therefore, .cumcount() may be correct. This may be important because .group() at level=[1,2] does not include any dates.
  2. Code-2 below output matches the output posted in question, however it may not be correct. Because, here the grouping is done "before" any dates are removed. Thus, the grouping at level=[1,2] could include dates before date_int. This can be checked by using df2.groupby(level=[1,2])['xx'].groups().

Side-note: The updated code in the question is giving me a different expected output for df10 than what is posted in the question

Code-1:

### Import libraries
import numpy as np
import pandas as pd
import datetime

### Create function for mask
def mask_it(date_int, xarray):
    # Create 'start_date'
    dt = str(date_int) # convert integer 'date_int' to string
    dt = datetime.datetime(year=int(dt[0:4]), month=int(dt[4:6]), day=int(dt[6:8])) # convert to date
    dtyear = dt.strftime("%y") # get last two digits of year
    dtmonth = dt.strftime("%B")[0:3] # get first three characters of month
    start_date = str(dtmonth)+'-'+str(dtyear)
    start_date =  datetime.datetime.strptime(start_date, '%b-%y')
    
    # Convert array dates from string to datetime
    xarray_NEW = np.array([ datetime.datetime.strptime(i, '%b-%y') for i in xarray])
    print(type(start_date))
    # Create boolean mask
    mask = [i >= start_date for i in xarray_NEW]
    
    return mask



### Function to create `df1`
def start_date_df(date_int):
    
    # Index
    df1_idx_date = np.array(['Jan-18', 'Jan-18', 'Feb-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'May-18', 'Jun-18', 'Jun-18', 'Jun-18','Jul-18', 'Aug-18', 'Aug-18', 'Sep-18', 'Sep-18', 'Oct-18','Oct-18', 'Oct-18', 'Nov-18', 'Dec-18', 'Dec-18',])
    df1_idx_char = np.array(['A', 'B', 'B', 'A', 'B', 'C', 'D', 'E', 'B', 'A', 'B', 'C','A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'A', 'B', 'C'])

    # Data
    df1_data = np.array([{'place': 1}, {'place': 5}, {'place': 3}, {'place': 2}, {'place': 7}, {'place': 3},{'place': 1}, {'place': 6}, {'place': 3}, {'place': 5}, {'place': 2}, {'place': 3},{'place': 1}, {'place': 9}, {'place': 3}, {'place': 2}, {'place': 7}, {'place': 3}, {'place': 6}, {'place': 8}, {'place': 2}, {'place': 7}, {'place': 9}])    


    #:::::::::::::::::::::::::::::::::::::::::::::
    ### Subset by using mask_it
    #:::::::::::::::::::::::::::::::::::::::::::::
    # Get mask
    mask1 = mask_it(date_int, df1_idx_date) 
    # Use mask
    df1_idx_date = df1_idx_date[mask1]
    df1_idx_char = df1_idx_char[mask1]
    df1_data = df1_data[mask1]
    #:::::::::::::::::::::::::::::::::::::::::::::   
    
    ### Get 'idx' and 'data'
    idx = [df1_idx_date, df1_idx_char]
    data = list(df1_data)

    
    df = pd.DataFrame(data, index=idx, columns=['place'])
    df.index.names=['date','name']
    df=df.reset_index()
    df['date'] = pd.to_datetime(df['date'],format = '%b-%y') # http://strftime.org/
    #df=df.set_index(['date','type'])
    df.reset_index(inplace=True)
    df['place'] = df.place.astype('float')
    df['date_int'] = df['date'].astype('str').str.replace('-','').astype('int64')
    df.set_index(['date_int','name'], inplace = True)


    return df


### Function to create 'df2'
def start_date_df2(date_int):
    
    # Index
    df2_idx_date = np.array(['Jan-18', 'Jan-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18','Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Aug-18',  'Aug-18', 'Sep-18', 'Sep-18', 'Oct-18','Oct-18', 'Oct-18','Oct-18', 'Oct-18','Oct-18', 'Dec-18', 'Dec-18',])
    df2_idx_char1 = np.array(['A', 'B', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C','C', 'C', 'D', 'D', 'D', 'D', 'E', 'E', 'E', 'E', 'A', 'A', 'B', 'B', 'C', 'C', 'B', 'C', 'A', 'B', 'C', 'C', 'A', 'A', 'B', 'B', 'B', 'C'])
    df2_idx_char2 = np.array(['B', 'A', 'B', 'C', 'D', 'E', 'A', 'C', 'D', 'E', 'A', 'B','D', 'E', 'A', 'B', 'C', 'E', 'A', 'B', 'C', 'D', 'B', 'C', 'A', 'C', 'A', 'B', 'C', 'B', 'B', 'A', 'A', 'B', 'C', 'B', 'C', 'A', 'C', 'B'])
    

    # Data
    df2_data = np.array([{'xx': -4, 'win': -1}, {'xx': 4, 'win': 1}, {'xx': -5, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': 1, 'win': 1}, {'xx': -4, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': 4, 'win': 1}, {'xx': 6, 'win': 1}, {'xx': 1, 'win': 1}, {'xx': 1, 'win': 1}, {'xx': -4, 'win': -1},{'xx': 2, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': -6, 'win': -1}, {'xx': -2, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 4, 'win': 1}, {'xx': -1, 'win': -1}, {'xx': 3, 'win': 1}, {'xx': 5, 'win': 1}, {'xx': 3, 'win': 1}, {'xx': 2, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': -2, 'win': -1}, {'xx': 1, 'win': 1}, {'xx': 6, 'win': 1}, {'xx': -6, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 3, 'win': 1}, {'xx': -2, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': 2, 'win': 1}, {'xx': -2, 'win': -1}, {'xx': 2, 'win': 1}])


    #:::::::::::::::::::::::::::::::::::::::::::::
    ### Subset by using mask_it
    #:::::::::::::::::::::::::::::::::::::::::::::
    # Get mask
    mask2 = mask_it(date_int, df2_idx_date) 
    # Use mask
    df2_idx_date = df2_idx_date[mask2]
    df2_idx_char1 = df2_idx_char1[mask2]
    df2_idx_char2 = df2_idx_char2[mask2]
    df2_data = df2_data[mask2]
    #::::::::::::::::::::::::::::::::::::::::::::: 
    
    ### Get 'idx' and 'data'
    idx = [df2_idx_date, df2_idx_char1, df2_idx_char2]
    data = list(df2_data)
    
    
    df2 = pd.DataFrame(data, index=idx, columns=['xx','win'])
    df2.index.names=['date','i1', 'i2']
    df2=df2.reset_index()
    df2['date'] = pd.to_datetime(df2['date'],format = '%b-%y') # http://strftime.org/
    #df=df.set_index(['date','type'])
    df2.reset_index(inplace=True)
    df2['xx'] = df2.xx.astype('float')
    df2['date_int'] = df2['date'].astype('str').str.replace('-','').astype('int64')
    df2=df2.drop(['date','index'], axis=1)
    df2.set_index(['date_int','i1','i2'], inplace = True)
    
    
    #........................
    # Limit by date_int
    df2 = df2.groupby(level=[1,2])['xx'].cumcount().sum(level=[0,1]) 
    
    #df2 = df2.loc[date_int:,:]
    #........................
    
    return df2


### Get DataFrames based on 'date_int'
df = start_date_df(20180501)
df['output'] = start_date_df2(20180501)
df['output'].fillna(0,inplace=True)

Output - 1

print(df)

               index       date  place  output
date_int name                                 
20180501 B         0 2018-05-01    3.0     0.0
20180601 A         1 2018-06-01    5.0     0.0
         B         2 2018-06-01    2.0     0.0
         C         3 2018-06-01    3.0     0.0
20180701 A         4 2018-07-01    1.0     0.0
20180801 B         5 2018-08-01    9.0     1.0
         C         6 2018-08-01    3.0     1.0
20180901 A         7 2018-09-01    2.0     1.0
         B         8 2018-09-01    7.0     1.0
20181001 C         9 2018-10-01    3.0     3.0
         A        10 2018-10-01    6.0     3.0
         B        11 2018-10-01    8.0     4.0
20181101 A        12 2018-11-01    2.0     0.0
20181201 B        13 2018-12-01    7.0     3.0
         C        14 2018-12-01    9.0     3.0

Code - 2:

### Import libraries
import numpy as np
import pandas as pd
import datetime

### Create function for mask
def mask_it(date_int, xarray):
    # Create 'start_date'
    dt = str(date_int) # convert integer 'date_int' to string
    dt = datetime.datetime(year=int(dt[0:4]), month=int(dt[4:6]), day=int(dt[6:8])) # convert to date
    dtyear = dt.strftime("%y") # get last two digits of year
    dtmonth = dt.strftime("%B")[0:3] # get first three characters of month
    start_date = str(dtmonth)+'-'+str(dtyear)
    start_date =  datetime.datetime.strptime(start_date, '%b-%y')
    
    # Convert array dates from string to datetime
    xarray_NEW = np.array([ datetime.datetime.strptime(i, '%b-%y') for i in xarray])
    print(type(start_date))
    # Create boolean mask
    mask = [i >= start_date for i in xarray_NEW]
    
    return mask



### Function to create `df1`
def start_date_df(date_int):
    
    # Index
    df1_idx_date = np.array(['Jan-18', 'Jan-18', 'Feb-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'May-18', 'Jun-18', 'Jun-18', 'Jun-18','Jul-18', 'Aug-18', 'Aug-18', 'Sep-18', 'Sep-18', 'Oct-18','Oct-18', 'Oct-18', 'Nov-18', 'Dec-18', 'Dec-18',])
    df1_idx_char = np.array(['A', 'B', 'B', 'A', 'B', 'C', 'D', 'E', 'B', 'A', 'B', 'C','A', 'B', 'C', 'A', 'B', 'C', 'A', 'B', 'A', 'B', 'C'])

    # Data
    df1_data = np.array([{'place': 1}, {'place': 5}, {'place': 3}, {'place': 2}, {'place': 7}, {'place': 3},{'place': 1}, {'place': 6}, {'place': 3}, {'place': 5}, {'place': 2}, {'place': 3},{'place': 1}, {'place': 9}, {'place': 3}, {'place': 2}, {'place': 7}, {'place': 3}, {'place': 6}, {'place': 8}, {'place': 2}, {'place': 7}, {'place': 9}])    


    #:::::::::::::::::::::::::::::::::::::::::::::
    ### Subset by using mask_it
    #:::::::::::::::::::::::::::::::::::::::::::::
    # Get mask
    mask1 = mask_it(date_int, df1_idx_date) 
    # Use mask
    df1_idx_date = df1_idx_date[mask1]
    df1_idx_char = df1_idx_char[mask1]
    df1_data = df1_data[mask1]
    #:::::::::::::::::::::::::::::::::::::::::::::   
    
    ### Get 'idx' and 'data'
    idx = [df1_idx_date, df1_idx_char]
    data = list(df1_data)

    
    df = pd.DataFrame(data, index=idx, columns=['place'])
    df.index.names=['date','name']
    df=df.reset_index()
    df['date'] = pd.to_datetime(df['date'],format = '%b-%y') # http://strftime.org/
    #df=df.set_index(['date','type'])
    df.reset_index(inplace=True)
    df['place'] = df.place.astype('float')
    df['date_int'] = df['date'].astype('str').str.replace('-','').astype('int64')
    df.set_index(['date_int','name'], inplace = True)


    return df


### Function to create 'df2'
def start_date_df2(date_int):
    
    # Index
    df2_idx_date = np.array(['Jan-18', 'Jan-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18', 'Mar-18', 'Mar-18', 'Mar-18','Mar-18','Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Jun-18', 'Aug-18',  'Aug-18', 'Sep-18', 'Sep-18', 'Oct-18','Oct-18', 'Oct-18','Oct-18', 'Oct-18','Oct-18', 'Dec-18', 'Dec-18',])
    df2_idx_char1 = np.array(['A', 'B', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'C', 'C','C', 'C', 'D', 'D', 'D', 'D', 'E', 'E', 'E', 'E', 'A', 'A', 'B', 'B', 'C', 'C', 'B', 'C', 'A', 'B', 'C', 'C', 'A', 'A', 'B', 'B', 'B', 'C'])
    df2_idx_char2 = np.array(['B', 'A', 'B', 'C', 'D', 'E', 'A', 'C', 'D', 'E', 'A', 'B','D', 'E', 'A', 'B', 'C', 'E', 'A', 'B', 'C', 'D', 'B', 'C', 'A', 'C', 'A', 'B', 'C', 'B', 'B', 'A', 'A', 'B', 'C', 'B', 'C', 'A', 'C', 'B'])
    

    # Data
    df2_data = np.array([{'xx': -4, 'win': -1}, {'xx': 4, 'win': 1}, {'xx': -5, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': 1, 'win': 1}, {'xx': -4, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': 4, 'win': 1}, {'xx': 6, 'win': 1}, {'xx': 1, 'win': 1}, {'xx': 1, 'win': 1}, {'xx': -4, 'win': -1},{'xx': 2, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': -6, 'win': -1}, {'xx': -2, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 4, 'win': 1}, {'xx': -1, 'win': -1}, {'xx': 3, 'win': 1}, {'xx': 5, 'win': 1}, {'xx': 3, 'win': 1}, {'xx': 2, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -1, 'win': -1}, {'xx': -2, 'win': -1}, {'xx': 1, 'win': 1}, {'xx': 6, 'win': 1}, {'xx': -6, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': -3, 'win': -1}, {'xx': -5, 'win': -1}, {'xx': 3, 'win': 1}, {'xx': -2, 'win': -1}, {'xx': 5, 'win': 1}, {'xx': 2, 'win': 1}, {'xx': -2, 'win': -1}, {'xx': 2, 'win': 1}])

    """
    #:::::::::::::::::::::::::::::::::::::::::::::
    ### Subset by using mask_it
    #:::::::::::::::::::::::::::::::::::::::::::::
    # Get mask
    mask2 = mask_it(date_int, df2_idx_date) 
    # Use mask
    df2_idx_date = df2_idx_date[mask2]
    df2_idx_char1 = df2_idx_char1[mask2]
    df2_idx_char2 = df2_idx_char2[mask2]
    df2_data = df2_data[mask2]
    #::::::::::::::::::::::::::::::::::::::::::::: 
    """
    
    ### Get 'idx' and 'data'
    idx = [df2_idx_date, df2_idx_char1, df2_idx_char2]
    data = list(df2_data)
    
    
    df2 = pd.DataFrame(data, index=idx, columns=['xx','win'])
    df2.index.names=['date','i1', 'i2']
    df2=df2.reset_index()
    df2['date'] = pd.to_datetime(df2['date'],format = '%b-%y') # http://strftime.org/
    #df=df.set_index(['date','type'])
    df2.reset_index(inplace=True)
    df2['xx'] = df2.xx.astype('float')
    df2['date_int'] = df2['date'].astype('str').str.replace('-','').astype('int64')
    df2=df2.drop(['date','index'], axis=1)
    df2.set_index(['date_int','i1','i2'], inplace = True)
    
    
    #........................
    # Limit by date_int
    df2 = df2.groupby(level=[1,2])['xx'].cumcount().sum(level=[0,1]) 
    
    #df2 = df2.loc[date_int:,:]
    #........................
    
    return df2


### Get DataFrames based on 'date_int'
df = start_date_df(20180501)
df['output'] = start_date_df2(20180501)
df['output'].fillna(0,inplace=True)

Output - 2:

print(df)

               index       date  place  output
date_int name                                 
20180501 B         0 2018-05-01    3.0     0.0
20180601 A         1 2018-06-01    5.0     3.0
         B         2 2018-06-01    2.0     3.0
         C         3 2018-06-01    3.0     2.0
20180701 A         4 2018-07-01    1.0     0.0
20180801 B         5 2018-08-01    9.0     2.0
         C         6 2018-08-01    3.0     2.0
20180901 A         7 2018-09-01    2.0     3.0
         B         8 2018-09-01    7.0     3.0
20181001 C         9 2018-10-01    3.0     5.0
         A        10 2018-10-01    6.0     6.0
         B        11 2018-10-01    8.0     7.0
20181101 A        12 2018-11-01    2.0     0.0
20181201 B        13 2018-12-01    7.0     4.0
         C        14 2018-12-01    9.0     4.0
user_60052060
Related