将长平面表格（每列包括年份和类别）转换为比较类别和年份的表格

Question

我一直在尝试创建一个程序，将具有20+个类别（年份和类别都在列中）的csv转换为一个将它们拆分为行和列的csv，但没有任何效果

如何在不必为每个CSV手动执行的情况下执行此操作？

我从来没有研究过它，所以我的知识是相当零散的，我所有的尝试目前已在大型低效的代码结束

顺便说一句，我这样做是为了我的学士学位论文，而不是为了投资或诸如此类的东西

当前显示的数据示例

df = pd.DataFrame({
   'Total Revenue 2006' : ['786'],
   'Total Revenue 2007' : ['643'],
   'Total Revenue 2008' : ['1200'],
   'Total Revenue 2009' : ['1456'],
   'Total Revenue 2010' : ['1675'],
   'Total Employees 2006' : ['42'],
   'Total Employees 2007' : ['55'],
   'Total Employees 2008' : ['65'],
   'Total Employees 2009' : ['45'],
   'Total Employees 2010' : ['60'],

我想拆分类别和年份，这样列就是年份，行就是类别

Answer 1

你也可以试试这个，虽然有点长：

transposed_df = df.transpose()
transposed_df.index.name = "Type"
transposed_df.columns = ["Value"]
transposed_df = transposed_df.reset_index()
transposed_df["Year"] = transposed_df.Type.apply(lambda x: x.rsplit(" ", 1)[-1])
transposed_df["Metric"] = transposed_df.Type.apply(lambda x: x.rsplit(" ", 1)[-0])
revenue_df = transposed_df[transposed_df.Metric=="Total Revenue"].set_index("Year")
employee_df = transposed_df[transposed_df.Metric=="Total Employees"].set_index("Year")
revenue_df.drop(["Type", "Metric"], inplace=True, axis=1)
revenue_df.columns = ["Revenue"]
employee_df.drop(["Type", "Metric"], inplace=True, axis=1)
employee_df.columns = ["TotalEmployees"]
combined_df = pd.concat([employee_df, revenue_df], axis=1)
combined_df.head()

    TotalEmployees  Revenue
Year        
2006    42  786
2007    55  643
2008    65  1200
2009    45  1456
2010    60  1675

Answer 2

给你

df = df.transpose()
df["temp"] = df.index
df["name"] = df["temp"].map(lambda x: x.rsplit(" ", 1)[0])
df["year"] = df["temp"].map(lambda x: x.rsplit(" ", 1)[1])
df.drop(columns="temp", inplace=True)
result = df.pivot(index='name', columns='year', values=0)

指 https://pandas.pydata.org/pandas-docs/stable/user_guide/reshaping.html 更多