Posts: 14
Threads: 4
Joined: Jun 2019
Jun-27-2019, 02:50 PM
(This post was last modified: Jun-27-2019, 03:51 PM by Larz60+.)
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
Posts: 14
Threads: 4
Joined: Jun 2019
Jun-28-2019, 07:22 AM
(This post was last modified: Jun-28-2019, 07:36 AM by starter_student.)
(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
Hey!
here is the entire code ...
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) I have no errors no warnings but nothing change in the output file
Posts: 818
Threads: 1
Joined: Mar 2018
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
Posts: 14
Threads: 4
Joined: Jun 2019
(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
Thank you!
|