I'm trying to pull cell values from the files I'm grabbing in the code below so I can write them in the loop for a new column. The column will be called ADT and their value is in cell A333. import os , sys , pandas as pd path = r "C:\Users\anthonyv\Downloads\Metro\New folder" files = os . listdir ( path ) data = [ ] # a list to hold your data for filename in files : f = filename . split ( ' MP ' ) address = f [ 0 ] mile = '.' . join ( f [ 1 ] . split ( ' ' ) [ 0 ] . split ( '_' ) ) # convert underscore to period df = pd . DataFrame ( { 'MP' : [ "{}" . format ( mile ) ] , 'RdNm' : [ "{}" . format ( address ) ] } ) data . append ( [ address , mile ] ) df = pd . DataFrame ( data , columns = [ 'Road' , 'Milepost' ] , dtype = float ) writer = pd . ExcelWriter ( 'MetroReportAGOL.xlsx' ) df . to_excel ( writer , sheet_name = 'Sheet1' ) writer . save ( ) print ( "Complete" )

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Excel cell value as variable?

Anonymous User

I'm trying to pull cell values from the files I'm grabbing in the code below so I can write them in the loop for a new column. The column will be called ADT and their value is in cell A333.

<SPAN class="keyword token">import</SPAN> os<SPAN class="punctuation token">,</SPAN> sys<SPAN class="punctuation token">,</SPAN> pandas <SPAN class="keyword token">as</SPAN> pd

path <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\anthonyv\Downloads\Metro\New folder"</SPAN>

files <SPAN class="operator token">=</SPAN> os<SPAN class="punctuation token">.</SPAN>listdir<SPAN class="punctuation token">(</SPAN>path<SPAN class="punctuation token">)</SPAN>

data <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>&nbsp; <SPAN class="comment token"># a list to hold your data</SPAN>

<SPAN class="keyword token">for</SPAN> filename <SPAN class="keyword token">in</SPAN> files<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; f <SPAN class="operator token">=</SPAN> filename<SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">' MP '</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; address <SPAN class="operator token">=</SPAN> f<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp; mile <SPAN class="operator token">=</SPAN> <SPAN class="string token">'.'</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">' '</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'_'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># convert underscore to period</SPAN>
&nbsp;&nbsp;&nbsp; df <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">{</SPAN><SPAN class="string token">'MP'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>mile<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'RdNm'</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"{}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>address<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">}</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; data<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>address<SPAN class="punctuation token">,</SPAN> mile<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> 

df <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>DataFrame<SPAN class="punctuation token">(</SPAN>data<SPAN class="punctuation token">,</SPAN>columns<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Road'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Milepost'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>dtype<SPAN class="operator token">=</SPAN>float<SPAN class="punctuation token">)</SPAN>
writer <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>ExcelWriter<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'MetroReportAGOL.xlsx'</SPAN><SPAN class="punctuation token">)</SPAN>
df<SPAN class="punctuation token">.</SPAN>to_excel<SPAN class="punctuation token">(</SPAN>writer<SPAN class="punctuation token">,</SPAN> sheet_name<SPAN class="operator token">=</SPAN><SPAN class="string token">'Sheet1'</SPAN><SPAN class="punctuation token">)</SPAN>
writer<SPAN class="punctuation token">.</SPAN>save<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Complete"</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Najděte více příspěvků označených jako

Accepted answers

DarrenWiens2

You do a bunch of work to get things into the dataframe within the loop, but never use it. Rather, you append to 'data' (2 columns), and then attempt to make a new dataframe from that (specifying 3 columns).

I assume you should use:

data<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>adt<SPAN class="punctuation token">,</SPAN> address<SPAN class="punctuation token">,</SPAN> mile<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

All comments

Anonymous User

The final table that's created from this script will be updating an ArcOnline feature service. Thanks

bixb0012

As an FYI, the issues you are running into have nothing to do with ArcGIS, ArcPy, or any other Esri product. Doing some online searching, especially in places like StackExchange/Overflow, will likely provide suggestions to users who are having similar issues as you are here. I am not discouraging you from posting on GeoNet, I just want to point out there are many other avenues to troubleshoot your problem once it has nothing to do with Esri.

Anonymous User

I made the change mentioned above and am receiving the error below.

DarrenWiens2

You first need to read the file into a dataframe:

excel <SPAN class="operator token">=</SPAN> pd<SPAN class="punctuation token">.</SPAN>read_excel<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'path/to/xlsx'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Then, find the value:

excel<SPAN class="punctuation token">.</SPAN>iloc<SPAN class="punctuation token">[</SPAN>row_index<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">[</SPAN>column_name<SPAN class="punctuation token">]</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Anonymous User

It's pulling values from the filename and adding it to an excel table. I'm now wanting to pull cell data from within each file and add it to the excel table it is creating.

DanPatterson_Retired

what does it do now?

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.