How to map data from one dataframe into a column in another dataframe?

user_10525916 0
Viewed 61

I have two separate dataframes representing different types of time-based data. One contains hundreds of thousands of timestamps spread across several months. This dataframe has columns representing the month of the year, the time of the day, and the measured temperature. The second dataframe consists of replacement temperatures for each month/hour combination. The data looks roughly as follows:

df1

Timestamp Month Hour Temperature
1/1/2021 00:00:00 1 0 10
1/1/2021 00:00:05 1 0 11
1/1/2021 00:00:07 1 0 8
1/1/2021 00:00:15 1 0 12
1/1/2021 00:01:00 1 1 13

etc.

df2

Hour Jan Feb Mar Apr etc
0 9 12 10 12 etc
1 10 11 14 15 etc
2 8 7 12 16 etc

df2 contains a row for each hour of the day, and a column for each month of the year (In the real data set the months are numeric, I wrote the names to make the description clear).

I need to map the data contained for the month/hour in df2 to the Temperature column in df 1. So df1, after editing, should appear as follows.

New df1

Timestamp Month Hour Temperature
1/1/2021 00:00:00 1 0 9
1/1/2021 00:00:05 1 0 9
1/1/2021 00:00:07 1 0 9
1/1/2021 00:00:15 1 0 9
1/1/2021 00:01:00 1 1 10

I have made it work using a nested for loop, as follows:

for month in df2.columns:
    for hour in df2.index:
        dT = df2.loc[hour, month]
        df1.loc[(df1['Month'] == month) & (df1['Hour'] == hour), 'Temperature'] = dT

The nested for loop cycles through all months and hours in df2, locates the cells in df1 with the matching month and hour, then sets the temperature equal to the temperature read out of df2.

But this code is both hard to read and super slow to execute. Does somebody know a better way??

Thanks!

pandas dataframe python
1 Answers

Let's call df1 and df2 your two dataframes in order.

First, make sure Timestamp is datetime and extract the month short name (we could also map the Month number to short name)

df1['Timestamp'] = pd.to_datetime(df1['Timestamp'])
df1['Month_name'] = df1['Timestamp'].dt.month_name().str[:3]

NB. if speed is critical, then skip the above step and rework your df2 to have months numbers in place of months names as columns, then in the final step use the "Month" column of df1 to perform the merge

Then rework df2 to unstack the months (this can be applied directly in the next step or saved in a variable):

df2.set_index('Hour').unstack().rename('Temperature')

Finally, merge the two:

df1.merge(df2.set_index('Hour').unstack().rename('Temperature'),
          left_on=['Month_name', 'Hour'],
          right_index=True,
          suffixes=('_old', ''),
         )

output:

            Timestamp  Month  Hour  Temperature_old Month_name  Temperature
0 2021-01-01 00:00:00      1     0               10        Jan            9
1 2021-01-01 00:00:05      1     0               11        Jan            9
2 2021-01-01 00:00:07      1     0                8        Jan            9
3 2021-01-01 00:00:15      1     0               12        Jan            9
4 2021-01-01 00:01:00      1     1               13        Jan           10

If you want to keep only the new temperatures, add .drop(['Month', 'Temperature_old'], axis=1)

user_163434640
Related