1

I have a dataframe (df):

    year  month ETP
0   2021    1   49.21
1   2021    2   34.20
2   2021    3   31.27
3   2021    4   29.18
4   2021    5   33.25
5   2021    6   24.70

I would like to add a column that gives me the number of working days for each row excluding holidays and weekends (for a specific country, exp: France or US)

so the output will be :

    year  month ETP     work_day
0   2021    1   49.21      20
1   2021    2   34.20      20
2   2021    3   31.27      21
3   2021    4   29.18      19
4   2021    5   33.25      20
5   2021    6   24.70      19

code :

import numpy as np
import pandas as pd
days = np.busday_count( '2021-01', '2021-06' ) 
df.insert(3, "work_day", [days])

and I got this error :

ValueError: Length of values does not match length of index

Any suggestions?

Thank you for your help

Fazia Chenna
  • 125
  • 1
  • 15

1 Answers1

2

assuming you are the one that will input the workdays, I suppose you can do it like this:

data = {'year': [2020, 2020, 2021, 2023, 2022], 
        'month': [1, 2, 3, 4, 6]} 
 
df = pd.DataFrame(data)
 
df.insert(2, "work_day", [20,20,23,21,22])

Where the 2 is the position of the new column, not just to be at the end, work_day is the name and the list has the values for every row.

EDIT: With NumPy

import numpy as np
import pandas as pd

days = np.busday_count( '2021-02', '2021-03' )
data = {'year': [2021], 
        'month': ['february']} 
 
df = pd.DataFrame(data)
 
df.insert(2, "work_day", [days])

with the busday_count you specify the starting and ending dates you want to see the workdays in. the result :

   year     month  work_day
0  2021  february  20
Tony
  • 618
  • 12
  • 27
  • @ T0ny1234 No, I want to have a code that allows me to automatically calculate the working days, I have a schedule from 2020 to 2030 so I cannot do it manually – Fazia Chenna Feb 17 '21 at 08:43
  • You can use NumPy, I will edit the code in the answer for you to see. – Tony Feb 17 '21 at 08:56
  • @ T0ny1234 sorry for my late reply, yes this is what I would like to have but when I tried on the dataframe in my example , it doesn't really work ! – Fazia Chenna Feb 17 '21 at 16:34
  • I add the error and the code in my question – Fazia Chenna Feb 18 '21 at 08:49
  • This error is due to not setting the correct amount of values. The sample code is just for one month. If you want to make ot for every month you should iterate through every month for the years you want. – Tony Feb 18 '21 at 09:23
  • @ T0ny1234 ok, yes it makes sense but I can't put all the years and all the months in the dataframe manually, the data I have is from 2021 to 2030! – Fazia Chenna Feb 18 '21 at 09:33