CODEWITHSUNDEEP
pythonpython-3.xpandas
Adrian
Adrian

Reputation: 784

Pandas: group by specific date

I want to split the dataframe by specific dates and the cut-off level to be 31st of March. Meaning that everything that came before 31st of march should belong to a dataframe and everything after should belong to the next data frame and so on. The data looks like this:

                    c_name       date  revenue  t_rev
310260  1-800 CONTACTS INC 1997-01-31    3.628      0
296014  1-800 CONTACTS INC 1998-01-31    6.970      0
291248  1-800 CONTACTS INC 1998-05-06   10.429      0
287356  1-800 CONTACTS INC 1998-08-11   12.801      0
283720  1-800 CONTACTS INC 1998-11-10   18.419      0
279837  1-800 CONTACTS INC 1999-02-24   18.227      0
277333  1-800 CONTACTS INC 1999-04-27   22.304      0
274650  1-800 CONTACTS INC 1999-07-20   23.960      0
269885  1-800 CONTACTS INC 1999-10-26   26.890      0
265851  1-800 CONTACTS INC 2000-02-07   25.371      0

Expected output:

df1:
                        c_name       date  revenue  t_rev
    310260  1-800 CONTACTS INC 1997-01-31    3.628      0

df2:
                        c_name       date  revenue  t_rev
    296014  1-800 CONTACTS INC 1998-01-31    6.970      0

df3:
                        c_name       date  revenue  t_rev
    291248  1-800 CONTACTS INC 1998-05-06   10.429      0
    287356  1-800 CONTACTS INC 1998-08-11   12.801      0
    283720  1-800 CONTACTS INC 1998-11-10   18.419      0
    279837  1-800 CONTACTS INC 1999-02-24   18.227      0

df4: 

                         c_name       date  revenue  t_rev
    277333  1-800 CONTACTS INC 1999-04-27   22.304      0
    274650  1-800 CONTACTS INC 1999-07-20   23.960      0
    269885  1-800 CONTACTS INC 1999-10-26   26.890      0
    265851  1-800 CONTACTS INC 2000-02-07   25.371      0

My code so far is the following:

import pandas as pd


path = 'C:/Users/Adrian/Desktop/'
df = pd.read_csv(path + "trailing.csv", low_memory=False, usecols=[0, 3, 6])
df.rename(columns={'report_dte': 'date'}, inplace=True)
df['date'] = pd.to_datetime(df['date'], format="%d/%m/%Y")
df = df.sort_values(["date"], ascending=True)
df['t_rev'] = 0

df2 = df.groupby("c_name")
counter = 0
for c_name, df_name in df2:
    counter += 1

    print(df_name.sort_values(['date'], ascending=True))
    print(len(df_name.index))
    if counter == 1:
        break

@Josh Friedlander, @Jeril and @KeepAlive solutions work as expected. Thank you all!

Upvotes: 1

Views: 518

Answers (3)

keepAlive
keepAlive

Reputation: 6655

What about doing

import datetime as dt

years        = range(1997, 2000 + 1)
df['date64'] = df.date.astype("datetime64")

for y,year in enumerate(years):
    min_date = dt.datetime(year    , 3, 31)
    max_date = dt.datetime(year + 1, 3, 31)

    globals()[f'df{y+1}'] = df.loc[
        (min_date < df.date64) & (df.date64 <= max_date)
    ].copy()

which has injected df1, df2, df3 and df4 in the global environment. Putting aside the discussion regarding if this is a good practice or not.

It is better to avoid playing directly inside the global environment like this: saving the truncated versions of df inside, say, a dictionnary called dfs, is advocated:

dfs = {}
#...
    #...
    dfs[y+1] = df.loc[
        (min_date < df.date64) & (df.date64 <= max_date)
    ].copy()

or doing the same thing with a list

dfs = []
#...
    #...
    dfs.append(
        df.loc[(min_date < df.date64) & (df.date64 <= max_date)].copy()
    )

Upvotes: 1

Josh Friedlander
Josh Friedlander

Reputation: 11657

A better way is with Pandas built-in Period:

df['year'] = df.date.dt.to_period('A-MAR')  # assigns years ending in March
dfs = [df.loc[df.year == year] for year in df.year.unique()]  
# gives list of dfs divided by year

Upvotes: 2

Jeril
Jeril

Reputation: 8521

I dint try running this code, but I guess it should work.

def getFiscalYear(dt):
    year = dt.year
    if dt.month<4: year -= 1
    return year

df['year'] = df['date'].apply(getFiscalYear)
df.groupby('year')

Make sure the 'date' column is of type pd.datetime. What we are doing here is, we are trying to create a column based on financial year, and grouping by based on that.

If you want to create a dataframe for each year, then you can iterate over the 'year' column and create new dataframe as you go.

df_dict = {}
for year in df['year'].unique():
    df_dict[year] = df[df['year' == year]]

Upvotes: 2

Related Questions