Trying to multiply a certain data cell by another certain data cell in pandas-CodePudding

Due to misunderstanding using my real scenario I am going to create one.

Here is the DataFrame.

import pandas as pd
num1df = pd.DataFrame({'Number 1': [1, 4, 3, 2, 100]})
num2df = pd.DataFrame({'Number 2': [1, 2, 'NaN', 4, 5]})
num3df = pd.DataFrame({'Number 3': [1, 2, 3, 1000, 0]})
numsdf = pd.concat([num1df, num2df, num3df], axis=1, join="inner")
print(numsdf)

   Number 1 Number 2  Number 3
0         1        1         1
1         4        2         2
2         3      NaN         3
3         2        4      1000
4       100        5         0

I want to be able to do the follow addition. Column Number 1 row 4 plus column Number 3 row 3 = Column Number 2 row 2. 100 1000 = 1100 (the answer should be in place of the NaN)

This should be the expected outcome:

   Number 1 Number 2  Number 3
0         1        1         1
1         4        2         2
2         3     1100         3
3         2        4      1000
4       100        5         0

How would I do that? I cannot figure it out.

CodePudding user response：

Notice: Solution working only if sme indices in all 3 DataFrames.

If possible replace non numeric values by missing values and then forward filling last non missng values in same column use:

marketcapdf['Market Cap'] = stockpricedf['Stock Price'] * 
                            pd.to_numeric(outstandingdf['Outstanding'],
                                          errors='coerce').ffill()

If working in one DataFrame:

df['Market Cap'] = df['Stock Price'] * 
                   pd.to_numeric(df['Outstanding'],
                                 errors='coerce').ffill()

EDIT: If need multiple by shifted second column with no change first value use:

numsdf['new'] = numsdf['Number 1'] * numsdf['Number 2'].shift(fill_value=1)
print(numsdf)
   Number 1  Number 2  new
0         5         1    5
1         4         2    4
2         3         3    6
3         2         4    6
4         1         5    4

EDIT1: I create new columns for better understanding:

num1df = pd.DataFrame({'Number 1': [1, 4, 3, 2, 100]})
num2df = pd.DataFrame({'Number 2': [1, 2, np.nan, 4, 5]})
num3df = pd.DataFrame({'Number 3': [1, 2, 3, 1000, 0]})
numsdf = pd.concat([num1df, num2df, num3df], axis=1, join="inner")

#add by shifted values
numsdf['new'] = numsdf['Number 1'].shift(-1, fill_value=0)   numsdf['Number 3']
#shift again
numsdf['new1'] = numsdf['new'].shift(-1, fill_value=0)
#replace NaN by another column
numsdf['new2'] = numsdf['Number 2'].fillna(numsdf['new1'])
print(numsdf)
   Number 1  Number 2  Number 3   new  new1    new2
0         1       1.0         1     5     5     1.0
1         4       2.0         2     5     5     2.0
2         3       NaN         3     5  1100  1100.0
3         2       4.0      1000  1100     0     4.0
4       100       5.0         0     0     0     5.0

CodePudding user response：

foo = numsdf.iloc[4, 0]
bar = numsdf.iloc[3, 2]
numsdf.at[2, 'Number 2'] = foo   bar

Output:

   Number 1 Number 2  Number 3
0         1        1         1
1         4        2         2
2         3     1100         3
3         2        4      1000
4       100        5         0