datetime.strptime extracting from dataframe: error
Question: Strptime doesn't do what I (starting Python data-scientist) want it to do.
I've got a data file formatted as follows:

    
    
    STN,YYYYMMDD,HH,RH
    210,20121001,1,0
    210,20121001,2,0
    210,20121001,3,0
    210,20121001,4,0
    ...
    

In the second column the dates, in the third the hour (1-24) of an
observation. I'd like to get the date and time in a datetime format to merge
it with other data. My try:

    
    
    import pandas as pd
    from datetime import datetime
    
    meteo = pd.read_csv("x:\\hourly.txt", parse_dates=[[1,2]])
    # dataframe created with a column 'YYYYMMDD_HH'
    meteo['datetime']=meteo['YYYYMMDD_HH'].apply(lambda x: datetime.strptime(x,'%Y%m%d %H'))
    

Python crashes on the last line with a (for me) very cryptic error:

    
    
    Traceback (most recent call last):
      File "X:\test.py", line 11, in <module>
        meteo['datetime']=meteo['YYYYMMDD_HH'].apply(lambda x: datetime.strptime(x,'%Y%m%d %H'))
      File "C:\Program Files\Anaconda3\lib\site-packages\pandas\core\series.py", line 1998, in apply
        mapped = lib.map_infer(values, f, convert=convert_dtype)
      File "inference.pyx", line 1016, in pandas.lib.map_infer (pandas\lib.c:53184)
      File "X:\test.py", line 11, in <lambda>
        meteo['datetime']=meteo['YYYYMMDD_HH'].apply(lambda x: datetime.strptime(x,'%Y%m%d %H'))
      File "C:\Program Files\Anaconda3\lib\_strptime.py", line 500, in _strptime_datetime
        tt, fraction = _strptime(data_string, format)
      File "C:\Program Files\Anaconda3\lib\_strptime.py", line 340, in _strptime
        data_string[found.end():])
    ValueError: unconverted data remains: 4
    

What am I doing wrong? Thanks for your help in advance, Niels


Answer: Parse the columns when you read the CSV file.

    
    
    import pandas as pd
    from datetime import datetime
    
    parse = lambda x: datetime.strptime(x, '%Y%m%d %H')
    df = pd.read_csv("time.csv", parse_dates = [['YYYYMMDD', 'HH']], date_parser=parse)
    
    print df
    

Output:

    
    
              YYYYMMDD_HH  STN  RH
    0 2012-10-01 01:00:00  210   0
    1 2012-10-01 02:00:00  210   0
    2 2012-10-01 03:00:00  210   0
    3 2012-10-01 04:00:00  210   0
    

