2

I am trying to create a new column in a Pandas dataframe using multiple conditional statements based on other info within the dataframe. I have tried iterating using .iteritems(). This works, but seems inelegant and returns a notice that I don't know how to understand and/or correct.

My code snippet is:

proj_file_pq['pd_pq'] = 0

for key, value in proj_file_pq['pd_pq'].iteritems():

    if proj_file_pq['qualifying'].iloc[key] - \
        proj_file_pq['avg_pd'].iloc[key] < 1:
        proj_file_pq['pd_pq'].iloc[key] = \
            proj_file_pq['qualifying'].iloc[key] - 1

    elif proj_file_pq['qualifying'].iloc[key] > \
        proj_file_pq['avg_start'].iloc[key]:
        proj_file_pq['pd_pq'].iloc[key] = \
            proj_file_pq['qualifying'].iloc[key] - \
                proj_file_pq['avg_finish'].iloc[key]

    elif proj_file_pq['qualifying'].iloc[key] + \
        proj_file_pq['avg_pd'].iloc[key] > 40:
        proj_file_pq['pd_pq'].iloc[key] = \
            40 - proj_file_pq['qualifying'].iloc[key]

    else:
        proj_file_pq['pd_pq'].iloc[key] = proj_file_pq['avg_pd'].iloc[key]

print(proj_file_pq[['Driver', 'avg_start', 'avg_finish', 'qualifying',\
                     'avg_pd', 'pd_pq']].head())

And here is the resulting output:

C:\Python36\lib\site-packages\pandas\core\indexing.py:189: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self._setitem_with_indexer(indexer, value)
              Driver  avg_start  avg_finish  qualifying  avg_pd  pd_pq
0  A.J. Allmendinger     18.000      21.875          16   3.875  3.875
1        Alex Bowman     14.500      18.000           8   3.500  3.500
2      Aric Almirola     21.250      19.250          13  -2.000 -2.000
3      Austin Dillon     18.875      18.375          17  -0.500 -0.500
4        B.J. McLeod     33.500      33.500          36   0.000  2.500

The original dataframe has the following head:

{'Driver': {0: 'A.J. Allmendinger', 1: 'Alex Bowman', 2: 'Aric Almirola', 3: 'Austin Dillon', 4: 'B.J. McLeod'}, 'qualifying': {0: 16, 1: 8, 2: 13, 3: 17, 4: 36}, 'races': {0: 8, 1: 6, 2: 8, 3: 8, 4: 2}, 'avg_start': {0: 18.0, 1: 14.5, 2: 21.25, 3: 18.875, 4: 33.5}, 'avg_finish': {0: 21.875, 1: 18.0, 2: 19.25, 3: 18.375, 4: 33.5}, 'avg_pd': {0: 3.875, 1: 3.5, 2: -2.0, 3: -0.5, 4: 0.0}, 'percent_fl': {0: 0.0036250647332988096, 1: 0.0071770334928229675, 2: 0.03655483224837256, 3: 0.006718346253229974, 4: 0.0}, 'percent_ll': {0: 0.0031071983428275505, 1: 0.001594896331738437, 2: 0.03505257886830245, 3: 0.006718346253229974, 4: 0.0}, 'percent_lc': {0: 0.9587884806355512, 1: 0.6226415094339622, 2: 0.9915590863952334, 3: 0.9607745779543198, 4: 0.2398212512413108}, 'finish_rank': {0: 25.0, 1: 17.0, 2: 20.5, 3: 19.0, 4: 35.0}, 'pd_rank': {0: 7.0, 1: 9.0, 2: 26.0, 3: 23.0, 4: 19.5}, 'fl_rank': {0: 28.0, 1: 21.0, 2: 8.0, 3: 22.0, 4: 35.0}, 'll_rank': {0: 19.0, 1: 24.0, 2: 6.0, 3: 16.0, 4: 31.0}, 'overall': {0: 79.0, 1: 71.0, 2: 60.5, 3: 80.0, 4: 120.5}, 'overall_rank': {0: 22.0, 1: 20.0, 2: 13.0, 3: 24.0, 4: 34.0}, 'pd_pts': {0: 3.875, 1: 3.5, 2: -2.0, 3: -0.5, 4: 0.0}, 'fl_pts': {0: 0.5455722423614707, 1: 1.0801435406698563, 2: 5.50150225338007, 3: 1.0111111111111108, 4: 0.0}, 'll_pts': {0: 0.2338166752977732, 1: 0.12001594896331738, 2: 2.6377065598397595, 3: 0.5055555555555555, 4: 0.0}, 'finish_pts': {0: 22.0, 1: 30.0, 2: 26.5, 3: 28.0, 4: 12.0}, 'total_pts': {0: 26.654388917659244, 1: 34.70015948963317, 2: 32.63920881321983, 3: 29.016666666666666, 4: 12.0}}

