CODEWITHSUNDEEP
pythonpandas
zebraartefakt
zebraartefakt

Reputation: 53

Python pandas print value where column = X and row = Y

I am relatively new to working with python and pandas and I'm trying to get the value of a cell in an excel sheet with python. To make matters worse, the excel sheet I'm working with doesn't have proper column names.

Here's what the dataframe looks like:

Sign       Name       2020-09-05 2020-09-06 2020-09-07
JD         John Doe   A          A          B
MP         Max Power  B          B          A

What I want to do is to print the value of the "cell" where the column header is the current date and the sign is "MP".

What I've tried so far is this:

import pandas as pd
from datetime import datetime

time=datetime.now()
relevant_sheet = time.strftime("%B" " %y")
current_day = time.strftime("%Y-%m-%d")

excel_file = pd.ExcelFile('theexcelfile.xlsx')
df = pd.read_excel(excel_file, relevant_sheet, skiprows=[0,1,2,3]) # I don't need these
relevant_value = df.loc[df['Sign'] == "MP", df[current_day]]

This gives me a key error for current_day:

KeyError: '2020-09-07'

To fully disclose any possible issue with the real dataframe I'm working with: If I just print the dataframe, I get columns that look like this:

2020-09-01 00:00:00

Which is why I also tried:

current_day = time.strftime("%Y-%m-%d 00:00:00")

Of course I also "manually" tried all kinds of date formats, but to no avail. Am I going entirely wrong about this? Is this excel screwing with me?

Upvotes: 5

Views: 4283

Answers (4)

Joe Ferndz
Joe Ferndz

Reputation: 8508

Minor changes to how you are doing things will get you the result.

Step 1: strip out the 00:00:00 (if you want just the date value)

Step 2: your condition had an extra df[]

#strip last part of the column names if column starts with 2020 
df.rename(columns=lambda x: x[:10] if x[:4] == '2020' else x, inplace=True)

current_day = datetime.date(datetime.now()).strftime("%Y-%m-%d")

relevant_value = df.loc[df['Sign'] == 'MP', current_day] #does not need df before current_day
print(relevant_value)

since you are already using pandas, you don't need to import datetime. you can just give this to get your date in yyyy-mm-dd format

current_day = pd.to_datetime('now').strftime("%Y-%m-%d")

Upvotes: 0

wwnde
wwnde

Reputation: 26676

Use df.filter to filter the relevant column.

Get the relevant column by extracting today's date and converting it to a string.

Proceed and query Sign for MP

df.loc[df['Sign']=='MP',(dt.date.today()).strftime('%Y-%m-%d')]

Upvotes: 0

Sadiq Raza
Sadiq Raza

Reputation: 354

You need to pass column name only instead of df[col_name].

Look .loc[] for detail.

df.loc[df['Sign'] == "MP", current_day]

Upvotes: 2

jezrael
jezrael

Reputation: 863166

If in columns names are datetimes use Timestamp.floor for remove times (set them to 00:00:00):

current_day = pd.to_datetime('now').floor('d')
print (current_day)
2020-09-07 00:00:00

relevant_value = df.loc[df['Sign'] == "MP", current_day]

If in columns names are datetimes in strings format use:

relevant_value = df.loc[df['Sign'] == "MP", current_day]

If there are python dates:

current_day = pd.to_datetime('now').date()
print (current_day)
2020-09-07

relevant_value = df.loc[df['Sign'] == "MP", current_day]

Upvotes: 2

Related Questions