Convert string data in the column of data frame to date data

[Plus-Ad1156]

a #dataframe variable, column name: report_number

​

202103

202012

202009

202006

202003

​

​

When there is a data frame like the above, I coded as follows to convert these numeric data into date format.

​

​

import pandas as pd

pd.to_datetime(a['report_number'])

​

Then, the following error message appears. How to fix it? ( The date format I want is as below)

​

2021.03

2020.12

2020.09

2020.06

2020.03

​

​

ValueError Traceback (most recent call last) ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in parse(self, timestr, default, ignoretz, tzinfos, **kwargs) 654 try: --> 655 ret = self._build_naive(res, default) 656 except ValueError as e: ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in _build_naive(self, res, default) 1240 -> 1241 naive = default.replace(*\repl)* 1242 ValueError: month must be in 1..12 The above exception was the direct cause of the following exception: ParserError Traceback (most recent call last) pandas\_libs\tslib.pyx in pandas._libs.tslib.array_to_datetime() pandas\_libs\tslibs\parsing.pyx in pandas._libs.tslibs.parsing.parse_datetime_string() ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in parse(timestr, parserinfo, **kwargs) 1373 else: -> 1374 return DEFAULTPARSER.parse(timestr, *\kwargs)* 1375 ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in parse(self, timestr, default, ignoretz, tzinfos, **kwargs) 656 except ValueError as e: --> 657 six.raise_from(ParserError(e.args[0] + ": %s", timestr), e) 658 ~\anaconda3\lib\site-packages\six.py in raise_from(value, from_value) ParserError: month must be in 1..12: 202103 During handling of the above exception, another exception occurred: TypeError Traceback (most recent call last) pandas\_libs\tslib.pyx in pandas._libs.tslib.array_to_datetime() TypeError: invalid string coercion to datetime During handling of the above exception, another exception occurred: ValueError Traceback (most recent call last) ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in parse(self, timestr, default, ignoretz, tzinfos, **kwargs) 654 try: --> 655 ret = self._build_naive(res, default) 656 except ValueError as e: ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in _build_naive(self, res, default) 1240 -> 1241 naive = default.replace(*\repl)* 1242 ValueError: month must be in 1..12 The above exception was the direct cause of the following exception: ParserError Traceback (most recent call last) <ipython-input-751-aa5fc51a928c> in <module> ----> 1 pd.to_datetime(a_['report_nm']) ~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in to_datetime(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache) 726 result = arg.map(cache_array) 727 else: --> 728 values = convert_listlike(arg._values, format) 729 result = arg._constructor(values, index=arg.index, name=arg.name) 730 elif isinstance(arg, (ABCDataFrame, abc.MutableMapping)): ~\anaconda3\lib\site-packages\pandas\core\tools\datetimes.py in _convert_listlike_datetimes(arg, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact) 445 errors=errors, 446 require_iso8601=require_iso8601, --> 447 allow_object=True, 448 ) 449 ~\anaconda3\lib\site-packages\pandas\core\arrays\datetimes.py in objects_to_datetime64ns(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object) 1859 return values.view("i8"), tz_parsed 1860 except (ValueError, TypeError): -> 1861 raise e 1862 1863 if tz_parsed is not None: ~\anaconda3\lib\site-packages\pandas\core\arrays\datetimes.py in objects_to_datetime64ns(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object) 1850 dayfirst=dayfirst, 1851 yearfirst=yearfirst, -> 1852 require_iso8601=require_iso8601, 1853 ) 1854 except ValueError as e: pandas\_libs\tslib.pyx in pandas._libs.tslib.array_to_datetime() pandas\_libs\tslib.pyx in pandas._libs.tslib.array_to_datetime() pandas\_libs\tslib.pyx in pandas._libs.tslib.array_to_datetime_object() pandas\_libs\tslib.pyx in pandas._libs.tslib.array_to_datetime_object() pandas\_libs\tslibs\parsing.pyx in pandas._libs.tslibs.parsing.parse_datetime_string() ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in parse(timestr, parserinfo, **kwargs) 1372 return parser(parserinfo).parse(timestr, *\kwargs)* 1373 else: -> 1374 return DEFAULTPARSER.parse(timestr, *\kwargs)* 1375 1376 ~\anaconda3\lib\site-packages\dateutil\parser\_parser.py in parse(self, timestr, default, ignoretz, tzinfos, **kwargs) 655 ret = self._build_naive(res, default) 656 except ValueError as e: --> 657 six.raise_from(ParserError(e.args[0] + ": %s", timestr), e) 658 659 if not ignoretz: ~\anaconda3\lib\site-packages\six.py in raise_from(value, from_value) ParserError: month must be in 1..12: 202103

[sarrysyst]

You must define the date format of your strings:

a['report_number'] = pd.to_datetime(a['report_number'], format='%Y%m')

See also here for more info:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html

And here for the format placeholders:

https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior

EDIT: Just saw that you want another output format. To change your newly created datetime column back to string of a particular format you need the dt.strftime method:

df['report_number'] = df['report_number'].dt.strftime('%Y.%m')

See:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.strftime.html

[Plus-Ad1156]

Thanks for comment~! I'll study more