CODEWITHSUNDEEP
pythonpandas
Axioms
Axioms

Reputation: 505

Pandas Data Frames: Getting an Element in a Column by Month in Datetime Index?

I've managed to group a data frame by Datetime month doing this:

df.set_index('Date').groupby(pd.Grouper(freq='M')).sum()

The data looks like this:

           price, debt
2018-4-30, 40.0, 50,0
2018-5-31, 10.0, 0.0
2018-6-30, 30.0, 0.0
2018-7-31, 30.0, 10.0

When I run print(df.index), it gives me this:

DatetimeIndex(['2018-04-30', '2018-05-31', '2018-6-30', '2018-7-31'], dtype='datetime64[ns]', name='Date', freq='M')

Is there a way to match a column by the Month/year in the index? So, if I wanted to get a price in July of 2018, it'll return 30.0. If not, what is the best way to do that? Thank you.

Upvotes: 1

Views: 93

Answers (1)

jezrael
jezrael

Reputation: 862511

Use partial string indexing by year and months with item for convert one element Series to scalar:

print(df['2018-07']['price'].item())
30.0

Or use grouping by MS for start of string and select by loc with first day:

df = df.set_index('Date').groupby(pd.Grouper(freq='MS')).sum()
print (df)
            price  debt
Date                   
2018-04-01   40.0  50.0
2018-05-01   10.0   0.0
2018-06-01   30.0   0.0
2018-07-01   30.0  10.0

print(df.loc['2018-07-01', 'price'])
30.0

Another solution is convert datetimes to month period by to_period:

df['Date'] = df['Date'].dt.to_period('M')
df = df.groupby('Date').sum()
print (df)
         price  debt
Date                
2018-04   40.0  50.0
2018-05   10.0   0.0
2018-06   30.0   0.0
2018-07   30.0  10.0

print (df.loc['2018-07', 'price'])
30.0

If use some old version of pandas also working (thanks Alexander ):

df.loc['2018-07', 'price']

but in pandas 0.22.0 get:

KeyError: 'the label [2018-07] is not in the [index]'

Upvotes: 1

Related Questions