2

I'm working with a .sav (SPSS) file in Python. All the variables look fine after import while using PyreadStat (also when using Pandas) except for the datetime variables. They read in as exponential numbers of type float using Python. But their original SPSS format is dd-mmm-yy (e.g., 02-feb-2021) of type date.

This is how the date variable looks like

1.383160e+10

Is there a way to convert this format to datetime using Python?

I've tried various ways of using the datetime module and time module. But what I get is a date from the year 2408

 # Here I'm using the float from the first row in the dataframe  
 time.gmtime(13831603200)

The results

time.struct_time(tm_year=2408, tm_mon=4, tm_mday=22, tm_hour=0, tm_min=0, tm_sec=0, tm_wday=1, tm_yday=113, tm_isdst=0)

When I use the datetime module:

python_date = datetime.fromtimestamp(13831603200).strftime('%d-%b-%Y, %H:%M:%S')
print(python_date)

22-Apr-2408, 00:00:00

[How the datetime variable (Vdatesub) is showing when using Python][1] [1]: https://i.stack.imgur.com/I7yza.png

Steinunn Sigurjónsdóttir
  • 23
  • 3
  • Does this answer your question? [Convert 'seconds since October 14, 1582' to Python datetime](https://stackoverflow.com/questions/51502602/convert-seconds-since-october-14-1582-to-python-datetime) – Alan Sep 12 '22 at 11:46
  • Nit: these things you're trying to read aren't variables but values. A variable is the name given to a value. – erip Sep 12 '22 at 11:51

1 Answers1

1

This is answered under these two posts (one Python, one R):

Convert 'seconds since October 14, 1582' to Python datetime

Read SPSS file into R, the data format for date is wrong, and generate more variable

In short: the date is stored as number of seconds from 14 Oct 1582, while Python starts at the Epoch date (01 Jan 1970).

You would need to calculate the number of seconds between 1582-10-14 and 1970-01-01 to adjust the timestamp value as per this post:

Timestamp out of range for platform localtime()/gmtime() function

(Possibly 12,218,515,200 seconds)

Alan
  • 2,914
  • 2
  • 14
  • 26
  • Thank you this ended up working: pd.to_datetime(((13831603200/86400)-141428), unit = 'D') and to convert the whole column: df['Vdatesub'] = pd.to_datetime(((df['Vdatesub']/86400)-141428), unit = 'D') – Steinunn Sigurjónsdóttir Sep 13 '22 at 10:00