Python Forum
Pandas/Excel, reading from one column writing back to another...
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pandas/Excel, reading from one column writing back to another...
#1
Hi Guys

Can you help me process the data in a spreadsheet, in particular to read values in one column apply some functions and then write the results back to another column.

I will simplify my problem as much as I can. The example I am going to give can be easily done in Excel without any 3rd party help, I just want to make the code as easy to read as possible, so that I can apply it back to my own case - which is a bit more complicated, with additional functions inside the main 'for' loop. All the code there is working fine though.
import 


import pandas as pd
df = pd.read_excel('serials.xlsx', sheet_name='Sheet1')
listSerialsLower = df['Serial_Lower']
listSerialsUpper = df['Serial_Upper']

writer = pd.ExcelWriter('serials.xlsx', engine='xlsxwriter')

for i in listSerialsLower:
    inputSerial = i
    upperSerial = str(inputSerial).upper()

    #please let me know how I can insert the corresponding values
    #for the lowercase serials into the list (or series?) for the uppercase serials
    #so that they would appear in the same row in the excel document. 

    #i have tried without success...
    listSerialsUpper[i] = upperSerial

df.to_excel(writer)
writer.save()


Thanks for helping me out.

WL
Reply
#2
I've solved it myself. Just really need a while loop instead of a for loop and to make one new variable, the size of the list.

sizeOfSerialsList = len(listSerials)
count = 0

while (count < sizeOfSerialsList):
    inputSerial = listSerials.iloc[count]
    inputSerial = str(inputSerial).upper()
    modelCodeIsolatedFromSerial = ""
    model = ""


    if len(inputSerial) == 12:
        modelCodeIsolatedFromSerial = inputSerial[-4:]
    elif len(inputSerial) == 11:
        modelCodeIsolatedFromSerial = inputSerial[-3:]

    try:
        model = databaseOfMacs[modelCodeIsolatedFromSerial]
        listModels.iloc[count] = model
    except:
        listModels.iloc[count] = "Not found"

    count = count + 1
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Pandas keep existing format of Excel AlphaInc 2 885 Jan-11-2024, 03:44 AM
Last Post: plonkarchivist
  HTML Decoder pandas dataframe column mbrown009 3 962 Sep-29-2023, 05:56 PM
Last Post: deanhystad
  pandas column percentile nuncio 7 2,383 Aug-10-2022, 04:41 AM
Last Post: nuncio
  pandas: Compute the % of the unique values in a column JaneTan 1 1,759 Oct-25-2021, 07:55 PM
Last Post: jefsummers
  Pandas Data frame column condition check based on length of the value aditi06 1 2,655 Jul-28-2021, 11:08 AM
Last Post: jefsummers
  How to move each team row to a new column. Pandas vladiwnl 0 1,696 Jun-13-2021, 08:10 AM
Last Post: vladiwnl
  iretate over columns in df and calculate euclidean distance with one column in pandas Pit292 0 3,269 May-09-2021, 06:46 PM
Last Post: Pit292
  [Pandas] Write data to Excel with dot decimals manonB 1 5,774 May-05-2021, 05:28 PM
Last Post: ibreeden
  Python - Pandas writing blank files to file tdunphy 0 1,973 Jan-14-2021, 12:11 AM
Last Post: tdunphy
Question Pandas - Creating additional column in dataframe from another column Azureaus 2 2,918 Jan-11-2021, 09:53 PM
Last Post: Azureaus

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020