Get the last date before an nth date for each month in Python

user_1870412 0
Viewed 178

I am using a csv with an accumulative number that changes daily.

         Day  Accumulative Number
0   9/1/2020                  100
1  11/1/2020                  102
2  18/1/2020                   98
3  11/2/2020                  105
4  24/2/2020                   95
5   6/3/2020                  120
6  13/3/2020                  100

I am now trying to find the best way to aggregate it and compare the monthly results before a specific date. So, I want to check the balance on the 11th of each month but for some months, there is no activity for the specific day. As a result, I trying to get the latest day before the 12th of each Month. So, the above would be:

         Day  Accumulative Number
0  11/1/2020                  102
1  11/2/2020                  105
2   6/3/2020                  120

What I managed to do so far is to just get the latest day of each month:

dateparse = lambda x: pd.datetime.strptime(x, "%d/%m/%Y") 

df = pd.read_csv("Accumulative.csv",quotechar="'", usecols=["Day","Accumulative Number"], index_col=False, parse_dates=["Day"], date_parser=dateparse, na_values=['.', '??']   )  
df.index = df['Day'] 
grouped = df.groupby(pd.Grouper(freq='M')).sum()

print (df.groupby(df.index.month).apply(lambda x: x.iloc[-1]))

which returns:

           Day  Accumulative Number                               
1   2020-01-18                   98
2   2020-02-24                   95
3   2020-03-13                  100

Is there a way to achieve this in Pandas, Python or do I have to use SQL logic in my script? Is there an easier way I am missing out in order to get the "balance" as per the 11th day of each month?

pandas aggregate-functions python-3.x
4 Answers

You can do groupby with factorize

n = 12
df = df.sort_values('Day')
m = df.groupby(df.Day.dt.strftime('%Y-%m')).Day.transform(lambda x :x.factorize()[0])==n
df_sub = df[m].copy()
user_79645270

You can try filtering the dataframe where the days are less than 12 , then take last of each group(grouped by month) :

df['Day'] = pd.to_datetime(df['Day'],dayfirst=True)


(df[df['Day'].dt.day.lt(12)]
 .groupby([df['Day'].dt.year,df['Day'].dt.month],sort=False).last()
 .reset_index(drop=True))

         Day  Accumulative_Number
0 2020-01-11                  102
1 2020-02-11                  105
2 2020-03-06                  120
user_98406370

I would try:

# convert to datetime type:
df['Day'] = pd.to_datetime(df['Day'], dayfirst=True)

# select day before the 12th
new_df = df[df['Day'].dt.day < 12]

# select the last day in each month
new_df.loc[~new_df['Day'].dt.to_period('M').duplicated(keep='last')]

Output:

         Day  Accumulative Number
1 2020-01-11                  102
3 2020-02-11                  105
5 2020-03-06                  120
user_42384080

Here's another way using expanding the date range:

# set as datetime
df2['Day'] = pd.to_datetime(df2['Day'], dayfirst=True)

# set as index
df2 = df2.set_index('Day')

# make a list of all dates
dates = pd.date_range(start=df2.index.min(), end=df2.index.max(), freq='1D')

# add dates
df2 = df2.reindex(dates)

# replace NA with forward fill
df2['Number'] = df2['Number'].ffill()

# filter to get output
df2 = df2[df2.index.day == 11].reset_index().rename(columns={'index': 'Date'})
print(df2)

        Date  Number
0 2020-01-11   102.0
1 2020-02-11   105.0
2 2020-03-11   120.0
user_92992590
Related