[FIXED] How can I add results of a for loop with an if statement to my dataframe?

Issue

I am new to python and I am having trouble with my loop/appending my results back to my original dataframe. Basically, I have a csv I am reading into python that has times of fish detection. I want to be able to classify the fish detection as either Day or Night. I am using the package Astral with an if statement, to show if the fish was detected at 'Day' or 'Night' depending on sunrise/sunset for the specific location and time of the year.

1. I am not sure if I have over-complicated my loop but after checking with NOAA it looks like the day/night times are correct. How do I add this data from the if statement back into my original dataframe that just has DetectTime?

2. If I want to add another for loop for an additional column, using dusk and dawn from Astral, how can I add it to the dataframe so the final df is DetectTime, DayNight, and DawnDusk? TYIA!

import astral
from astral import sun
import pandas as pd


df = pd.read_csv("fishdata.csv", sep = '\t')


obs = astral.Observer(latitude = 30.128, longitude = -115.455, elevation = 0.0)

df['DetectTime'] = pd.to_datetime((df['DetectTime']))
df.DetectTime= df.DetectTime.dt.tz_localize('UTC').dt.tz_convert('Etc/GMT+8')
df = pd.DataFrame(df)


for data in df.DetectTime:
    date = pd.to_datetime(data.strftime("%m/%d/%Y %H:%M"))
    daylight = pd.to_datetime((sun.daylight(obs, date, tzinfo = 'Etc/GMT+8')))
    if pd.to_datetime(daylight[0].strftime("%m/%d/%Y %H:%M")) <= date < pd.to_datetime(daylight[1].strftime("%m/%d/%Y %H:%M")):
            print(date, 'Day')
    else: print(date, 'Night')

Solution

You don't need to use a for loop. Try using apply().

Method1:

# Create sample data
df = pd.DataFrame([pd.Timestamp('2014-01-23 00:00:00', tz='UTC'), pd.Timestamp('2014-01-23 12:00:00', tz='UTC')], columns=['DetectTime'])
df.DetectTime = df.DetectTime.dt.tz_convert('Etc/GMT+8')

# Determine day or night
df['day_or_night'] = df.DetectTime.apply(lambda date: 'Day' if sun.daylight(obs, date, tzinfo = 'Etc/GMT+8')[0] <= date.to_pydatetime() < sun.daylight(obs, date, tzinfo = 'Etc/GMT+8')[1] else 'Night')

Output:

print(df)

DetectTime  day_or_night
0   2014-01-22 16:00:00-08:00   Day
1   2014-01-23 04:00:00-08:00   Night

Method 2:
Another method is to separate sun.daylight into two columns and then use eval(). This method produces the same result but makes your codes easier to read.

df['range1'] = df.DetectTime.apply(lambda x: sun.daylight(obs, x, tzinfo = 'Etc/GMT+8')[0])
df['range2'] = df.DetectTime.apply(lambda x: sun.daylight(obs, x, tzinfo = 'Etc/GMT+8')[1])
df['day_or_night'] = df.eval('range1 <= DetectTime < range2')

Output:

print(df)

    DetectTime  range1  range2  day_or_night
0   2014-01-22 16:00:00-08:00   2014-01-22 06:37:10.514609-08:00    2014-01-22 17:10:03.436729-08:00    True
1   2014-01-23 04:00:00-08:00   2014-01-23 06:36:49.146199-08:00    2014-01-23 17:10:56.041892-08:00    False

Answered By - Crystal L

Answer Checked By - Marilyn (PHPFixing Volunteer)