5

Here is the head of my first dataframe, A1:

            K37L  K37M  K37N  K37P  K37Q  K37R  K37S  K37T  K37U  K37V  ...   \
1997-01-01  79.8  80.4  72.8  36.7   0.0  90.0  96.9  92.2  79.8  93.7  ...    
1997-02-01  79.1  81.7  73.6  36.7   0.0  90.2  97.4  92.2  80.3  93.5  ...    
1997-03-01  79.2  80.8  73.2  37.0   0.0  90.3  97.5  92.0  80.4  93.2  ...    

            K385  K386   K387  K388  K389  K38A  K38B  K38C  K38D  K38E  
1997-01-01  67.4  79.1  227.0  83.8  82.1  94.0  74.2  88.4  81.1  73.9  
1997-02-01  67.5  79.2  220.9  83.9  82.1  94.2  74.5  88.5  81.1  74.3  
1997-03-01  67.5  79.3  218.9  84.0  82.2  94.3  74.7  88.7  81.1  74.4  

[3 rows x 27 columns]

Here is the head of my second dataframe, A2:

            K37L  K37M  K37N  K37P  K37Q  K37R  K37S  K37T  K37U  K37V  ...   \
1996-01-01  78.9  79.4  71.7  36.7   0.0  88.7  94.1  90.7  80.2  98.9  ...    
1996-02-01  79.3  81.0  72.7  36.7   0.0  88.7  94.3  90.9  79.8  98.7  ...    
1996-03-01  79.8  80.4  72.7  36.7   0.0  89.0  94.6  91.0  79.6  98.6  ...    

            K385  K386   K387  K388  K389  K38A  K38B  K38C  K38D  K38E  
1996-01-01  70.9  78.7  257.8  83.9  79.7  92.2  73.8  86.4  79.6  74.0  
1996-02-01  70.7  78.7  257.2  83.9  79.8  92.6  73.7  86.6  79.9  73.9  
1996-03-01  70.9  78.7  257.3  83.9  80.1  92.6  73.8  87.2  80.1  74.0  

[3 rows x 27 columns]

What I want to do is: A2 / (A1 - 1) * 100

The most important part is that I want to divide all the values in A1 with A2.

My attempt:

A3 = A2 / (A1-1)*100
print(A3.head(3))

Yields:

            K37L  K37M  K37N  K37P  K37Q  K37R  K37S  K37T  K37U  K37V  ...   \
1996-01-01   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...    
1996-02-01   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...    
1996-03-01   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  ...    

            K385  K386  K387  K388  K389  K38A  K38B  K38C  K38D  K38E  
1996-01-01   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  
1996-02-01   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  
1996-03-01   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN  

[3 rows x 27 columns]

Is it possible to divide an entire dataframe (its values) from another dataframe? Or am I going to have to construct some sort of complex loop to achieve this?

DanONS
  • 195
  • 1
  • 2
  • 7

2 Answers2

7

This is because pandas will match by index (here by date) in order to apply the division. The dates don't match so no operation will take place.

You can try this:

A3 = A2.reset_index(drop=True) / (A1.reset_index(drop=True) - 1) * 100

You can then reapply whichever index you wish:

A3.index = A1.index  # for instance
IanS
  • 15,771
  • 9
  • 60
  • 84
  • 1
    Thanks, that was really helpful and you also explained why I couldn't get it to work. I tried your method and it worked excellently – DanONS Sep 22 '17 at 11:07
6

Indexes are not match, so get NaNs

One possible solution is divide by numpy array, if both df have same size:

A3 = A2 / (A1.values-1)*100
print(A3.head(3))
                  K386        K387        K388       K389       K38A  \
1996-01-01  100.768246  114.070796  101.328502  98.273736  99.139785   
1996-02-01  100.639386  116.962256  101.206273  98.397041  99.356223   
1996-03-01  100.510856  118.081689  101.084337  98.645320  99.249732   

                  K38B       K38C        K38D        K38E  
1996-01-01  100.819672  98.855835   99.375780  101.508916  
1996-02-01  100.272109  98.971429   99.750312  100.818554  
1996-03-01  100.135685  99.429875  100.000000  100.817439
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thanks Jezrael for the explanation, I tried your method too and it also solves the issue :) – DanONS Sep 22 '17 at 11:11