Advice on improving this is appreciated.

Todd Burus
  • 963
  • 1
  • 6
  • 20

3 Answers3

3

Set up your conditions:

c1 = (df.qualifying - df.avg_pd).lt(1)
c2 = (df.qualifying.gt(df.avg_start))
c3 = (df.qualifying.add(df.avg_pd).gt(40))

And your corresponding outputs:

o1 = df.qualifying.sub(1)
o2 = df.qualifying.sub(df.avg_finish)
o3 = 40 - df.qualifying

Using np.select:

df['pd_pq'] = np.select([c1, c2, c3], [o1, o2, o3], df.avg_pd)

              Driver  qualifying       finish_pts  total_pts  pd_pq
0  A.J. Allmendinger    0.233817    ...    22.0    26.654389  3.875
1        Alex Bowman    0.120016    ...    30.0    34.700159  3.500
2      Aric Almirola    2.637707    ...    26.5    32.639209 -2.000
3      Austin Dillon    0.505556    ...    28.0    29.016667 -0.500
4        B.J. McLeod    0.000000    ...    12.0    12.000000  2.500
user3483203
  • 50,081
  • 9
  • 65
  • 94
  • Thanks. That certainly adds readability. Is there an advantage for using that dot notation for calling columns in the dataframe? I've never seen that used before. – Todd Burus Jul 28 '18 at 04:31
  • 1
    It's a helpful option that pandas allows, no real benefit, just autocompletes for me in my console, so I like to use it. I also think it looks cleaner – user3483203 Jul 28 '18 at 04:32
  • 1
    There *are* some cases that it won't work, for example, you cannot *create* a column using dot notation, and you can't access columns that are named things like `index` or other names used by `pandas` – user3483203 Jul 28 '18 at 04:36
1

I didn't run this as I didn't have the test data, but this should work, presuming I was correct with my parentheses and you import numpy as np

import numpy as np 

proj_file_pq['pd_pq'] = np.where(proj_file_pq['qualifying'] - proj_file_pq['avg_pd'] < 1, proj_file_pq['qualifying'] - 1, 
  np.where(proj_file_pq['qualifying'] > proj_file_pq['avg_start'], proj_file_pq['qualifying'] - proj_file_pq['avg_finish'], 
  np.where(proj_file_pq['qualifying'] + proj_file_pq['avg_pd'] > 40, 40 - proj_file_pq['qualifying'], 
  proj_file_pq['avg_pd']))

print(proj_file_pq[['Driver', 'avg_start', 'avg_finish', 'qualifying',\
                 'avg_pd', 'pd_pq']].head())

You don't need to create proj_file_pq['pd_pq'] prior and set it equal to 0 with this method

WIT
  • 1,043
  • 2
  • 15
  • 32
  • Thank you. This works, and is certainly shorter. Do you think there may be another way that leads to increased readability? – Todd Burus Jul 28 '18 at 04:13
  • No problem, yes you can definitely write out the conditions like the individual below did, so you would have condition1 = ...., condition2 =... and then in the function do: proj_file_pq['pd_pq'] = np.where(cond1, value_if_cond1, np.where(cond2, value_if_cond2... np.where(cond3, value_if_cond3, value_if_none_of_conditions))) – WIT Jul 28 '18 at 04:16
  • This doesn't allow for the first possible outcome. **`np.where`** only allows for a True and False outcome, not multiple. It happens to work in this case because in the sample you provided, the first condition is never met. – user3483203 Jul 28 '18 at 04:16
  • Why use nested `np.wheres` when there is a method that is designed specifically for multiple conditions? – user3483203 Jul 28 '18 at 04:18
1

One heads up I want to give you is the error: C:\Python36\lib\site-packages\pandas\core\indexing.py:189: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

Usually happens for me when I've created multiple data frames without using reset_index() at the end of your command to create the data frame. You may want to use that when creating your table to see if it gets rid of the slicing error. I normally use reset_index(drop=True) if you already have an ID column to avoid creating redundant ID columns.

I hope this helps clear that up!

Clinton Sorrel
  • 87
  • 7