Python: Select columns dynamically from January till current month

user_6389099 0
Viewed 231

I have the below dataframe:

>>>df

Name    Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A       0   1   2   3   4   5   6   7   8   9   1   2   
B       3   4   5   6   7   8   9   1   2   4   5   6
C       7   8   9   1   2   3   4   5   6   7   8   9
D       1   2   3   4   5   6   7   8   9   2   3   4

I am trying to create a monthly report where i need only those number which start from Jan till current month and Jan till last month. So effectively, there should be two columns in the output:

Current Month: May

>>>df_output:

Name    Jan:May Jan:April
A           10      6   
B           25      18          
C           27      25          
D           15      10

How do i achieve that? The output column names are not specific, can be changed.

pandas dataframe python
6 Answers
import pandas as pd

df = pd.DataFrame(
    {"Name": ["A", "B", "C", "D"],
    "Jan": [0,3,7,1],
    "Feb": [1,4,8,2],
    "Mar": [2,5,9,3],
    "Apr": [3,6,1,4],
    "May": [4,7,2,5],
    "Jun": [5,8,3,6],
    "Jul": [6,9,4,3],
    "Aug": [7,1,5,8]})

# you can wrap this code up in a function if you like:
# def create_month_df(df, start_month, end_month):
# ...
# return df_output

start_month = "Jan"
end_month = "Jun"
sum_col_name = start_month + ":" + end_month

df_output = pd.DataFrame()
df_output[sum_col_name] = df.loc[:, start_month:end_month].apply(sum)

Output: 

df_output
     Jan:Jun
Jan       11
Feb       15
Mar       19
Apr       14
May       18
Jun       22
user_53270680

If your columns headers are just string format, here is one way to do it. Suppose your column names correspond to numbers, i.e. Jan=1, Feb=2 etc, suppose you want to split it at May (i.e. column number 5) df['Jan-Mymonth'] = df.iloc[:,1:5].sum(axis=1) followed by

df['MyMonth-Dec'] = df.iloc[:,5:].sum(axis=1)

user_99264720 
>>> df1 = df.set_index('Name')
>>> pd.DataFrame({'->Apr':df1.loc[:, 'Jan':'Apr'].sum(axis=1), '->May':df1.loc[:, 'Jan':'May'].sum(axis=1)})
      ->Apr  ->May
Name
A         6     10
B        18     25
C        25     27
D        10     15
user_879730

Define current month:

from datetime import datetime as dt
from datetime import date, timedelta
today = datetime.today().replace(day = dt.now().day)
curr_month = today.strftime("%b")
df.loc[:, "Jan":curr_month].apply(sum)

Here You will get output dynemically. You can define "First_month = Jan" as well.

user_105063470

You can calculate the current Month dynamically and do something like this - 

import pandas as pd
from datetime import datetime

month = datetime.today().month

###Sample dataframe with column names as month number(1-12), sample data row included to verify output

d= pd.DataFrame(columns=(1,2,3,4,5,6,7,8,9,10,11,12), data={(1,1,1,1,1,1,1,1,1,1,1,1)})

d['Total_YTD'] = 0  ##Year to Data total(till current month)
d['Total_Year'] = 0 ##Total of entire year

for i in range(1, month+1):
    d['Total_YTD'] += d[i]

for i in range(1, 13):
    d['Total_Year'] += d[i]
user_84663800

set Name column as index, and then use apply, cumsum, shift and unstack.

df.set_index("Name").apply(
    lambda x: pd.DataFrame(
            [x.cumsum(), x.cumsum().shift(1).fillna(0)],
            columns=df_test.columns[1:],
            index=['till current month', 'till last month']
        ).unstack().T.astype(int),
    axis=1
)
user_50147000
Related