为Pandas的每个领域创造12个月

Question

我有一个如下的数据集

Particulars "TotalBud"  "TotalTar"
Staff Cost   374.58      320.45 
Staff Expenses   15.81   13.53 
Supply Costs     56.35   48.21 
Labour costs     22.41   19.19 

我想生成一个数据帧，如下所示：

"Particulars"   "TotalBud"  "TotalBud_Apr"  "TotalBud_May"  "TotalBud_Jun"  "TotalBud_Mar"  "TotalTar"  "TotalTar_Apr"  "TotalTar_May"  "TotalTar_Feb"  "TotalTar_Mar"
Staff Cost   374.58      31.21   2.60    0.22    0.02    320.45      266.33      212.20      158.07  03.95 
Staff Expenses   15.81   1.32    0.11    0.01    0.00    13.53   11.25   8.97    6.69    4.41 
Supply Costs     56.35   4.70    0.39    0.03    0.00    48.21   40.07   31.93   23.79   15.65 
Labour costs     22.41   1.87    0.16    0.01    0.00    19.19   15.96   12.74   9.51    6.29 

我希望每个现有列的列从4月到3月，它们必须是x/12，其中x是该列的原始值。如果我的excel文件包含新列，代码应该能够迭代并为它们生成月份字段

Answer 1

import pandas as pd
import xlsxwriter

data = pd.read_excel('Budget.xlsx',sheet_name=0)
data.fillna(0,inplace=True)

new_colums = data.columns
new_colums = new_colums.insert(1,'Month')

master_data = pd.DataFrame(columns=new_colums)

month_list = ['APR-20','MAY-20','JUN-20','JUL-20','AUG-20','SEP-20','OCT-20','NOV-20','DEC-20','JAN-21','FEB-21','MAR-21']

def rowTranformation(row):
    global master_data
    temp = pd.DataFrame(columns=new_colums)
    for i in range(12):
        val = []
        val.append(row[new_colums[0]])
        val.append(month_list[i])
        for j in new_colums[2:]:
            val.append(round(row[j]/12,2))
        temp.loc[i] = val
    master_data = master_data.append(temp,ignore_index=True)


data.apply(lambda x: rowTranformation(x),axis=1)
master_data.to_excel('output.xlsx',sheet_name='MonthlyView',index=False)