Trouble adding season column based on a datetime object

1
votes

I'm trying to finish my workproject but I'm getting stuck at a certain point.

Part of the dataframe I have is this:

year_month year month
2007-01 2007 1
2009-07 2009 7
2010-03 2010 3

However, I want to add the column "season". I'm illustrating soccer seasons and the season column needs to illustrate what season the players plays. So if month is equal or smaller than 3, the "season" column needs to correspond with ((year-1), "/", year) and if larger with (year, "/", (year + 1)). The table should look like this:

year_month year month season
2007-01 2007 1 2006/2007
2009-07 2009 7 2009/2010
2010-03 2010 3 2009/2010

Hopefully someone else can help me with this problem.

Here is the code to create the first Table:

import pandas as pd
from datetime import datetime

df = pd.DataFrame({'year_month':["2007-01", "2009-07", "2010-03"],
                  'year':[2007, 2009, 2010],
                  'month':[1, 7, 3]})

# convert the 'Date' columns to datetime format
df['year_month']= pd.to_datetime(df['year_month'])

Thanks in advance!

2
pythonpandasdataframemultiple-columns

2 Answers

3
votes

You can use np.where() to specify the condition and get corresponding strings according to True / False of the condition, as follows:

df['season'] = np.where(df['month'] <= 3, 
                        (df['year'] - 1).astype(str) + '/' + df['year'].astype(str), 
                        df['year'].astype(str) + '/' + (df['year'] + 1).astype(str))

Result:

  year_month  year  month     season
0 2007-01-01  2007      1  2006/2007
1 2009-07-01  2009      7  2009/2010
2 2010-03-01  2010      3  2009/2010
2
votes

You can use a lambda function with conditionals and axis=1 to apply it to each row. Using f-Strings reduces the code needed to transform values from the year column into strings as needed for your new season column.

df['season'] = df.apply(lambda x: f"{x['year']-1}/{x['year']}" if x['month'] <= 3 else f"{x['year']}/{x['year']+1}", axis=1)

Output:

  year_month  year  month     season
0    2007-01  2007      1  2006/2007
1    2009-07  2009      7  2009/2010
2    2010-03  2010      3  2009/2010