0

I have two spreadsheets. One of them is about 20000 rows of patients entering an emergency room. Each row has a date/time column, along with separate date and separate time columns. Clearly multiple patients enter on the same day. The other spreadsheet has 366 dates and times of sunrises and sunsets for each day covered in the first spreadsheet.

What I want to do is create a column in the first spreadsheet called "is_night" and code each entry as a 0 if it's daytime (i.e. the time the patient entered is greater than the time of sunrise and less than the time of sunset corresponding to the patient's day of entry), and as a 1 if it's nighttime (the "else" condition)

What would be the simplest way to go about doing this? Doing time comparison is a pain in Excel and Python is giving me issues with parsing. Note that I have access to Python 3 (with Pandas), R, Excel, and Access. Thanks!

EDIT: Here are my column headings for each spreadsheet:

Patient Data:  row_id   day_of_week date_in time_in date_out    time_out    complaint   mdassigned  acuity_id   elapsed_time    triage  disp_desc   adm_dec adm_delay   diagnosis   icd date_time_in    date_time_out

Sun Data:  day_number   sunrise_time    sunset_time season  month   day year    is_dst  sunrise_time_dst    sunset_time_dst date    sunrise_date_time   sunset_date_time    sunrise_date_time_dst   sunset_date_time_dst
Brandon Sherman
  • 673
  • 1
  • 8
  • 25
  • Get the date/time info from the patient file, look up the date in the astronomical file, compare the patient time to the sunrise and sunset time. – wwii Jul 02 '14 at 14:05
  • Thanks! But how would I code that in an Excel cell that I could then apply for every cell for each of the 366 dates? – Brandon Sherman Jul 02 '14 at 14:10
  • I have some Python code that fairly accurately computes the time of sunrise and sunset for a particular location given its geographic coordinates (longitude and latitude), which sounds like it might be useful. The current code was derived from even older public domain code I found on the web years ago, and I think I've posted it in a answer here somewhere if you're interested. LMK if you want more info. – martineau Jul 02 '14 at 14:25
  • That could be good, thanks! I got my data from the Navy's website. It was in text format, so I saved it as a .txt and imported it into an Excel spreadsheet. Then I separated it into dates and times based on the date range of my original data set. pnuts, I'm updating my original post with the column headings for each spreadsheet. – Brandon Sherman Jul 02 '14 at 14:26
  • I used to do something similar from the Navy website data, but now compute it dynamically in code. Here' a link to [my answer](http://stackoverflow.com/questions/15044521/javascript-or-python-how-do-i-figure-out-if-its-night-or-day/15044612#15044612) to a question titled [_Javascript or Python - How do I figure out if it's night or day?_](http://stackoverflow.com/questions/15044521/javascript-or-python-how-do-i-figure-out-if-its-night-or-day) – martineau Jul 02 '14 at 14:28
  • Just to confirm, B1 and C1 are in the original spreadsheet of patient information? – Brandon Sherman Jul 02 '14 at 14:43

2 Answers2

0

you could use pandas, and try something like this. don't know your variables and details, but the code shown below should be understandable, and you can apapt it to your particular situation

# dates_df is a file with dates and sunrise and sunset times
# patiets_df is a file with patiens data

def get_sunrise(x):
    sunrise = dates_df[dates_df['date'] = x['date']].sunrise_time
    return sunrise

def get_sunset(x):
    sunset = dates_df[dates_df['date'] = x['date']].sunset_time
    return sunset

# add columns for sunrise and sunset to patients_df

patients_df['sunrise']=patients_df.apply(get_sunrise, axis = 1)
patients_df['sunset']=patients_df.apply(get_sunset, axis = 1)

# checking every admission time from patients_df if it is after sunset and before sunrise
patients_df['is_night'] = patients_df.apply(lambda x: x.time < x.sunrise and x.time > x.sunset, axis=1)
yemu
  • 26,249
  • 10
  • 32
  • 29
  • That actually looks really good, but I don't think the code compiles right. But that's what I'm looking to do. By the way, can you have multiple assignment statements in one line like that in Python? – Brandon Sherman Jul 02 '14 at 14:36
  • I haven't tested it, cause I don't have your data :( It may not work, that's true ;) please include sample data in your question if you can. – yemu Jul 02 '14 at 18:40
0

I figured it out. I loaded both tables into Access, did a join based on the date, then wrote a quick if statement in Excel and it worked.

Brandon Sherman
  • 673
  • 1
  • 8
  • 25
  • nice, that's a good idea, I also thought about the join on the date using pandas :) – yemu Jul 03 '14 at 07:23