from pandas import ExcelWriter
import pandas as pd
df2 = pd.read_excel('file.xlsx', 'sheet1', na_values=['NULL'])
#df2

l1 = ["a", "b", "c"]
l2 = ["d", "e", "f"]
l3 = ["g", "h", "i"]
l4 = ["j", "k", "l"]


for ind in df2.index: 
    for i in l1:
        if l1[i] in df2.index and l1[i] in df2.index:
            df2["Scope"] = 0
#df2        
for ind in df2.index: 
    for i in l2:
        if l2[i] in df2.index and l2[i] in df2.index:
            df2["Scope"] = 1
#df2            
for ind in df2.index: 
    for i in l3:
        if l3[i] in df2.index and l3[i] in df2.index:
            df2["Scope"] = 2
            
#df2
for ind in df2.index: 
    for i in l4:
        if l4[i] in df2.index and l4[i] in df2.index:
            df2["Scope"] = 3

#df2    

writer = ExcelWriter('output_file.xlsx')
df2.to_excel(writer,'Sheet1',index=False)
writer.save() 

(Jun-27-2019, 02:50 PM)starter_student Wrote: I have an Excel file ... name, description, scope (column scope is fast empty)
and I want to assign a "code" (0, 1, 2 and 3) in each row of the scope column
when in the first_row 1,2 or all elements of the list l1 are also in the columns name and description
then scope.first_row = 0 and so on.

here is my code but it doesnt work

from pandas import ExcelWriter
import pandas as pd
df2 = pd.read_excel('file.xlsx', 'sheet1', na_values=['NULL'])
#df2

l1 = ["a", "b", "c"]
l2 = ["d", "e", "f"]
l3 = ["g", "h", "i"]
l4 = ["j", "k", "l"]


for ind in df2.index: 
    for i in l1:
        if l1[i] in df2.index and l1[i] in df2.index:
            df2["Scope"] = 0
#df2        
for ind in df2.index: 
    for i in l2:
        if l2[i] in df2.index and l2[i] in df2.index:
            df2["Scope"] = 1
#df2            
for ind in df2.index: 
    for i in l3:
        if l3[i] in df2.index and l3[i] in df2.index:
            df2["Scope"] = 2
            
#df2
for ind in df2.index: 
    for i in l4:
        if l4[i] in df2.index and l4[i] in df2.index:
            df2["Scope"] = 3

#df2    

writer = ExcelWriter('output_file.xlsx')
df2.to_excel(writer,'Sheet1',index=False)
writer.save() 

Can someone help me with this issue ... please

tof_list = ["camera", "tof", "time of flight", "topology", "Time-of-flight camera", "time-of-flight", "Time-of-flight sensor", "TOF cameras", "3D Sensor"]
camerad_list = ["3D Camera", "Camera", "3D Camera Sensor", "Range camera", "Stereo Camera", "Vectograph", "Range camera", "Stereo camera", "3D Sensor", "image sensor"]
ir_list = ["IR Spektroskopie", "Infrared spectroscopy", "spectroscopy sensor", "vibrational spectroscopy", "infrared spectrometer", "infrared spectrum", "spectroscopy sensor"]
hyp_list = ["Hyperspektralkamera", "Hyperspektral", "Hyperspektral Sensor", "Hyperspektrale Kameras", "hyperspektral camera sensor", "Hyperspectral Imaging", "hyperspectral imaging systems", "Spectral scanning", "hyperspectral sensor"]

#a = df2['productName'][ind]
#b = df2['productDescription'][ind]

l1 = len(tof_list)
l2 = len(camerad_list)
l3 = len(ir_list)
l4 = len(hyp_list)

for ind in df2.index: 
    for i in range(l1):
        if tof_list[i] in df2.index and tof_list[i] in df2.index:
            df2["TechnologyClassification"] = 110
            
            
#df2        
for ind in df2.index: 
    for i in range(l2):
        if camerad_list[i] in df2.index and camerad_list[i] in df2.index:
            df2["TechnologyClassification"] = 120
#df2            
for ind in df2.index: 
    for i in range(l3):
        if ir_list[i] in df2.index and ir_list[i] in df2.index:
            df2["TechnologyClassification"] = 210
            
#df2
for ind in df2.index: 
    for i in range(l4):
        if hyp_list[i] in df2.index and hyp_list[i] in df2.index:
            df2["TechnologyClassification"] = 220

#df2    

writer = ExcelWriter('output_file.xlsx')
df2.to_excel(writer, 'Sheet1', index=False)
writer.save()    

df2.to_csv(r'C:\Users\username\Desktop\output_file.csv', index=False)

for ind in df2.index: 
    for i in range(l1):
        if tof_list[i] in df2.index and tof_list[i] in df2.index:
            df2["TechnologyClassification"] = 110

df2.loc[df2.index.isin(tof_list), "TechnologyClassification"] = 110

(Jun-29-2019, 10:19 AM)scidam Wrote: Usually, when working with Pandas it is possible to avoid loops. Your code has a lot of them.

This fragment:

for ind in df2.index: 
    for i in range(l1):
        if tof_list[i] in df2.index and tof_list[i] in df2.index:
            df2["TechnologyClassification"] = 110

could be rewritten, as follows:

df2.loc[df2.index.isin(tof_list), "TechnologyClassification"] = 110