0

Code:

forecast ['conus_days'] = np.where(forecast['Month']==forecast["conus_mth"],
                              (forecast['Start Date'] - forecast['start_month'].apply(us_bd)),
                               0)
forecast['conus_days']=forecast['conus_days'].dt.days
forecast ['conus_days1'] = np.where(forecast['Month']==forecast["conus_mth"],
                               forecast['end_month'] - forecast['Start Date'],
                               0)
forecast['conus_days1']=forecast['conus_days1'].dt.days

forecast['oconus_days'] = np.where(forecast['Month']==forecast['oconus_mth'],
                               (forecast['End Date'] - forecast['start_month']),
                               0)
forecast['oconus_days']=forecast['oconus_days'].dt.days

forecast['oconus_days1']= np.where(forecast['Month']== forecast["oconus_mth"],
                              (forecast['end_month']- forecast['End Date'].apply(us_bd)),0)

Result:

       Name      EID  Start Date   End Date      Country  year  Month  \
0       GP        123456 2019-08-01 2020-01-03  Afghanistan  2020      1   
1       MW       3456789 2019-09-22 2020-02-16        Conus  2020      1   
2       MH        456789 2019-12-05 2020-03-12        Conus  2020      1   
3       DR        789456 2019-09-11 2020-03-04         Iraq  2020      1   
4       JR        985756 2020-01-03 2020-05-06      Germany  2020      1   

   days_in_month start_month  end_month  working_days  conus_mth  oconus_mth  \
0             31  2020-01-01 2020-01-31            21          8           1   
1             31  2020-01-01 2020-01-31            21          9           2   
2             31  2020-01-01 2020-01-31            21         12           3   
3             31  2020-01-01 2020-01-31            21          9           3   
4             31  2020-01-01 2020-01-31            21          1           5   

   conus_days  conus_days1  oconus_days  oconus_days1  
0           0            0            2            25  
1           0            0            0             0  
2           0            0            0             0  
3           0            0            0             0  
4           1           28            0             0

row 0, oconus_days1 output should be 20 and NOT 25.

using us_bd = CustomBusinessDay(calendar=USFederalHolidayCalendar()) to calculate the number of workdays between to dates excluding holidays and weekends - same as conus_days column =1, that's correct. If you look at the function np.where .... forecast['end_month'] - forecast['End Date'].apply(us_bd) output should be 20.

Henry Yik
  • 22,275
  • 4
  • 18
  • 40
German Portes
  • 5
  • 6
  • Could you please try without pandas and where, just using `USFederalHolidayCalendar()` and your two days? – Joe Jan 09 '20 at 05:31
  • that only works when using a specified index. I need to iterate through rows and only account for rows where Month == oconus_mth – German Portes Jan 10 '20 at 02:54
  • its actually the apply portion of the function that's not work for that specific row. what should I do different the apply function a few rows above works just fine. – German Portes Jan 10 '20 at 03:30
  • I am not sure that the `where` is the problem. Can you add a new column and just apply the function to all fields and see if your result is as expected? – Joe Jan 10 '20 at 06:23
  • Joe, tried it and got a different error- ValueError: Length of values does not match length of index. forecast['oconus_days1'] = (forecast['Month']==forecast['oconus_mth'], forecast['end_month'] - forecast['End Date'].apply(us_bd), 0) forecast['oconus_days1']=forecast['oconus_days1'].dt.days print(forecast) – German Portes Jan 11 '20 at 20:11
  • https://stackoverflow.com/a/33480745/7919597 try it like that – Joe Jan 11 '20 at 21:21
  • Joe, I've don't that and it works. but, that only allows to to look at one row at a time and does not iterate through rows because it creates a series with index. – German Portes Jan 11 '20 at 22:08
  • import pandas as pd from pandas.tseries.holiday import USFederalHolidayCalendar from pandas.tseries.offsets import CustomBusinessDay day1 = forecast['start_month'][0] day2 = forecast['end_month'][0] us_bd = CustomBusinessDay(calendar=USFederalHolidayCalendar()) forecast['working_days'] = len(pd.pandas.date_range(start=day1,end=day2, freq=us_bd)) print(len(pd.pandas.date_range(start=day1,end=day2, freq=us_bd))) forecast – German Portes Jan 11 '20 at 22:09
  • question, ultimately why does np.where work on the first line as it should and not on the subsequent use? – German Portes Jan 11 '20 at 22:25
  • The problem might be somewhere else. What was the result of the example you just did? 20? 25? Could you please instead of using only the first row iterate from 0 to 4 and compate the result to what you posted in your question? Is it the same or different? – Joe Jan 12 '20 at 06:15
  • Joe, the problem may be ASW with the CustomBusinessDay(calendar=USFederalHolidayCalendar() module not properly counting weekend and holidays between the two daes – German Portes Jan 13 '20 at 20:43
  • 1 import pandas as pd 2 from pandas.tseries.holiday import USFederalHolidayCalendar 3 from pandas.tseries.offsets import CustomBusinessDay 4 us_bd = CustomBusinessDay(calendar=USFederalHolidayCalendar()) 5 dt1 = forecast['end_month'][0] 6 dt2= forecast['End Date'][0] 7 print(len(pd.pandas.date_range(start=dt2,end=dt1, freq=us_bd))) 8 print(forecast) 20 – German Portes Jan 13 '20 at 20:44
  • Please do not post code here, add it above, below your question stating "Edit: New Example" or similar. – Joe Jan 14 '20 at 06:47

0 Answers0