Newbie needs help

[actuary316]

Brand new Python user, long time APL user.

In APL I have the ability to read a matrix, designate a pararmeter (or multiple parameters) and there is functionality that will return the values in the columns that match the designated parameter(s). Here is an example:

State type Jan Feb Mar
MA widgets 1000 1200 800
MA wuzzels 555 700 750
RI widgets 200 300 375
RI wuzzels 400 300 275

In APL I have the ability to return all the cols that have MA and the function would return:
widgets 1000 1200 800
wuzzels 555 700 750

or I could say return all the cols that have RI and widgets and the function would return:
200 300 375

How would I accomplish this in Python? I started by creating a matrix in Excel. I have both the pandas and openpyxl libraries open. I've watched many tutorials that show how to read a specific cell, or entire row or entire column from an Excel table but I haven't been able to find anything like I do in APL.

Ideally, I would like to be able to read the table in Excel, find what row(s) match my designated parameters and then return the associated column data and assign it to a varaible (as values).

Any ideas? Or any resources I can use/review? Thanks!

[deanhystad]

You can do what you describe using pandas. In the example below I use StringIO to simulate reading the data from a CSV file.

import pandas as pd from io import StringIO file = StringIO( """State type Jan Feb Mar MA widgets 1000 1200 800 MA wuzzels 555 700 750 RI widgets 200 300 375 RI wuzzels 400 300 275""" ) data = pd.read_csv(file, sep=" ") print("Data\n", data, sep="\n") ma_data = data.loc[data.State == "MA"] print("\nMA Data\n", ma_data, sep="\n") print("\nMA Data without State\n", ma_data.drop("State", axis=1), sep="\n") print("\nMA Data without State or type\n", ma_data.drop(["State", "type"], axis=1), sep="\n")

Output:
Data State type Jan Feb Mar 0 MA widgets 1000 1200 800 1 MA wuzzels 555 700 750 2 RI widgets 200 300 375 3 RI wuzzels 400 300 275 MA Data State type Jan Feb Mar 0 MA widgets 1000 1200 800 1 MA wuzzels 555 700 750 MA Data without State type Jan Feb Mar 0 widgets 1000 1200 800 1 wuzzels 555 700 750 MA Data without State or type Jan Feb Mar 0 1000 1200 800 1 555 700 750

[actuary316]

You can do what you describe using pandas. In the example below I use StringIO to simulate reading the data from a CSV file.

import pandas as pd from io import StringIO file = StringIO( """State type Jan Feb Mar MA widgets 1000 1200 800 MA wuzzels 555 700 750 RI widgets 200 300 375 RI wuzzels 400 300 275""" ) data = pd.read_csv(file, sep=" ") print("Data\n", data, sep="\n") ma_data = data.loc[data.State == "MA"] print("\nMA Data\n", ma_data, sep="\n") print("\nMA Data without State\n", ma_data.drop("State", axis=1), sep="\n") print("\nMA Data without State or type\n", ma_data.drop(["State", "type"], axis=1), sep="\n")

Output:
Data State type Jan Feb Mar 0 MA widgets 1000 1200 800 1 MA wuzzels 555 700 750 2 RI widgets 200 300 375 3 RI wuzzels 400 300 275 MA Data State type Jan Feb Mar 0 MA widgets 1000 1200 800 1 MA wuzzels 555 700 750 MA Data without State type Jan Feb Mar 0 widgets 1000 1200 800 1 wuzzels 555 700 750 MA Data without State or type Jan Feb Mar 0 1000 1200 800 1 555 700 750

Thank you for the response. Much appreciated.

Ultimate goal is to sum up the sales for the quarter, based on specified parameters.
(ex 1) What are the quarterly sales of RI wuzzels?
(ex 2) What are the number of MA widgets sold in March?

And I dont want to Print the result, I want to assign the result to a variable so I can use it further down my program.

Thanks

[deanhystad]

I think pandas will work very well for all you want to do.

[Pedroski55]

If you want to use Excel in Python, try the module openpyxl

Basically, you are starting with a csv file, so convert that to xl first, then get whatever you want from the openpyxl data.

import openpyxl import csv path2file = 'csv/csv_files/widgit.csv' # open an empty workbook wb = openpyxl.Workbook() # designate the only work sheet in the new wb ws = wb.active # change the csv to xlsx with open(path2file) as f: # set the delimiter to space for your given data reader = csv.reader(f, delimiter=' ') for row in reader: ws.append(row) savename = 'openpyxl/xl_files/wigit.xlsx' wb.save(savename) sourceFile = openpyxl.load_workbook(savename) sourceFilesheets = sourceFile.sheetnames print(sourceFilesheets) # there is 1 sheet called Sheet in every new wb # access things you want to see for sheet in sourceFilesheets: print('sheet is', sheet) maxRow = sourceFile[sheet].max_row maxCol = sourceFile[sheet].max_column for rowNum in range(2, maxRow + 1): print('id number is', sourceFile[sheet].cell(row=rowNum, column=1).value) for colNum in range(1, maxCol + 1): header = sourceFile[sheet].cell(row=rowNum, column=colNum).value print('Column headers are', header) # (ex 1) What are the quarterly sales of RI wuzzels? # (ex 2) What are the number of MA widgets sold in March? for sheet in sourceFilesheets: print('sheet is', sheet) maxRow = sourceFile[sheet].max_row maxCol = sourceFile[sheet].max_column # row 1 is headers, start with row 2 for rowNum in range(2, maxRow + 1): state = sourceFile[sheet].cell(row=rowNum, column=1).value product = 'wuzzels' print(f'State is {state}, product is {product}') if state == 'RI' and product == 'wuzzels': total_sales = 0 for colNum in range(3, maxCol + 1): total_sales = total_sales + int(sourceFile[sheet].cell(row=rowNum, column=colNum).value) print(f'Total sales of wuzzels in RI for the first quarter was {total_sales}.')

[snippsat]

As mentioned Pandas will work very well for this case,you most try some stuff yourself.
Example quarterly sales of RI wuzzels

import pandas as pd from io import StringIO file = StringIO( """State type Jan Feb Mar MA widgets 1000 1200 800 MA wuzzels 555 700 750 RI widgets 200 300 375 RI wuzzels 400 300 275""" ) data = pd.read_csv(file, sep=" ") # Quarterly sales of RI wuzzels print(data) ri_wuzzels_data = data.loc[(data['State'] == 'RI') & (data['type'] == 'wuzzels')] ri_wuzzels_quarterly_sales = ri_wuzzels_data[['Jan', 'Feb', 'Mar']].sum(axis=1).iloc[0] print(f"\nQuarterly sales of RI wuzzels: {ri_wuzzels_quarterly_sales}"

Output:
 State type Jan Feb Mar 0 MA widgets 1000 1200 800 1 MA wuzzels 555 700 750 2 RI widgets 200 300 375 3 RI wuzzels 400 300 275 Quarterly sales of RI wuzzels: 975

And I dont want to Print the result, I want to assign the result to a variable so I can use it further down my program.

Can of course to both,usally want print to see stuff working before move on.
The variable will just have the result,and can be used further.

>>> ri_wuzzels_quarterly_sales 975