Saya berjuang dengan cara memutar kerangka data dengan kolom multi-diindeks. Pertama saya mengimpor data dari file .xlsx dan sejak saat itu saya mencoba membuat Dataframe tertentu. Catatan: Saya tidak diizinkan ...

2
JDavid046 6 April 2021, 00:27

1 menjawab

Jawaban Terbaik

Anda harus membersihkannya sedikit, karena saya tidak tahu bagaimana kolom Total harus ditangani.

Kode di bawah ini membaca file excel sebagai MultiIndex, sedikit modifikasi nama kolom, sebelum menumpuk dan mengekstrak kolom Tahun dan Bulan.

df = pd.read_excel("Downloads/myFile.xlsx",  header=[0,1], index_col=[0, 1, 2])
df.index.names = ['Project', 'KPI', 'Metric']
df.columns = df.columns.delete(-1).union([('Total', 'Total')])
df.columns.names = ['Month_Year', 'Values']
(df
 .stack(level = 0)
 .rename_axis(columns=None)
 .reset_index()
 .assign(Year = lambda df: df.Month_Year.str.split(" ").str[-1],
         Month = lambda df: df.Month_Year.str.split(" ").str[0])
 .drop(columns='Month_Year')
)

      Project      KPI            Metric    Real   Target  Total   Year     Month
0   Project 1  Numeric  Project 1 Metric    10.0     30.0    NaN   2019     April
1   Project 1  Numeric  Project 1 Metric   651.0  51651.0    NaN   2019  February
2   Project 1  Numeric  Project 1 Metric   200.0    215.0    NaN   2019   January
3   Project 1  Numeric  Project 1 Metric     2.0      5.0    NaN   2019     March
4   Project 1  Numeric  Project 1 Metric     NaN      NaN    9.0  Total     Total
5   Project 2  General  Project 2 Metric    20.0     10.0    NaN   2019     April
6   Project 2  General  Project 2 Metric   500.0    100.0    NaN   2019  February
7   Project 2  General  Project 2 Metric   749.0     12.0    NaN   2019   January
8   Project 2  General  Project 2 Metric     1.0      7.0    NaN   2019     March
9   Project 2  General  Project 2 Metric     NaN      NaN    7.0  Total     Total
10  Project 3  Numeric  Project 3 Metric    30.0     20.0    NaN   2019     April
11  Project 3  Numeric  Project 3 Metric   200.0     55.0    NaN   2019  February
12  Project 3  Numeric  Project 3 Metric  5583.0     36.0    NaN   2019   January
13  Project 3  Numeric  Project 3 Metric     3.0      7.0    NaN   2019     March
14  Project 3  Numeric  Project 3 Metric     NaN      NaN    4.0  Total     Total
1
sammywemmy 5 April 2021, 22:31