Home > Net >  How can I always choose the last column in a csv table that's updated monthly?
How can I always choose the last column in a csv table that's updated monthly?

Time:11-09

Automating small business reporting from my Quickbooks P&L. I'm trying to get the net income value for the current month from a specific cell in a dataframe, but that cell moves one column to the right every month when I update the csv file.

For example, for the code below, this month I want the value from Nov[0], but next month I'll want the value from Dec[0], even though that column doesn't exist yet.

Is there a graceful way to always select the second right most column, or is this a stupid way to try and get this information?

import numpy as np
import pandas as pd


nov = -810
dec = 14958
total = 8693
d = {'Jan': [50], 'Feb': [70], 'Total':[120]}
df = pd.DataFrame(data=d)

CodePudding user response:

Sure, you can reference the last or second-to-last row or column.

d = {'Jan': [50], 'Feb': [70], 'Total':[120]}
df = pd.DataFrame(data=d)
x = df.iloc[-1,-2]

This will select the value in the last row for the second-to-last column, in this case 70. :)

CodePudding user response:

If you plan to use the full file, @VincentRupp's answer will get you what you want.

But if you only plan to use the values in the second right most column and you can infer what it will be called, you can tell pd.read_csv that's all you want.

import pandas as pd  # 1.5.1


# assuming we want this month's name
# can modify to use some other month
abbreviated_month_name = pd.to_datetime("today").strftime("%b")

df = pd.read_csv("path/to/file.csv", usecols=[abbreviated_month_name])

print(df.iloc[-1, 0])

References

  • Related