Excel data calculation using Python

Question

Hi, I am new to Python. I need to make an arithmetic calculation of excel data using Python, but the data arrangement is a bit complicated.

I have to calculate

(D5*D4 + C5*C4)*B5

which the results are placed on column H

then it continues to the entire materials (adipose tissue newborn #1 to adipose tissue adult #1)

(D6*D4 + C6*C4)*B6
(D7*D4 + C7*C4)*B7
(D8*D4 + C8*C4)*B8

etc.

Could anyone please help me? Below is my current codes, and I am getting stuck how to code the operation. Thank you very much in advance

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns
%matplotlib inline

# read an excel file and convert 
# into a dataframe object
df = pd.DataFrame(pd.read_excel("ICRU_blank_af.xlsx"))
# show the dataframe
df.head(5)

Is there a reason you can't do this calculation in Excel? It looks like you would only need to add some absolute references in the rows; ie. =(D5*D$4 + C5*C$4)*B5 in cell H5 and copy down. — Professor Pantsless
– Professor Pantsless, Commented Oct 12, 2021 at 21:02

G H · Accepted Answer · 2021-10-13 22:56:54Z

1

I would recommend something like the following:

    data = {'first_column':  [1, 2, 3, 4, 5, 6, 7, 8],
        'second_column': [1, 2, 3, 4, 5, 6, 7, 8],
        'third_column':  [1, 2, 3, 4, 5, 6, 7, 8],
        'fourth_column': [1, 2, 3, 4, 5, 6, 7, 8]
        }

df = pd.DataFrame(data)

#solving for ((D*D)+(C*C))*B
#Assume first_column equates to Col D
#Assume second_column equates to Col C
#Assume third_column equates to Col B

#Pulling pairs of D column
first_pair = list(zip(df['first_column'], df['first_column'].iloc[1:]))

#pulling pairs of C column
second_pair = list(zip(df['second_column'], df['second_column'].iloc[1:]))

#(D5*D4)
first_multiplication = [reduce(operator.mul, tup, 1) for tup in first_pair]

#(C5*C4)
second_multiplication = [reduce(operator.mul, tup, 1) for tup in second_pair]

final_value = []

#solving for ((D5*D4)+(C5*C4))*B5 
for first, second, third in zip(first_multiplication,second_multiplication,df['third_column']):
    value = first + second *third
    final_value.append(value)

My results are the following: first_pair/second_pair = [(1,2),(2,3),(3,4),(4,5)...]

first_multiplication/second_multiplication = [2,6,12,20,...]

first formula value = 4 ((2)+(2))*1 final list (to append to new column) = [4,24,72,160,300,504,784]

edited Oct 13, 2021 at 22:56

answered Oct 11, 2021 at 19:33

G H

1161 gold badge1 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hana T. A Over a year ago

Hi thank you for your answer, however my table is a bit complex. I think the codes you gave is for basic table, but my table and required calculation is complex. I need to 'pull' some specific cells in my calculation, so this is my confused

G H Over a year ago

@HanaT.A I edited my response. Please note that I have basically given you the formula. If you have any questions, let me know. Please upvote my solution as well if I addressed your question.

G H · Accepted Answer · 2021-10-14 04:42:40Z

I read your question in greater detail, and I think you're looking at creating some constants:

data = {'first_column':  [1, 2, 3, 4, 5, 6, 7, 8],
        'second_column': [1, 2, 3, 4, 5, 6, 7, 8],
        'third_column':  [1, 2, 3, 4, 5, 6, 7, 8],
        'fourth_column': [1, 2, 3, 4, 5, 6, 7, 8]
        }

df = pd.DataFrame(data)

#solving for ((D5*$D$4)+(C5*$C$4))*B5
#Assume first_column equates to Col d
#Assume second_column equates to Col C
#Assume third_column equates to Col B

first_pairing = []
second_pairing = []

#(D*$D$X)
for cell in df['first_column']:
    pairing = cell * df['first_column'].iloc[0]
    first_pairing.append(pairing)

#(C*$C$X)    
for cell in df['second_column']:
    pairing = cell * df['second_column'].iloc[0]
    second_pairing.append(pairing)
    
final_value = []

#solving for ((D5*$D$4)+(C5*$C$4))*B5 
for first, second, third in zip(first_pairing,second_pairing,df['third_column']):
    value = first + second *third
    final_value.append(value)

I would replace the iloc values to the exact row that you're looking to keep as a constant, and the column names to match the df header names.

Collectives™ on Stack Overflow

Excel data calculation using Python

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related