In a dataframe I'm trying to calculate a third column row wise from a time value column and one with a time zone. Unfortunately without success.
df.time_gmt = pd.DatetimeIndex(df.time_gmt, tz='utc')
#given #given #expected result
time_gmt tz_id tz_time
0 2022-03-31 06:53:53.796000+00:00 NaN NaT
1 2022-03-31 07:09:00.903000+00:00 Europe/Zurich 2022-03-31 09:09:00.903000+02:00
2 2022-03-31 07:09:50.627000+00:00 Asia/Seoul 2022-03-31 16:09:50.627000+09:00
3 2022-03-31 07:22:07.028000+00:00 Europe/Vilnius 2022-03-31 10:22:07.028000+03:00
I can use "tz_convert" to get the new value only for a fixed timezone. e.g.:
df['tz_time'] = df['time_gmt'].dt.tz_convert('Europe/Vilnius')
But I can't manage to create the value dynamically from the corresponding row in >df.tz_id< with something like:
df['tz_time'] = df['time_gmt'].dt.tz_convert(df['tz_id'])
df['tz_time'] = df[pd.notna(df.tz_id)]['time_gmt'].apply(lambda x: x.tz_convert(df[pd.notna(df.tz_id)]['tz_id']))
Now I'm at the end of my wisdom. Any useful idea will be highly appreciated.
here is one way to do it
convert the time to a datetime and then use apply.
df['time_gmt']=pd.to_datetime(df['time_gmt'])
df['converted_time']= df[~df['tz_id'].isna()].apply(lambda x: x['time_gmt'].tz_convert(x['tz_id'] ), axis=1)
df
time_gmt tz_id tz_time converted_time
0 2022-03-31 06:53:53.796000+00:00 NaN NaT NaN
1 2022-03-31 07:09:00.903000+00:00 Europe/Zurich 2022-03-31 09:09:00.903000+02:00 2022-03-31 09:09:00.903000+02:00
2 2022-03-31 07:09:50.627000+00:00 Asia/Seoul 2022-03-31 16:09:50.627000+09:00 2022-03-31 16:09:50.627000+09:00
3 2022-03-31 07:22:07.028000+00:00 Europe/Vilnius 2022-03-31 10:22:07.028000+03:00 2022-03-31 10:22:07.028000+03:00