pandas: convert time to timezone into new column-CodePudding

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.

CodePudding user response：

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