how to create a new dataframe from an existing dataframe

You can melt the dataframe and then create a new datetime column. Then keep / reorder necessary columns and sort the values:

df = df.groupby(['T1','YEAR','G1'])[['JAN','FEB','MAR','APRL','DEC']].sum().reset_index().rename({'APRL' : 'APR'}, axis=1)
df = df.melt(id_vars=['T1', 'YEAR','G1'], var_name='TIME', value_name='VALUES')
df['TIME'] = pd.to_datetime(df['TIME'] + '-' + df['YEAR'].astype(str))
df = df[['T1', 'G1', 'VALUES', 'TIME']].sort_values(['T1', 'G1','TIME'])
df
Out[1]: 
     T1     G1  VALUES       TIME
0   ABC   DATA    0.00 2015-01-01
6   ABC   DATA   18.60 2015-02-01
12  ABC   DATA    0.90 2015-03-01
18  ABC   DATA    6.90 2015-04-01
24  ABC   DATA    3.00 2015-12-01
1   ABC   TECH    8.90 2016-01-01
7   ABC   TECH    0.00 2016-02-01
13  ABC   TECH    0.00 2016-03-01
19  ABC   TECH    3.90 2016-04-01
25  ABC   TECH    0.00 2016-12-01
2   DEF   TECH    0.00 2020-01-01
8   DEF   TECH    9.00 2020-02-01
14  DEF   TECH    0.00 2020-03-01
20  DEF   TECH    8.06 2020-04-01
26  DEF   TECH    6.00 2020-12-01
3   GHI    OPT    0.00 2017-01-01
9   GHI    OPT    1.10 2017-02-01
15  GHI    OPT    9.80 2017-03-01
21  GHI    OPT    6.80 2017-04-01
27  GHI    OPT    0.00 2017-12-01
4   JKL   DATA    7.10 2018-01-01
10  JKL   DATA    2.10 2018-02-01
16  JKL   DATA    0.00 2018-03-01
22  JKL   DATA    0.00 2018-04-01
28  JKL   DATA    8.00 2018-12-01
5   JKL  OTHER    5.00 2020-01-01
11  JKL  OTHER    2.00 2020-02-01
17  JKL  OTHER    6.00 2020-03-01
23  JKL  OTHER    6.00 2020-04-01
29  JKL  OTHER    5.00 2020-12-01

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top