Reshaping DataFrame; Melt/Pivot

Body:

Problem:

I have a wide DataFram that I'm struggling to reshape and aggregate using pandas.

Sample Data:

import pandas as pd
from io import StringIO

# Sample data
data = """
,Unnamed: 1,Unnamed: 2,Unnamed: 3
3,Bezeichnung (Bezirksregion),Straftaten \\n-insgesamt-,Raub
4,Mitte,81178,823
5,Tiergarten Süd,4113,37
6,Regierungsviertel,6251,41
7,Alexanderplatz,18999,163
"""

# Load data into a DataFrame
df = pd.read_csv(StringIO(data), index_col=0)

# Display the DataFrame
print("Loaded DataFrame:")
print(df)

Desired Output:

desired_output = {
    'Region': ['Mitte', 'Tiergarten Süd', 'Regierungsviertel', 'Alexanderplatz'],
    'Crimes': ['Straftaten -insgesamt-', 'Straftaten -insgesamt-', 'Raub', 'Raub'],
    'count': [81178, 4113, 41.33, 163],
}

Current Approach:

melted_df = pd.melt(df, id_vars=[df.columns[0]], var_name='crime', value_name='crime_count')

Issues:

Result not as expected:

Assistance Requested:

How can I reshape the DataFrame to achieve the desired output?

IIUC, you need to rename the columns using the first row of your data, then melt it on the first column and rename the resultant dataframe's columns:

res = (df
    .set_axis(df.iloc[0], axis=1)
    .drop(df.index[0])
    .melt(id_vars=df.iloc[0,0])
    .set_axis(['Region', 'Crimes', 'Count'], axis=1)
)

Output for your sample data:

              Region                    Crimes  Count
0              Mitte  Straftaten \n-insgesamt-  81178
1     Tiergarten Süd  Straftaten \n-insgesamt-   4113
2  Regierungsviertel  Straftaten \n-insgesamt-   6251
3     Alexanderplatz  Straftaten \n-insgesamt-  18999
4              Mitte                      Raub    823
5     Tiergarten Süd                      Raub     37
6  Regierungsviertel                      Raub     41
7     Alexanderplatz                      Raub    163