CODEWITHSUNDEEP
pythondatetimeunixpandas
Sitz Blogz
Sitz Blogz

Reputation: 1061

Convert date-time format to Unix Time stamp Pandas

I am trying to convert the normal date-time to unix time stamp in pandas. while looking for some samples around I could only find one example here but I am not able to use in my context. The data set has no headers and the last 2 columns need to convert UNIX time stamp and generate a new output along with first 3 columns. 

1466f7b93975983f6e292a8a4faaa4b2,1619b4d0d283c0dddb17d24a359a3b49,36db348cde68592a31d502366fc52932,2010-03-08 17:09:00.472544,2010-03-12 16:09:58.122987
367c13356a5d22158f0ae56977134e2c,eedb7d0714796b64767a8710ea3844a7,925476200929fd346ea312cbe9a046fe,2010-03-08 17:08:29.174236,2010-03-12 16:09:58.122987
edf6b1e4f67b0e8a5080d299c9f9aeb2,7cb7681b90388a7522d0f06578591567,ffde0649a72ded8e33522c503a4d5cbe,2010-03-08 17:08:22.030524,2010-03-12 16:09:58.122987
6bb2ad8bc78897e99072d4d76cf0f19c,b644947ac4db03bdb518cfa71765f8c8,eb25089d396c06255cbb5f1bad801cc4,2010-03-08 17:07:55.819137,2010-03-12 16:09:58.122987

The input file has like millions of rows only few I have posted here. Any suggestion will be valuable. Thank in advance

Upvotes: 1

Views: 3031

Answers (2)

jezrael
jezrael

Reputation: 862691

You can first read_csv and then convert last two columns to np.int64 by astype divided by 10**9. For writing to file use to_csv:

import pandas as pd
import numpy as np
import io

temp=u"""1466f7b93975983f6e292a8a4faaa4b2,1619b4d0d283c0dddb17d24a359a3b49,36db348cde68592a31d502366fc52932,2010-03-08 17:09:00.472544,2010-03-12 16:09:58.122987
367c13356a5d22158f0ae56977134e2c,eedb7d0714796b64767a8710ea3844a7,925476200929fd346ea312cbe9a046fe,2010-03-08 17:08:29.174236,2010-03-12 16:09:58.122987
edf6b1e4f67b0e8a5080d299c9f9aeb2,7cb7681b90388a7522d0f06578591567,ffde0649a72ded8e33522c503a4d5cbe,2010-03-08 17:08:22.030524,2010-03-12 16:09:58.122987
6bb2ad8bc78897e99072d4d76cf0f19c,b644947ac4db03bdb518cfa71765f8c8,eb25089d396c06255cbb5f1bad801cc4,2010-03-08 17:07:55.819137,2010-03-12 16:09:58.122987"""

#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), 
                 header=None, #no header in csv
                 names=['a','b','c','d', 'e'], #set custom column names
                 parse_dates=['d','e']) #parse columns d, e to datetime
print df
                                   a                                 b  \
0  1466f7b93975983f6e292a8a4faaa4b2  1619b4d0d283c0dddb17d24a359a3b49   
1  367c13356a5d22158f0ae56977134e2c  eedb7d0714796b64767a8710ea3844a7   
2  edf6b1e4f67b0e8a5080d299c9f9aeb2  7cb7681b90388a7522d0f06578591567   
3  6bb2ad8bc78897e99072d4d76cf0f19c  b644947ac4db03bdb518cfa71765f8c8   

                                  c                          d  \
0  36db348cde68592a31d502366fc52932 2010-03-08 17:09:00.472544   
1  925476200929fd346ea312cbe9a046fe 2010-03-08 17:08:29.174236   
2  ffde0649a72ded8e33522c503a4d5cbe 2010-03-08 17:08:22.030524   
3  eb25089d396c06255cbb5f1bad801cc4 2010-03-08 17:07:55.819137   

                           e  
0 2010-03-12 16:09:58.122987  
1 2010-03-12 16:09:58.122987  
2 2010-03-12 16:09:58.122987  
3 2010-03-12 16:09:58.122987  


df['d'] = df['d'].astype(np.int64) // 10**9
df['e'] = df['e'].astype(np.int64) // 10**9

print df
                                  a                                 b  \
0  1466f7b93975983f6e292a8a4faaa4b2  1619b4d0d283c0dddb17d24a359a3b49   
1  367c13356a5d22158f0ae56977134e2c  eedb7d0714796b64767a8710ea3844a7   
2  edf6b1e4f67b0e8a5080d299c9f9aeb2  7cb7681b90388a7522d0f06578591567   
3  6bb2ad8bc78897e99072d4d76cf0f19c  b644947ac4db03bdb518cfa71765f8c8   

                                  c           d           e  
0  36db348cde68592a31d502366fc52932  1268068140  1268410198  
1  925476200929fd346ea312cbe9a046fe  1268068109  1268410198  
2  ffde0649a72ded8e33522c503a4d5cbe  1268068102  1268410198  
3  eb25089d396c06255cbb5f1bad801cc4  1268068075  1268410198  

df.to_csv('filename', header=None, index=False)

Upvotes: 3

piRSquared
piRSquared

Reputation: 294278

Unix date time is just the number of seconds since January 1, 1970.

So to ensure conversion from the correct date:

def dt2ut(dt):
    epoch = pd.to_datetime('1970-01-01')
    return (dt - epoch).total_seconds()

Then

import pandas as pd
import numpy as np
import io

temp=u"""1466f7b93975983f6e292a8a4faaa4b2,1619b4d0d283c0dddb17d24a359a3b49,36db348cde68592a31d502366fc52932,2010-03-08 17:09:00.472544,2010-03-12 16:09:58.122987
367c13356a5d22158f0ae56977134e2c,eedb7d0714796b64767a8710ea3844a7,925476200929fd346ea312cbe9a046fe,2010-03-08 17:08:29.174236,2010-03-12 16:09:58.122987
edf6b1e4f67b0e8a5080d299c9f9aeb2,7cb7681b90388a7522d0f06578591567,ffde0649a72ded8e33522c503a4d5cbe,2010-03-08 17:08:22.030524,2010-03-12 16:09:58.122987
6bb2ad8bc78897e99072d4d76cf0f19c,b644947ac4db03bdb518cfa71765f8c8,eb25089d396c06255cbb5f1bad801cc4,2010-03-08 17:07:55.819137,2010-03-12 16:09:58.122987"""
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), header=None, names=['a','b','c','d', 'e'])

df['d'] = df['d'].apply(dt2ut).astype(np.int64)
df['e'] = df['e'].apply(dt2ut).astype(np.int64)

Upvotes: 1

Related Questions