Using a conditional to execute numpy where with pandas

Question

I have a value in a field t1_months that is set to calculate the number of months that spending is occurring. I'm confident in that value and would like to use that as a limit for the number of allotments to be populated.

With this code I am getting the error ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

So that leads me to think that I need to retool this line as this is the line that the code stopped on.
if count <= counter:

How can i go about creating a conditional like this but still be able to use the np.where clause?

for month_num in range(1, 13):
    count = 0
    # counter = df['t1_months']
    if count <= df['t1_months']:
        if count <= counter:
            df['t1_' + str(month_num) + '/1/2016'] = np.where(
                (df["StartMonthYear"] <= pd.to_datetime(str(month_num) + '/1/2016'))
                & (df["EndMonthYear"] >= pd.to_datetime(str(month_num) + '/1/2016')), df["t1_allotment"], '0.0')
    count += 1

So if I have the dataframe of df = pd.DataFrame( {'t1_months' : [12,10,6,7]}) how can I use 12 as the check in the first row to only populate 12 allocations, 10 in the second row, 6 in the third and 7 in the last?

Expected output would be something like this:

t1_months 1/1/2016 ... 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 12/1/2016
12        500      ...     500      500      500      500      500      500      500  
10        500      ...     500      500      500      500      500     500 
500       500      0        0  
6         500      ...     500     500     500     500     0     0     0     0     0     0
7         500      ...     500      500      500      500      500      0      0      0      0      0

df['t1_months'] is a whole column in Pandas so how is the comparison supposed to be done if that column contains multiple different values? — roganjosh
– roganjosh, Commented Oct 9, 2017 at 20:11
Your error is generated here: if count <= df['t1_months'] count is an int, df['t1_months] is a series. These cannot be compared — Woody Pride
– Woody Pride, Commented Oct 9, 2017 at 20:15
@WoodyPride & roganjosh That's what Im trying to work with. I want the value of each row in the dataframe as a check. Then once that row has been resolved, how do I move to the next row, and the next etc...Sorry for my ignorance, I'm a bit new with pandas. — mnickey
– mnickey, Commented Oct 9, 2017 at 20:36
What your code is saying is this: if 0 is less than or equal to [1, 2, 12, 0, 12, 1, 2, 3, 4, 12, 0] how can you possibly answer that question? This is the problem with your code. What you may want to do is work out how to iterate over the series. But that is not what the question is about. If you want a more specific answer then you should post data and expected output. Keep trying :-) — Woody Pride
– Woody Pride, Commented Oct 9, 2017 at 20:41

MaxU - stand with Ukraine · Accepted Answer · 2017-10-09 22:27:31Z

Source DF:

In [77]: d
Out[77]:
   t1_months
0         12
1         10
2          6
3          7

Option 1:

In [78]: d.join(d.t1_months.apply(lambda x: pd.Series([500]*x)).fillna(0)) \
    ...:  .rename(columns=lambda x: '{}/1/2016'.format(x+1) if isinstance(x,int) else x)
Out[78]:
   t1_months  1/1/2016  2/1/2016  3/1/2016  4/1/2016  5/1/2016  6/1/2016  7/1/2016  8/1/2016  9/1/2016  10/1/2016  11/1/2016  \
0         12     500.0     500.0     500.0     500.0     500.0     500.0     500.0     500.0     500.0      500.0      500.0
1         10     500.0     500.0     500.0     500.0     500.0     500.0     500.0     500.0     500.0      500.0        0.0
2          6     500.0     500.0     500.0     500.0     500.0     500.0       0.0       0.0       0.0        0.0        0.0
3          7     500.0     500.0     500.0     500.0     500.0     500.0     500.0       0.0       0.0        0.0        0.0

   12/1/2016
0      500.0
1        0.0
2        0.0
3        0.0

Option 2:

In [87]: d.join(pd.DataFrame([[500]*x for x in d.t1_months],
    ...:                     columns=['{}/1/2016'.format(i) for i in range(1,13)],
    ...:                     index=d.index))
    ...:
Out[87]:
   t1_months  1/1/2016  2/1/2016  3/1/2016  4/1/2016  5/1/2016  6/1/2016  7/1/2016  8/1/2016  9/1/2016  10/1/2016  11/1/2016  \
0         12       500       500       500       500       500       500     500.0     500.0     500.0      500.0      500.0
1         10       500       500       500       500       500       500     500.0     500.0     500.0      500.0        NaN
2          6       500       500       500       500       500       500       NaN       NaN       NaN        NaN        NaN
3          7       500       500       500       500       500       500     500.0       NaN       NaN        NaN        NaN

   12/1/2016
0      500.0
1        NaN
2        NaN
3        NaN

UPDATE:

Say that I have this DF though.

df = pd.DataFrame( {'months': [12,10,6,7], 'allot': [200, 500, 347, 192]}) .

How do I replace the value of 500 with that which is in the df['allot'] row so that the first pass would have 200, the second 500, the third 347 and then 192 in the last pass?

In [10]: df.join(pd.DataFrame([[1]*x for x in df['months']],
    ...:                      columns=['{}/1/2016'.format(i) for i in range(1,13)],
    ...:                      index=df.index).fillna(0).mul(df['allot'], axis=0))
    ...:
    ...:
Out[10]:
   allot  months  1/1/2016  2/1/2016  3/1/2016  4/1/2016  5/1/2016  6/1/2016  7/1/2016  8/1/2016  9/1/2016  10/1/2016  11/1/2016  \
0    200      12     200.0     200.0     200.0     200.0     200.0     200.0     200.0     200.0     200.0      200.0      200.0
1    500      10     500.0     500.0     500.0     500.0     500.0     500.0     500.0     500.0     500.0      500.0        0.0
2    347       6     347.0     347.0     347.0     347.0     347.0     347.0       0.0       0.0       0.0        0.0        0.0
3    192       7     192.0     192.0     192.0     192.0     192.0     192.0     192.0       0.0       0.0        0.0        0.0

   12/1/2016
0      200.0
1        0.0
2        0.0
3        0.0

This is really close! Say that I have this DF though. df = pd.DataFrame( {'months' : [12,10,6,7], 'allot' : [200, 500, 347, 192]}) . How do I replace the value of 500 with that which is in the df['allot'] row so that the first pass would have 200, the second 500, the third 347 and then 192 in the last pass? Either way, thanks for the advice. I'll try to run with this and keep plugging away :)

Collectives™ on Stack Overflow

Using a conditional to execute numpy where with pandas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related