1

I have a column in my dataframe, df['Month'], that has month names: January, February, etc.

I want to convert these to month numbers: 1, 2, etc.

In my Pandas, df['Month_Num'] = list(cal.month_abbr).index(df['Month']) throws error:

The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Pandas:

import pandas as pd
import calendar as cal

df['Day'] = 1
# convert 'January' to 1
df['Month_Num'] = list(cal.month_abbr).index(df['Month'])
df['date_tw'] = ''
# Create date_time based on format `mm/dd/yy`:
df['date_tw'] = pd.to_datetime(df[['Month_Num', 'Day', 'Year']])

result_df = df

Why is this occurring? The only data I could find on it was from here related to comparison operators, which mine is not using.

user3871
  • 12,432
  • 33
  • 128
  • 268

1 Answers1

1

Try a slightly different approach with map/replace instead;

mapping = {v : k for k, v in enumerate(cal.month_abbr)}
df['Month_Num'] = df['Month'].map(mapping)

For columns with full month names, use cal.month_name instead.

cs95
  • 379,657
  • 97
  • 704
  • 746
  • in my `Month_Num` column, this gets me `5.0` for `May` only, and the rest are `null`. I'm assuming that's because `month_abbr` accepts abbreviations, whereas my `month` col are full months `January, February, etc`. Calendar doesn't seem to offer support for full months? – user3871 Mar 05 '18 at 23:13
  • @Growler Ah, you'll want `cal.month_name` instead. – cs95 Mar 05 '18 at 23:14
  • Great this works. One more thing, I'm getting `ValueError: to assemble mappings requires at least that [year, month, day] be specified: [month] is missing` when I do `df['date_tw'] = pd.to_datetime(df[['Month_Num', 'Day', 'Year']])` — It looks like pandas expects `Year, Month Day`, so I tried that and still get the same error. I checked debugger and `Month_Num` is there with no nulls. – user3871 Mar 05 '18 at 23:29
  • @Growler You may want to try `pd.to_datetime(df[['Year', 'Month_Num', 'Day']])` instead. – cs95 Mar 06 '18 at 03:30