pandas adding data from index table to dataframe

Question

I have a pandas dataframe monthlyTempDiff with contents as:

monthlyAdder.ix[1:3]
         City1     City2    City3
monthID                              
1        0.01      0.1      0.02
2        0.04      0.2      0.03
8        0.17      0.3      0.05

Column monthID is also the index.

I have another table fwdTempTable

fwdTempTable.ix[1:3]
             City1     City2    City3      City4        monthID
DateTime                                                   
2017-1-01    22        24       26         16           1
2017-8-01    23        25       27         17           8
2017-2-01    13        15       17         27           2

I want to add to fwdTempTable, data from monthlyAdder based on combination of monthID and City1, City2, City3 to obtain the following:

fwdTempTable.ix[1:3]
             City1     City2    City3      City4        monthID
DateTime                                                   
2017-1-01    22.01     24.1     26.02      16           1
2017-8-01    23.17     25.3     27.05      17           8
2017-2-01    13.04     15.2     17.03      27           2

i.e. for each row of dataframe fwdTempTable, I extract the City1, City2 or City3 category and map it along with monthID to dataframe monthlyAdder. After mapping to monthlyAdder, I will get the right adder for a city and a month. I adjust the city data in fwdTempTable with this adder for that month.

These dataframes are large and I can't think beyond for loop on each column of fwdTempTable.

Scott Boston · Accepted Answer · 2017-09-18 18:15:54Z

2

Let's try set_index and add:

fwdTempTable.set_index('monthID', append=True).add(monthlyAdder, fill_value=0).reset_index('monthID')

Output:

           monthID  City1  City2  City3  City4
DateTime                                      
2017-1-01        1  22.01   24.1  26.02   16.0
2017-8-01        8  23.17   25.3  27.05   17.0
2017-2-01        2  13.04   15.2  17.03   27.0

answered Sep 18, 2017 at 18:15

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BENY · Accepted Answer · 2017-09-18 18:23:33Z

2

df1.reset_index().set_index('monthID')
cdx=df1.columns.isin(df.columns)    
df1.loc[:,cdx]=df1.loc[:,cdx].apply(lambda x:df.loc[x.index,x.name]+x )
df1.reset_index().set_index('DateTime')

Out[215]: 
           monthID  City1  City2  City3  City4
DateTime                                      
2017-1-01        1  22.01   24.1  26.02     16
2017-8-01        8  23.17   25.3  27.05     17
2017-2-01        2  13.04   15.2  17.03     27

edited Sep 18, 2017 at 18:23

answered Sep 18, 2017 at 18:17

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

piRSquared · Accepted Answer · 2017-09-18 18:28:27Z

1

fwdTempTable.add(
    fwdTempTable[['monthID']].join(monthlyAdder, on='monthID'), fill_value=0)

           City1  City2  City3  City4  monthID
DateTime                                      
2017-1-01  22.01   24.1  26.02   16.0        2
2017-8-01  23.17   25.3  27.05   17.0       16
2017-2-01  13.04   15.2  17.03   27.0        4

answered Sep 18, 2017 at 18:28

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

pandas adding data from index table to dataframe

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related