How can I calculate values in a Pandas dataframe based on another column in the same dataframe

Question

I am attempting to create a new column of values in a Pandas dataframe that are calculated from another column in the same dataframe:

df['ema_ideal'] = df['Adj Close'].ewm(span=df['ideal_moving_average'], min_periods=0, ignore_na=True).mean

However, I am receiving the error:

ValueError: The truth of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any(), or a.all().

If I have the span set to 30, or some integer, I do not receive this error. Also, ideal_moving_average is a column of float.

My two questions are:

Why exactly am I receiving the error?

How can I incorporate the column values from ideal_moving_average into the df['ema_ideal'] column (subquestion as I am new to Pandas - is this column a Series within the dataframe?)

Thanks for the help!

EDIT: Example showing Adj Close data, in bad formatting

Date              Open        High         Low       Close   Adj Close                                                                           
2017-01-03  225.039993  225.830002  223.880005  225.240005  222.073914   
2017-01-04  225.619995  226.750000  225.610001  226.580002  223.395081   
2017-01-05  226.270004  226.580002  225.479996  226.399994  223.217606   
2017-01-06  226.529999  227.750000  225.899994  227.210007  224.016220   
2017-01-09  226.910004  227.070007  226.419998  226.460007  223.276779   
2017-01-10  226.479996  227.449997  226.009995  226.460007  223.276779

Answer 1

For anyone wondering, the problem was that span could not take multiple values, which was happening when I tried to pass df['ideal_moving_average'] into it. Instead, I used the below code, which seemed to go line by line passing the value for that row into span.

df['30ema'] = df['Adj Close'].ewm(span=df.iloc[-1]['ideal_ma'], min_periods=0, ignore_na=True).mean()

EDIT: I will accept this as correct for now, until someone shows that it doesn't work or can create something better, thanks for the help.

Answer 2

I think something like this will work for you:

df['ema_ideal'] = df.apply(lambda x: df['Adj Close'].ewm(span=x['ideal_moving_average'], min_periods=0, ignore_na=True).mean(), axis=1)

Providing axis=1 to DataFrame.apply allows you to access the data row wise like you need.

Answer 3

There's absolutely no issue creating a dataframe column from another dataframe.

The error you're receiving is completely different, this error is returned when you try to compare Series with logical fonctions such as and, or, not etc...

In general, to avoid this error you must compare Series element wise, using for example & instead of and, or ~ instead of not, or using numpy to do element wise comparation.

Here, the issue is that you're trying to use a Serie as the span of your ema, and pandas ewma function only accept integers as spans.

You could for example, calculate the ema for each possible periods, and then regroup them in a Serie that you set as the ema idealcolumn of your dataframe.