CODEWITHSUNDEEP
pythonpandas
Travis Leleu
Travis Leleu

Reputation: 4240

Normalizing data in data frame with data from different data frame

I'm analyzing some federal data that is aggregated on a state-by-state basis. The row labels are state names, column labels are the facet labels. The cell values are the population counts. This df looks like:

+---------------------------------------+
|                            f1     f2  |
| Alabama                    127    321 |
| Alaska                      84    12  |
| Arizona                    295    12  |
| Arkansas                    82    71  |
+---------------------------------------+

I have another data frame where the rows are also state names, but the single column is the population of that state. This df looks like:

+--------------------------------+
|                          Counts|
| Alabama                    146 |
| Alaska                      89 |
| Arizona                    314 |
| Arkansas                    85 |
+--------------------------------+

I would like to divide every column in the first data frame by the values in the second data frame. This normalizes state incidence data by population.

How can I accomplish this in pandas? I'm experienced in python, but a pandas newbie, and can't figure it out.

TIA, Travis

Upvotes: 1

Views: 96

Answers (1)

behzad.nouri
behzad.nouri

Reputation: 78011

use .div with axis=0:

>>> left
           f1   f2
Alabama   127  321
Alaska     84   12
Arizona   295   12
Arkansas   82   71
>>> right
          Counts
Alabama      146
Alaska        89
Arizona      314
Arkansas      85
>>> left.div(right['Counts'], axis=0)
             f1     f2
Alabama   0.870  2.199
Alaska    0.944  0.135
Arizona   0.939  0.038
Arkansas  0.965  0.835

note that this aligns the right hand side to the index of left hand side, so the result of below would be the same as above:

>>> left.div(right.loc[::-1, 'Counts'], axis=0)  # permute the indices
             f1     f2
Alabama   0.870  2.199
Alaska    0.944  0.135
Arizona   0.939  0.038
Arkansas  0.965  0.835

Upvotes: 1

Related Questions