Python对每一列按特定行划分行

Question

我需要将每一行除以数据框架中每一列的特定/设置行。在这种情况下，我需要将每行除以每个时间段的收入。我想知道每个账户收入的百分比。我还想弄清楚如何使动态任何数量的列。

我当前的数据帧:

data = {'202112 YTD': {'Gross Margin': 200000,
'Other (Income) & Expense': -100000,
'Revenue': 5000000,
'SG&A Expense': 150000,
'Segment EBITDA': 200000},
'202212 YTD': {'Gross Margin': 2850000,
'Other (Income) & Expense': -338000,
'Revenue': 6000000,
'SG&A Expense': 15000,
'Segment EBITDA': 200000}}

df = pd.DataFrame.from_dict(data)
df

所需输出:

outdata = {'202112 YTD': {'Gross Margin': 0.040,
'Other (Income) & Expense': -0.020,
'Revenue': 1,
'SG&A Expense': 0.030,
'Segment EBITDA': 0.040},
'202212 YTD': {'Gross Margin': 0.475,
'Other (Income) & Expense': -0.056,
'Revenue': 1,
'SG&A Expense': 0.003,
'Segment EBITDA': 0.033}}

outdf = pd.DataFrame.from_dict(outdata)
outdf

请帮忙。最初的尝试是像下面这样构建解决方案:

import copy
import pandas as pd
original_table = [
{'name': 'Alice', 'age': 25, 'gender': 'Female'},
{'name': 'Bob', 'age': 32, 'gender': 'Male'},
{'name': 'Charlie', 'age': 40, 'gender': 'Male'},
{'name': 'Daisy', 'age': 22, 'gender': 'Female'},
{'name': 'Eve', 'age': 18, 'gender': 'Female'},
]

# Duplicate the table using copy.deepcopy()
duplicate_table = copy.deepcopy(original_table)
# Choose a specific column to divide the rows by
column_name = 'age'
divisor_value = original_table[3][column_name]
# Iterate over the rows in the duplicate table and divide each column by the divisor value
for i, row in enumerate(duplicate_table):
if column_name in row:
duplicate_table[i][column_name] = row[column_name] / divisor_value
else:
print(f"column: {column_name} not found in table")
# Convert the duplicate table to a DataFrame
duplicate_df = pd.DataFrame(duplicate_table)
# Print the duplicate DataFrame
duplicate_df
duplicate_df 

Answer 1

直接使用:

outdf = df.div(df.loc['Revenue']).round(3)

输出:

202112 YTD  202212 YTD
Gross Margin                    0.04       0.475
Other (Income) & Expense       -0.02      -0.056
Revenue                         1.00       1.000
SG&A Expense                    0.03       0.002
Segment EBITDA                  0.04       0.033