Generate incremental values by rows with timestamp in Pandas-CodePudding

I'm wondering if there's a way I can generate rows of incremental values with timestamp rolling at the same time. At the moment, I am thinking to do it manually by increasing the values and timestampe in a separate dataframes and trying to join them together. Any better solution?

import pandas as pd
from pandas import Timestamp

data = {
    'apple': {Timestamp('2020-01-01 00:00:00'): 10},
    'orange': {Timestamp('2020-01-01 00:00:00'): 20},
    'lemon': {Timestamp('2020-01-01 00:00:00'): 30}
}

df = pd.DataFrame.from_dict(data)
            apple  orange  lemon
2020-01-01     10      20     30

Expected output:


                apple              orange               lemon
date
2021-01-01          10                 20                  30
2022-01-01          20                 30                  40
2023-01-01          30                 40                  50

CodePudding user response：

IIUC use:

#reepat index values
df = df.loc[df.index.repeat(3)]

#counter by duplicated index values
s = df.groupby(level=0).cumcount()
#multiple counter by 10 and add to all columns
df = df.add(s.mul(10), axis=0)
#if always January 1 add years by counter Series
df.index = pd.to_datetime(df.index.year   s   1, format='%Y')
print (df)
            apple  orange  lemon
2021-01-01     10      20     30
2022-01-01     20      30     40
2023-01-01     30      40     50

CodePudding user response：

You can try convert each element in dataframe to list then explode

n = 3

df = (df.applymap(lambda x: range(x, x 10*n, 10))
      .reset_index())
df['index'] =df['index'].apply(lambda d: pd.date_range(d, freq='YS', periods=n))
df = (df.explode(df.columns.tolist())
      .set_index('index'))

print(df)

           apple orange lemon
index
2020-01-01    10     20    30
2021-01-01    20     30    40
2022-01-01    30     40    50