Convert Panda Row Index into Row Number for Google Sheet Formula in a Data Frame

Question

I have a Pandas Data Frame indexed from 2 to 100 with stock symbols in the first column "Ticker". In the second column I want to create a formula that will do a price lookup when the df is exported to Google Sheets.

How do I explicitly refer to the df row index number in this example?

Basically, I want to insert a new df column called "Current Price" and then have the formula:

=GOOGLEFINANCE($a2, 'price')
=GOOGLEFINANCE($a3, 'price')
=GOOGLEFINANCE($a4, 'price')
=GOOGLEFINANCE($a5, 'price')

in the Rows of this second column beginning in row 2 and down through the end of the df.

EDIT - I have added some stand alone code to show what I want to do:

import pandas as pd
import numpy as np

# Create a Panda df of stock tickers
lst = ['MSFT', 'GOOG', 'APPL', 'BGFV', 'DD']
df = pd.DataFrame(lst)

# Change the index so it begins with 2 instead of 0
df.index = np.arange(2, len(df) + 2)
df.rename(columns={0:'Ticker'}, inplace=True)

# Create a second Panda df of row numbers
row=[]
for i in range(len(df)):
  row.append(i+2)
row_df = pd.DataFrame(row)

# Change the index so it begins with 2 instead of 0
row_df.index = np.arange(2, len(df) + 2)
row_df.rename(columns={0:'Row_Number'}, inplace=True)

row_df['Row_Number'].astype(str).astype(int)

# merge the Row Number into the first df
df['Row_Number'] = row_df['Row_Number']
df.head(3)

# create a new colum that will have the Google Finance formula
df['Current_Price'] = '=(GOOGLEFINANCE($a' + str(df['Row_Number']) +', "price"))'
# Should read like "MSFT, 2, =(GOOGLEFINANCE)$a2, "price""

df.head(3)

Rockport Redfish · Accepted Answer · 2022-03-06 14:09:47Z

0

It took me a while to figure this out, but the key is to use the dataframe.at command as in this example https://www.askpython.com/python-modules/pandas/update-the-value-of-a-row-dataframe

Here is the revised code:

import pandas as pd
import numpy as np

# Create a Panda df of stock tickers
lst = ['MSFT', 'GOOG', 'APPL', 'BGFV', 'DD']
df = pd.DataFrame(lst)

df["Current Price"] = ""

for i in range(len(df)):  #determines the number of rows in the df
  row = str(i + 2)  # creates the offset for the google sheet row number
  df.at[i,'Current Price']='=(GOOGLEFINANCE($a' + row +', "price"))'

df.head(3)

answered Mar 6, 2022 at 14:09

Rockport Redfish

1093 silver badges12 bronze badges

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

Comments

Timus · Accepted Answer · 2022-03-22 08:08:50Z

0

You don't need to loop explicitly:

lst = ['MSFT', 'GOOG', 'APPL', 'BGFV', 'DD']
df = pd.DataFrame(lst)

df['Current Price'] = (
    '=(GOOGLEFINANCE($a' + (df.index + 2).astype(str) + ', "price"))'
)

edited Mar 22, 2022 at 8:08

answered Mar 21, 2022 at 20:54

Timus

11.4k5 gold badges20 silver badges33 bronze badges

1 Comment

Rockport Redfish Over a year ago

Thank you for the upgrade. That looks simplier.

jesse · Accepted Answer · 2023-08-20 19:27:42Z

0

You could do:

df['index'] = list(range(2, 101)) #added missing quotation and need letters

And then:

df['Current_Price'] = '=(GOOGLEFINANCE($a' + str(df['index']) +', "price"))'

I don't know why you want to do this, but this will get you a index column from 2 to 100 in your df['Current_Prince'].

edited Aug 20, 2023 at 19:27

jesse

6683 gold badges8 silver badges24 bronze badges

answered Mar 5, 2022 at 2:47

luka1156

1891 silver badge8 bronze badges

3 Comments

Rockport Redfish Over a year ago

Your suggestion adds a new column in the original df called index, and populates each row with the string "range(2, 101)". It then adds the "Current Price" column with the result like "=(GOOGLEFINANCE($arange(2, 101), 'price'))

luka1156 Over a year ago

Sorry, I'll edit it.

Rockport Redfish Over a year ago

I edited the original with some stand alone code to show what I have tried to do.

Collectives™ on Stack Overflow

Convert Panda Row Index into Row Number for Google Sheet Formula in a Data Frame

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related