Read data in OpenOffice xlsx file then convert to a list using python

user_14624003 0
Viewed 63

Good day. I am using pandas to read a column of data in a xlsx file. I only have 1 column and it is filled with ages of people. This is my code:

import math
import pandas as pd
import openpyxl

df = pd.read_excel('/run/media/soumi/Luna (HDD)/CF-0001/PSHS/Statistics/Module 1/SLG 3.0 Data set 3.1 DOH COVID Data Drop Case Information.xlsx') # can also index sheet by name or fetch all sheets
num = df['B2:B57001;'].tolist()

num.sort() 
print(f'\n{num}')

fdt = pd.Series(num).value_counts().sort_index().reset_index().reset_index(drop=True)
fdt.columns = ['Element', 'Frequency']
print (fdt)

I am expecting that it will create a frequency table for me, however, when I run this, it shows me this:

 python -u "/run/media/soumi/Luna (HDD)/CF-0001/Programming/Python/Stat.py"
Traceback (most recent call last):
  File "/home/soumi/.local/lib/python3.9/site-packages/pandas/core/indexes/base.py", line 3361, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas/_libs/index.pyx", line 76, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/index.pyx", line 108, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/hashtable_class_helper.pxi", line 5198, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas/_libs/hashtable_class_helper.pxi", line 5206, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'B2:B57001;'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/run/media/soumi/Luna (HDD)/CF-0001/Programming/Python/Stat.py", line 6, in <module>
    num = df['B2:B57001;'].tolist()
  File "/home/soumi/.local/lib/python3.9/site-packages/pandas/core/frame.py", line 3458, in __getitem__
    indexer = self.columns.get_loc(key)
  File "/home/soumi/.local/lib/python3.9/site-packages/pandas/core/indexes/base.py", line 3363, in get_loc
    raise KeyError(key) from err
KeyError: 'B2:B57001;'

What should I do to fix this? Thanks in advance

Edit 1: I'd also to add that I'm using Manjaro alone and I don't use Windows so I don't have the excel app...If that helps

openoffice-calc xlsx openoffice.org python
1 Answers

Your code tries to find column named B2:B57001;. What you should do, IUUC:

num = df.iloc[:, 0].tolist()

If you want to construct frequency table from column Age, then you can do this:

frequency = pd.value_counts(df.Age).to_frame().reset_index()

If you just want a list of frequency, then do this:

freq_list = pd.value_counts(df.Age).to_frame().reset_index()['Age'].tolist()
user_76272570
Related