0

I have an Excel Sheet where for some of the columns uniqueidentifier is set to "yes" and for rest of them it is blank.

Using the Pandas library, I get the data that have no value in uniqueidentifier using the following command:

df=pandas.read_excel("sample.xlsx")
df=df[df.uniqueidentifier != "yes"]

I then want to write "yes" to the uniqueidenfier column every time I loop through the index in the excel file.

list=[]
for i in df.index
  list.append(df['Subject'][i])
  # do some function here
  var="some value"

Every time I get the proper values in list and in var for the respective index. I want my function to write something to the uniqueidentifier column for that i value in the loop (for example, if in row 4, then when uniqueidentifier is not set, the list should take the value from the subject and append and write "yes" to uniqueidentifier column)

In addition to the above, I want to write the var value to an adjacent column in the excel file.

Could someone help me as I am new to python and stuck here?

S.Mehta
  • 15
  • 1
  • 7
  • Please refer the answer here, https://stackoverflow.com/questions/13842088/set-value-for-particular-cell-in-pandas-dataframe-using-index – neo Mar 27 '18 at 04:57
  • @neo Sorry I made some edits to the original question. I am reading data from an excel and then eliminating values that have **uniqueidentifier=yes**. Does **df.set_value(i,"uniqueidentifier","yes")** still work on excel sheet? – S.Mehta Mar 27 '18 at 05:13
  • Do you want to save the edits to excel sheet or to the dataframe only? – neo Mar 27 '18 at 05:19
  • @neo to the excel sheet. So next time I open it does not take the value that has uniqueidentifier=yes and only takes on the new values – S.Mehta Mar 27 '18 at 05:57

1 Answers1

0

to set the unique identifier to be yes you can add to your loop the foloowing line:

if [#some conditions are met]:    
    df.loc[i,'uniqueidentifier']='YES'
    df.iloc[i].new_column=var
    #new_column is the name of the column where you write your var value. you can name it however you want.

then you need to export it back to excel. you can use:

writer = pd.ExcelWriter('file.xlsx')
df.to_excel(writer,'Sheet1')
writer.save()

but keep in mind this will overwrite your old excel

Lior T
  • 137
  • 2
  • 9
  • Is there a way not to overwrite the entire excel file because the entire file is getting overwritten and that is causing problems for my function. Like any other library where I can do the same function as pandas but it does not overwrite the data? – S.Mehta Mar 27 '18 at 22:55
  • you can append specific line to file and it will add it to the end of your excel. use: f=open("filename","a+"), then f.write(data) and finally f.close(). all inside your loop. i'm pretty sure you can also tell your script to replace a specific line in the excel but not sure how to do it as i never tried it before – Lior T Mar 28 '18 at 04:23
  • what is f here? and also it will append the data at the end of the file correct? If I have 10 rows then the values will appear from the 11th row. For example in my example above my excel file has a column UNiqueIdentifier that has 3 rows of yes and rest of them blank. So will this append the data from 4th row or from the last row where the data end? – S.Mehta Mar 28 '18 at 04:35
  • f is just a name that lets you acsses the file with the open command. you can use any other name you like. regarding your second question append finds the last row of the file and add one more. – Lior T Mar 28 '18 at 04:38