Is it possible to set a custom delimiter/separator for a specific column when cleaning data with pandas?

user_12326879 0
Viewed 194

My dataset is a .txt file separated by colons (:). One of the columns contains a date AND time, the date is separated by backslash (/) which is fine. However, the time is separated by colons (:) just like the rest of the data which throws off my method for cleaning the data.

Example of a couple of lines of the dataset:

USA:Houston, Texas:05/06/2020 12:00:00 AM:car
Japan:Tokyo:05/06/2020 11:05:10 PM:motorcycle
USA:Houston, Texas:12/15/2020 12:00:10 PM:car
Japan:Kyoto:01/04/1999 05:30:00 PM:bicycle

I'd like to clean the dataset before loading it into a dataframe in python using pandas. How do I separate the columns? I can't use

df = pandas.read_csv('example.txt', sep=':', header=None)

because that will separate the time data into different columns. Any ideas?

pandas data-cleaning dataframe python-3.x
2 Answers

You can concatenate the columns back:

df = pandas.read_csv("example.txt", sep=":", header=None)

df[6] = pd.to_datetime(
    df[2].astype(str) + ":" + df[3].astype(str) + ":" + df[4].astype(str)
)

df = df[[0, 1, 6, 5]].rename(
    columns={0: "State", 1: "City", 6: "Time", 5: "Type"}
)
print(df)

Prints:

   State            City                Time        Type
0    USA  Houston, Texas 2020-05-06 00:00:00         car
1  Japan           Tokyo 2020-05-06 23:05:10  motorcycle
2    USA  Houston, Texas 2020-12-15 12:00:10         car
3  Japan           Kyoto 1999-01-04 17:30:00     bicycle
user_100359850 
pd.read_csv(file, sep=r"(?:(?<!\d):(?!\d)?)", header=None)

See demo for the regex!

Regex is looking for a non-digit behind the : via negative look behind ?<! and possibly a non-digit after : via negative look ahead. The latter is optional to cover the case e.g. Tokyo:05 and split here also. The ?: at the beginning says "don't keep the :'s you find in the result".

I get:

       0               1                       2           3
0    USA  Houston, Texas  05/06/2020 12:00:00 AM         car
1  Japan           Tokyo  05/06/2020 11:05:10 PM  motorcycle
2    USA  Houston, Texas  12/15/2020 12:00:10 PM         car
3  Japan           Kyoto  01/04/1999 05:30:00 PM     bicycle
user_93321870
Related