我有一个 Excel 工作表,其中的数据以下列方式存储。
我想将这些数据组织成一个 Pandas 数据框,使其看起来像下面这样......
将数据设置为这样的格式将使我能够轻松地使用数据透视表并进行分析。
如果在 excel 中也有一种简单的方法可以做到这一点,请告诉我。 Python 将是更好的选择,因为我有数百个这样的表,每个表都有数百行和列。
请您参考如下方法:
这就是我用 Python 解决问题的方式,尽管可能有更优雅的解决方案。
首先我会使用 openpyxl 解析月/年值
from openpyxl import load_workbook
wb = load_workbook('data_so.xlsx')
sheet_ranges = wb['Sheet1']
year = str(sheet_ranges['A1'].value)
month = str(sheet_ranges['B1'].value)
从那里我发现 pandas 包对于您需要的操作类型非常有用,设置多索引允许轻松 reshape (通过堆栈)。
import pandas as pd
df = pd.read_excel('data_so.xlsx', skiprows=3)
columns = pd.MultiIndex.from_tuples([('A', 'Price'), ('A', 'Quantity'),
('B', 'Price'), ('B', 'Quantity'),
('C', 'Price'), ('C', 'Quantity')],
names=['letters', 'index'])
index = pd.MultiIndex.from_arrays(df.ix[:,:2].values.T,names=('Item', 'Code'))
df2 = pd.DataFrame(df.ix[:,2:].values, columns=columns, index=index)
df3 = df2.stack('letters').reset_index()
然后我们可以将月份和年份设置为我们收集的值:
df3['Year'] = year
df3['Month'] = month
您可以进一步调用 MultiIndex 和堆栈来安排价格和数量到您想要的。这不是很干净,但可以解决您的问题。
df3.index = pd.MultiIndex.from_arrays(df3[['Item','Code','letters','Year','Month']].values.T,
names=('Item', 'Code','letters','Year','Month'))
df4 = df3[['Price','Quantity']].stack()
df5 = pd.DataFrame(df4)
df5.columns = ['Value']
df5.reset_index()
