CODEWITHSUNDEEP
pythonpandas
Saylent
Saylent

Reputation: 159

pandas increment row based on how many times a date is in a dataframe

i have this list for example dates = ["2020-2-1", "2020-2-3", "2020-5-8"] now i want to make a dataframe which contains only the month and year then the count of how many times it appeared, the output should be like

Date Count
2020-2 2
2020-5 1

Upvotes: 0

Views: 82

Answers (3)

ragas
ragas

Reputation: 916

Shorted code:

df['month_year'] = df['dates'].dt.to_period('M')

df1 = df.groupby('month_year')['dates'].count().reset_index(name="count")
print(df1)

month_year  count
0    2020-02      2
1    2020-05      1

Upvotes: 1

ArchAngelPwn
ArchAngelPwn

Reputation: 3046

This will give you a "month" and "year" column with the count of the year/month

If you want you could just combine the month/year columns together, but this will give you the results you expect if not exactly cleaned up.

df = pd.DataFrame({'Column1' : ["2020-2-1", "2020-2-3", "2020-5-8"]})
df['Month'] = pd.to_datetime(df['Column1']).dt.month
df['Year'] = pd.to_datetime(df['Column1']).dt.year
df.groupby(['Month', 'Year']).agg('count').reset_index()

Upvotes: 1

timz
timz

Reputation: 26

import pandas as pd
dates = ["2020-2-1", "2020-2-3", "2020-5-8"]
df = pd.DataFrame({'Date':dates})
df['Date'] = df['Date'].str.slice(0,6)
df['Count'] = 1
df = df.groupby('Date').sum().reset_index()

Note: you might want to use the format "2020-02-01" with padded zeros so that the first 7 characters are always the year and month

Upvotes: 1

Related Questions