2
\$\begingroup\$

I have a pandas dataframe, my_calculation, that is a Cartesian product of 4 different categories (Name, Freqset, Formula, and Location) and two additional Address categories. There are 3 different names, 12 different frequency sets, 14 different formulas, and 13 different locations making 6552 rows. There can be any number of possible addresses (their possible states are stored in a different frame), but in this frame, for any location there is only one possible Address1 and one possible Address2.

0    Name   Freqset    Formula    Location    Address1    Address2
1    Jeff   freqset1   form1      New York    Box 12      Box 15
2    Jeff   freqset1   form1      Buffalo     Box 60      Box 11
3    Jeff   freqset1   form1      Miami       Box 10      Box 80
4...............................................................
................................................................
6551 Leroy  freqset12  form14     Charleston  Box 100     Box 28

I only made the dataframe in this manner because that's how I would logically set it up in a db table for maintainability. If there is a more efficient structure for this dataframe, or an easier structure to work with, please let me know.

I need to add a column for frequencies for each address (Freq1 for Address1 and Freq2 for Address2) and then a column for a probability calculation I will call Calculation Result that is based on the Formula column (I haven't addressed this yet). I want to use this frame as an index to do what will amount to millions of additional calculations. My thinking is that a dataframe of values to choose millions of results from SHOULD be more efficient than doing millions of calculations on the fly. If this is flawed thinking, please let me know.

The frequencies are stored in a dictionary of dataframes. An example of one dataframe:

    Address    New York    Buffalo    Miami
0   Box 1      0.24560     0.95000    0.25000
1   Box 2      0.00100     0.45190    0.65091
.............................................   
n   Box n      0.45450     0.20341    0.11110

They are accessed like this:

freq_dict.freq['Freqset1']
freq_dict.freq['Freqset2']

etc...

The frequencies come out of excel automatically in this structure and I am unsure if any manipulation of this structure from its current state will make things any easier or more efficient.

Here is the only way I can get the Freq1 and Freq2 columns in my_calculation to populate. I'm almost positive this isn't the way to go about things.

import numpy as np
import pandas as pd

#arrays of Cartesian categories
names = np.array(['Jeff', 'Jenn', 'Leroy'])

locations = np.array(['New York', 'Buffalo', 'Miami', 'Tampa', 'Boston',
                     'Pittsburgh', 'Portland', 'Seattle', 'Toronto',
                     'Witchita', 'Austin', 'Bangor', 'Charleston'])

freqsets = np.array(['freqset1','freqset2','freqset3','freqset4',
                      'freqset5','freqset6','freqset7','freqset8',
                      'freqset9','freqset10','freqset11','freqset12'])

formulas = np.array(['form1', 'form2', 'form3', 'form4', 'form5',
                     'form6', 'form7', 'form8', 'form9', 'form10',
                     'form11', 'form12', 'form13', 'form14'])

temp_arr1 = np.zeros((6552, ))
temp_arr2 = np.zeros((6552, ))
i = 0

for name in names:
    for location in locations:
        for freqset in freqsets:
            for formula in formulas:
                temp_arr1[i] = freq_dict.freq[freqset].query('Address ==' + my_calculation['Address1'][i])[my_calculation['Location'][i]].item()
                temp_arr1[i] = freq_dict.freq[freqset].query('Address ==' + my_calculation['Address2'][i])[my_calculation['Location'][i]].item()
                i+=1

my_calculation['Freq1'] = pd.Series(temp_arr1)
my_calculation['Freq2'] = pd.Series(temp_arr2)

Now my_calculation dataframe looks like this (last column not part of code yet):

0    Name   Freqset    Formula    Location    Address1    Address2    Freq1     Freq2     Calculation Result
1    Jeff   freqset1   form1      New York    Box 12      Box 15      0.00020   0.23140   TBD   
2    Jeff   freqset1   form1      Buffalo     Box 60      Box 11      0.05121   0.15432   TBD   
3    Jeff   freqset1   form1      Miami       Box 10      Box 80      0.12120   0.54230   TBD   
4......................................................................................
.......................................................................................
6551 Leroy  freqset12  form14     Charleston  Box 100     Box 28      0.32001   0.00023   TBD   

In the long run I want to be able to create this table quickly because I want to do it thousands of times. Right now it takes about 28 seconds on my machine to do just one, and I have yet to populate a Calculation Result column.

Let me know if something doesn't quite run correctly. I am transcribing code across machines and can correct easily.

\$\endgroup\$
1
  • \$\begingroup\$ Before this gets away from me, I already see one place I can improve. I'm doing 6552 queries of the dictionary. I should be able to reduce this to 312. (ie 12 freqsets x 26 addresses) \$\endgroup\$ – Dan Sep 9 '20 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.