Home>
Thing I want to do
  1. The columns are product name, total sales, sales start period, sales end period, sales period, and average monthly sales.
  2. I want to display the average monthly sales by row: product name, column: year/month, and corresponding to the sales period for each product. (As shown in the image)
  3. I am plunging into the pandas dataframe in the 1 state, but I am not sure how to bring it to the 2 state, so I would appreciate it if you could tell me.

*Some data names have been changed.

image

] (0fab6b8efddf4993e1c2b8af7da0ef5a.jpeg)

  • Answer # 1

    It's a long to wide in data processing.
    There are various methods, for example here

    https://note.nkmk.me/python-pandas-stack-unstack-pivot/

  • Answer # 2

    DataFrame.apply()In the function called bypd.date_range()It can be realized by returning the Series data using the Index generated in.

    import pandas as pd
    df = pd.DataFrame({'Product':['A','B','C'],
                       'Start of sale': ['2019/12','2018/3','2019/7'],
                       'End of Sale': ['2020/2','2019/7','2020/6'],
                       'Average monthly sales': [1000,2000,3000]})
    ret = df.set_index('product', drop=True).apply(lambda d:pd.Series(d['average sales per month'], index=pd.date_range(start=d['sales start time' ], end=d['End of sale'], freq='MS')), axis=1).fillna(0)
    print(ret)
    # 2018-03-01 2018-04-01 2018-05-01 2018-06-01 2018-07-01 2018-08-01 ... 2020-01-01 2020-02-01 2020-03-01 2020 -04-01 2020-05-01 2020-06-01
    #Product                                                                          ...
    #A 0.0 0.0 0.0 0.0 0.0 0.0 ... 1000.0 1000.0 0.0 0.0 0.0 0.0
    #B 2000.0 2000.0 2000.0 2000.0 2000.0 2000.0 ... 0.0 0.0 0.0 0.0 0.0 0.0
    #C 0.0 0.0 0.0 0.0 0.0 0.0 ... 3000.0 3000.0 3000.0 3000.0 3000.0 3000.0