0

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.

its_me
  • 5
  • 3

1 Answers1

0

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
Naveed
  • 11,495
  • 2
  • 14
  • 21
  • Great! Thanks a lot! I had tried a few things, but I couldn't come up with a brilliant idea. Now it looks so easy... – its_me Jul 27 '22 at 12:32