Home / Questions / 324466
Accepted
Shurlena
Asked: 2020-01-26 12:07:14 +0800 CST 2020-01-26 12:07:14 +0800 CST

Extract month numbers from a column with dates (strings) and replace it with their name

  • 772

In a DataFrame I have created a Month column, where I have taken the number of the month from another column that had the format 2015.06.12. From the .06. removed, I have converted it into a number with a:

str.extract('(\.\d{2}\.)')

And I've removed the colon like this:

str.extract('(\d{2})')

Now what I wanted to do is change those numbers to the corresponding month. I have seen that it is possible to do it with the calendar module, I have tried to do it like this:

def month(x):
  return calendar.month_name[x]

df['Month'] = df.Month.apply(month)

But I get the error: "list indices must be integers or slices, not float"

Also say that there are NaN cells, that in principle I want to leave them like this.

Any suggestions how I can do it?

Thank you!

python

1 Answers

  1. Best Answer

    The problem is the values NaN​​as I think you suspect. NaNis actually represented as a float, hence the error in using it as an index to calendar.month_name. In fact, the column with the NaNcan't be converted to intwithout losing the NaN.

    You can deal with it in different ways, following your reasoning, a very simple one is to check in the function if the received value ( x) is a value or not NaN. A reproducible example:

    import pandas as pd
import numpy as np
import calendar


df = pd.DataFrame({"Fecha": ("2015.01.12", 
                             "2015.02.12",
                             "2015.03.12",
                             "not a date",
                             "2015.09.12")}
                  )

df["Month"] = df["Fecha"].str.extract('\.(\d{2})\.').astype(np.float)

def month(x):
    return calendar.month_name[int(x)] if not np.isnan(x) else np.nan

df['Month'] = df.Month.apply(month)

    >>> df


        Fecha       Month
0  2015.01.12       enero
1  2015.02.12     febrero
2  2015.03.12       marzo
3  not a date         NaN
4  2015.09.12  septiembre

    However, I would not get so complicated with regex and calendar, you can in principle do this with pandas in a vectorized way just by converting the strings with the dates to type DateTimeand using the method pandas.Series.dt.month_name:

    import pandas as pd



df = pd.DataFrame({"Fecha": ("2015.01.12", 
                             "2015.02.12",
                             "2015.03.12",
                             "not a date",
                             "2015.09.12")}
                  )

df['Mes'] = (pd.to_datetime(df['Fecha'], format='%Y.%m.%d', errors="coerce")
               .dt.month_name(locale='es_ES.utf8')
               )

    >>> df

            Fecha         Mes
    0  2015.01.12       Enero
    1  2015.02.12     Febrero
    2  2015.03.12       Marzo
    3  not a date         NaN
    4  2015.09.12  Septiembre

    I don't know what language you want for the month names, but you can change it by specifying the locale you want using the argument locale. Default is en_US.

    • 4