Bdtjtk

I have a csv file that contains a column of data where each value is an integer meant to represent the hour and minute in a day. The problem is that each value does not follow the same format. If it is between 12:00 AM and 12:10 AM the value will just be one digit, the minute. If it is between 12:10 AM and 1:00 AM, the value will have to digits, again the minute. If it is between 1:00 AM and 10:00 AM, the value will have three digits, the hour and minute. Finally, for all other values (those between 10:00 AM and 12:00 AM, the value will have four digits, again the hour and minute.

I tried using the pandas, "to_datetime" function to operate on the whole column.

from pandas import read_csv, to_datetime

url = lambda year: f'ftp://sidads.colorado.edu/pub/DATASETS/NOAA/G00807/IIP_{year}IcebergSeason.csv'

df = read_csv(url(2011))

def convert_float_column_to_int_column(df, *column_names):

    for column_name in column_names:

        try:

            df[column_name] = df[column_name].astype(int)

        except ValueError:

            df = df.dropna(subset=[column_name]).reset_index(drop=True)

            df[column_name] = df[column_name].astype(int)

    return df

df2 = convert_float_column_to_int_column(df, 'ICEBERG_NUMBER', 'SIGHTING_TIME')

df2['SIGHTING_TIME'] = to_datetime(df2['SIGHTING_TIME'].astype(str), format='%H%M')

The result I got was:

ValueError: time data '0' does not match format '%H%M' (match). 

Which was as expected.

I'm sure I could work around this problem by iterating through each row, using if statements, and converting each value to a four character string but these files are relatively big so that would be too slow of a solution.

No need for if statements. Series.str.zfill will pad it with the correct number of zeros to get it in the proper format. Then use pd.to_datetime, subtracting off 1900-01-01 which is the date it will use when none of those fields are present:

Input Data

import pandas as pd

df = pd.DataFrame({'Time': [1, 12, 123, 1234]})

#   Time

#0     1

#1    12

#2   123

#3  1234

pd.to_datetime

df['Time'] = (pd.to_datetime(df.Time.astype(str).str.zfill(4), format='%H%M') 

              - pd.to_datetime('1900-01-01'))



#0   00:01:00

#1   00:12:00

#2   01:23:00

#3   12:34:00

#Name: Time, dtype: timedelta64[ns]

pd.to_timedelta

Can also be used, but since you cannot specify a format parameter you need to clean everything beforehand:

df['Time'] = df.Time.astype(str).str.zfill(4)



# Pandas .str methods are slow, use a list comprehension to speed it up

#df['Time'] = df.Time.str[0:2] + ':' + df.Time.str[2:4] + ':00'    

csize=2

df['Time'] = [':'.join(x[i:i+csize] for i in range(0, len(x), csize))+':00' for x in df.Time.values]



df['Time'] = pd.to_timedelta(df.Time)



#0   00:01:00

#1   00:12:00

#2   01:23:00

#3   12:34:00

#Name: Time, dtype: timedelta64[ns]