CODEWITHSUNDEEP
python-3.xpandasdatetimedataframemulti-index
machump
machump

Reputation: 1267

Pandas- increment (Year, Month) multiindex and Year & Month columns

With a dataframe df like:

           YEAR     MONTH     VALUE
(2017,1) 2017        1          1
(2017,2) 2017        2          1
(2017,3) 2017        3          1

How can I automatically generate new incremental rows onto the dataframes such as:

       YEAR     MONTH     VALUE
(2017,1) 2017        1          1
(2017,2) 2017        2          1
(2017,3) 2017        3          1
(2017,4) 2017        4          1
(2017,5) 2017        5          1
(2017,6) 2017        6          1

I see that one can add new rows by creating a panda Series and appending it onto df but I have alot of rows to add and this method seems super manual.

I've set MONTH and YEAR as datetime using

df["MONTH"]=pd.to_datetime(df["MONTH"], format='%m')
df["YEAR"]=pd.to_datetime(df["YEAR"], format='%Y')

But i'm unsure how to automatically increment new rows onto the existing dataframe.

Upvotes: 2

Views: 810

Answers (1)

caverac
caverac

Reputation: 1637

You can try this

import dateutil.relativedelta

last_row = df.iloc[-1]
last_value = last_row['VALUE'] # or change it
last_date = datetime.datetime(last_row['YEAR'], last_row['MONTH'], 1)


t = [last_date + dateutil.relativedelta.relativedelta(months = k + 1) for k in range(12)]
df1 = [{'YEAR' : k.year, 'MONTH' : k.month, 'VALUE' : last_value} for k in t]
df1 = pandas.DataFrame(df1, index = [(k.year, k.month) for k in t])

dfnew = df.append(df1)

print(dfnew)


            MONTH  VALUE  YEAR
(2017, 1)       1      1  2017
(2017, 2)       2      1  2017
(2017, 3)       3      1  2017
(2017, 4)       4      1  2017
(2017, 5)       5      1  2017
(2017, 6)       6      1  2017
(2017, 7)       7      1  2017
(2017, 8)       8      1  2017
(2017, 9)       9      1  2017
(2017, 10)     10      1  2017
(2017, 11)     11      1  2017
(2017, 12)     12      1  2017
(2018, 1)       1      1  2018
(2018, 2)       2      1  2018
(2018, 3)       3      1  2018

​

Upvotes: 1

Related Questions