Python Forum

Hello,

I'm not sure why I'm receiving the error message at the very bottom, its for line 30. I'm just trying to count how many times the value 0 occurs in ws1 column F starting at row 4, and then write it out to ws3 column M row 2.

import openpyxl as xl 
import os
import pandas as pd 
 
input_dir = 'C:\\work\\comparison\\NNM'
template = 'C:\\work\\comparison\\template.xlsx'
summary = 'C:\\work\\comparison\\summary.xlsx'
newFile = 'Comparison.xlsx'
 
 
 
 
files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]
 
 
wb3 = xl.load_workbook(template) 
ws3 = wb3.worksheets[0] 
 
i=0
ii=0
 
 
for file in files: 
   input_file =  os.path.join(input_dir, file)
   wb1=xl.load_workbook(input_file)
   ws1=wb1.worksheets[0]
   
   series = pd.read_excel(input_file, sheetname=None, skiprows = [5], usecols = "F", squeeze = True) 
   counts = series.value_counts()
   z = counts[0]
    
   wb2 = xl.load_workbook(summary) 
   ws2 = wb2.worksheets[1]
 
     
   ws3[f'A{i+2}']=ws1['A1'].value[28:]
   ws3[f'D{i+2}']=ws1['B4'].value
   ws3[f'E{i+2}']=ws1['D4'].value
   ws3[f'I{i+2}']=ws1['B'][-1].value
   ws3[f'J{i+2}']=ws1['D'][-1].value
   ws3[f'O{i+2}']=ws1['E'][-1].value 
   ws3[f'N{i+2}']=ws2[f'I{ii+6}'].value  
   ws3[f'M{i+2}']=z 
   i += 1
   ii +=1          
    
    
 
   wb3.save(newFile)

Error Message

Error:
counts = series.value_counts()

AttributeError: 'collections.OrderedDict' object has no attribute 'value_counts

Hello,
I am not very familiar with the modules you are using, so I can't give very specific advice. But value_counts() seems to be a method of pandas dataframe. While pd.read_excel seems to return an ordered dictionary. I hope you can work on finding the issue from here.

pd.read_excel() return a DataFrame and not a series.

# series = pd.read_excel(input_file, sheetname=None, skiprows = [5], usecols = "F", squeeze = True) 
df = pd.read_excel(input_file, sheetname=None, skiprows = [5], usecols = "F", squeeze = True)

Now on DataFrame can access a series bye name df.Age.value_counts() or df['Age'].value_counts(),if eg a column has name Age.

(Jul-16-2020, 07:43 PM)snippsat Wrote: [ -> ]pd.read_excel() return a DataFrame and not a series.

# series = pd.read_excel(input_file, sheetname=None, skiprows = [5], usecols = "F", squeeze = True) 
df = pd.read_excel(input_file, sheetname=None, skiprows = [5], usecols = "F", squeeze = True)

Now on DataFrame can access a series bye name df.Age.value_counts() or df['Age'].value_counts(),if eg a column has name Age.

If I do this, it doesn't work correctly for some reason I get an error with Keyword:Speed which is the column header under column F. I can't use z=counts now either.

   df = pd.read_excel(file, sheet_name=None, usecols = 'F', squeeze = True) 
   df['Speed'].value_counts()
   z = counts[0]

That's odd, I get a dataframe object (not Ordered dict) with pd.read_excel. The only thing I can think of is the sheet_name argument in read_excel. If you select None it is going to get the data in the cells you specify in all the sheets in the workbook (perhaps this is why the output is Ordered Dict as mentioned by j.crater, not dataframe as intended). Try entering the sheet you are interested in, or ignore the argument if there is only one sheet in the workbook).

Otherwise, can you please print series and show what you get?