我有一个数据帧df:
Item Name Location Jan-Units Feb-Units Mar-Units Jan-Rev Feb-Rev Mar-Rev Jan-Trl Feb-Trl Mar-Trl
123 ABC CA 20 30 40 100 150 200 1.00 1.50 2.00
456 DEF AZ 25 35 45 125 175 225 1.25 1.75 2.25
我需要多次融化这个df,保持前3列不变,并根据融化列的前3个字符添加一个日期列。输出应为:
Item Name Location Date Units Rev Trl
123 ABC CA 01/01/2020 20 100 1.00
123 ABC CA 02/01/2020 30 150 1.50
123 ABC CA 03/01/2020 40 200 2.00
456 DEF AZ 01/01/2020 25 125 1.25
456 DEF AZ 02/01/2020 35 175 1.75
456 DEF AZ 03/01/2020 45 225 2.25
(实际df有2020年的所有月份和数百行(
首先重塑列的形状,然后使用pd.wide_to_long
:
df.columns = ["-".join(i.split("-")[::-1]) for i in df.columns]
print (pd.wide_to_long(df, stubnames=["Units","Rev","Trl"],
i=["Item","Name"], j="Date",
sep="-", suffix="w+")
.reset_index())
Item Name Date Location Units Rev Trl
0 123 ABC Jan CA 20 100 1.00
1 123 ABC Feb CA 30 150 1.50
2 123 ABC Mar CA 40 200 2.00
3 456 DEF Jan AZ 25 125 1.25
4 456 DEF Feb AZ 35 175 1.75
5 456 DEF Mar AZ 45 225 2.25