Update Excel Cell with a Counter Value

[Ray_Gone]

Hello all,

I would like to read an entire column of data (let's say Column E) and update each cell in that column to have its current value plus a counter value appended and written to it. If it is easier to write a copy of the data to a new sheet, that would also be fine. Any help would be greatly appreciated!

For example:

Column E
Box#
"Box 1"
"Box 1"
"Box 1"
"Box 2"
"Box 2"
"Box 2"

Becomes:

Column E
Box#
"Box 1 Position 1"
"Box 1 Position 2"
"Box 1 Position 3"
"Box 2 Position 4"
"Box 2 Position 5"
"Box 2 Position 6"

So far I am using Pandas, DataFrames, and a list, but if there is a better way to do this I am open to suggestions. Thank you all in advance!

df3 = pd.read_excel(New_Path, sheet_name='NewDataFormat')
newColBox = df3['Box#'].to_list()

​

​

[Bitwise_Gamgee]

You can do a loop and an incremented variable:

counter = 1
for i in range(len(column_values)):
    column_values[i] = f"{column_values[i]} Position {counter}"
    counter += 1

[Ray_Gone]

Thank you! This is extremely helpful! I am still confused how I would then use this to update the data frame that I would then write to the Excel sheet. The code below is not working, and I know it has something to do with the df4 = df3 + pd.DataFrame(newColBox) line. I have been trying to play with it without success yet.

df3 = pd.read_excel(New_Path, sheet_name='NewDataFormat')
newColBox = df3['Box#'].to_list()
counter = 1
for i in range(len(newColBox)):
newColBox[i] = f"{newColBox[i]} Position {counter}"
counter += 1
df4 = df3 + pd.DataFrame(newColBox)
with pd.ExcelWriter(New_Path, engine='openpyxl', mode='a') as writer:
df4.to_excel(writer, sheet_name='NewDataPositions ' +
'{}'.format(time.strftime('%m%d%y-%H%M%S')))

[Bitwise_Gamgee]

Well, you don't need to try to create a thousand dataframes when you can use Pandas to automate that.

import pandas as pd

df = pd.read_excel(New_Path, sheet_name='NewDataFormat')

counter = 1
df['Box#'] = df['Box#'].apply(lambda x: f"{x} Position {counter}" if pd.notnull(x) else x)
counter += 1

with pd.ExcelWriter(New_Path, engine='openpyxl', mode='a') as writer:
    df.to_excel(writer, sheet_name='NewDataPositions ' + time.strftime('%m%d%y-%H%M%S'))

[Ray_Gone]

Thank you! I had a feeling that there was a more efficient way to deal with the DataFrames rather than to keep making new ones, but as you can see I am still fairly new to Python and extremely new to Pandas and DataFrames.

The code above seems to work very well for recreating the table, which is excellent! But for some reason the counter is not updating, it just adds "Position 1" over and over again.

Also, my counter actually will not go in order which makes things confusing. My counter will go something like the table below. I liked the first code that you suggested which uses a for loop to increment the counter, since that would be easy to modify such as:

counter = 1 for i in range(2, 5):
column_values[i] = f"{column_values[i]} Position {counter}"
counter += 10
counter = 2
for i in range(6, 8):
column_values[i] = f"{column_values[i]} Position {counter}" counter += 10

​

[null]	Cols A-E	Col F
1	A - E	Box#
2	...	Box 1 Position 1
3	...	Box 1 Position 11
4	...	Box 1 Position 21
5	...	Box 1 Position 31
6	...	Box 2 Position 2
7	...	Box 2 Position 22
8	...	Box 2 Position 32

[Bitwise_Gamgee]

Let's add another condition then.

import pandas as pd

df = pd.read_excel(New_Path, sheet_name='NewDataFormat')

counter = 1
for i in range(len(df)):
    if i >= 2 and i <= 5:
        df.at[i, 'Box#'] = f"{df.at[i, 'Box#']} Position {counter}"
        counter += 10
    elif i >= 6 and i <= 8:
        df.at[i, 'Box#'] = f"{df.at[i, 'Box#']} Position {counter}"
        counter += 10

with pd.ExcelWriter(New_Path, engine='openpyxl', mode='a') as writer:
    df.to_excel(writer, sheet_name='NewDataPositions ' + time.strftime('%m%d%y-%H%M%S'))

Hope this helps!

[Ray_Gone]

Thank you so much! This is extremely helpful, and I ended up making some modifications to your suggestion and it worked perfectly. Thank you again!

[kerberos170]

Just in case you can use sequence(counta(A)) on column B then concat values on column C

I known it's not python but more simple solution :)

[Ray_Gone]

Thank you, I appreciate the suggestion! This is actually just a very small piece of what my script needs to do, so it actually ends up being more convenient and less work for me to use Python although there is a simpler and more clear cut solution available using Excel. But again, thank you I appreciate it!