Using an list index to create column in Pandas

May 20, 2022

I’ve been stuck on this for longer than I would like to admit. I’m trying to use index of a list to make a new column based on the Day column. I’m sure this is super simple. Really all I am trying to do is get the day difference between today and these other days.

There maybe even a way to get my result with datetime but I just haven’t been able to find either solution yet.

import pandas as pd
from datetime import datetime


today = datetime.today().strftime('%Y/%m/%d')
todays_week_day = str.upper(str(datetime.today().strftime('%a')))

# Lets assume today is "THU" for this example

todays_week_day = "THU"

day_abrivs = list(["SUN", "MON", "TUE", "WED", "THU", "FRI", "SAT"])

todays_week_day_num = day_abrivs.index(todays_week_day)


df=
    attendance          day
 0     1546             FRI 
 1     1978             SAT 
 2     2150             SUN

df['day_num'] = day_abrivs.index(df['day'])
df['day_diff'] = df['day_num'] - todays_week_day_num

# This gives the following error on the Day_Num col so I don't even get to the Day_diff

Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Python_Projects\Shell-B\venv\lib\site-packages\pandas\core\generic.py", line 1537, in __nonzero__
    raise ValueError(
ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Desired output is as follows:

df=
    attendance          day     day_num    day_diff
 0     1546             FRI        5          1
 1     1978             SAT        6          2
 2     2150             SUN        0         -4

>Solution :

You are getting that error basically because you are not passing a single string value to the index method, you are passing a Serie. So I recommend the Series.apply method to obtain each single day identification. Look at this:

# Your initial dataframe
df = pd.read_csv(io.StringIO("""
atendance,day
1546,FRI
1978,SAT
2150,SUN
"""))

df['day_num'] = df['day'].apply(lambda d: day_abrivs.index(d))
df['day_diff'] = df['day_num'] - todays_week_day_num
print(df)

Output: