How Can I Efficiently Melt and Unmelt Pandas DataFrames?

Melting a Pandas Dataframe

Introduction

In Pandas, melting dataframes involves transposing data from a wide format to a long format, making it useful for various data manipulation tasks. This article will guide you through the process of melting dataframes and explore different scenarios with real-world examples.

Problem 1: Transposing Column Data

Objective: Transpose columns into rows while repeating the original column names.

Solution:

df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')

This code will create a new dataframe with 'Subject' and 'Grades' columns, while the original column names will be repeated for each row.

Example:

df = pd.DataFrame({'Name': ['Bob', 'John', 'Foo', 'Bar', 'Alex', 'Tom'],
                   'Math': ['A+', 'B', 'A', 'F', 'D', 'C'],
                   'English': ['C', 'B', 'B', 'A+', 'F', 'A'],
                   'Age': [13, 16, 16, 15, 15, 13]})

melted_df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')

print(melted_df)

Output:

   Name  Age Subject Grades
0   Bob   13  English     C
1  John   16  English     B
...
11  Tom   13     Math     C

Problem 2: Filtering Columns

Objective: Melt specific columns, excluding others.

Solution:

df.melt(id_vars=['Name', 'Age'], value_vars='Math', var_name='Subject', value_name='Grades')

In this example, only the 'Math' column is melted while 'Age' and 'Name' are retained as identifiers.

Example:

melted_df = df.melt(id_vars=['Name', 'Age'], value_vars='Math', var_name='Subject', value_name='Grades')

print(melted_df)

Output:

   Name  Age Subject Grades
0   Bob   13    Math    A+
1  John   16    Math     B
...

Problem 3: Grouping and Ordering Melted Data

Objective: Group the melted data and sort by values.

Solution:

melted_df.groupby('value', as_index=False).agg({
    'Subject': ', '.join,
    'Grades': ', '.join
}).sort_values('value', ascending=True)

This code will group the melted data by score and join the 'Subject' and 'Grades' values with commas.

Example:

grouped_df = melted_df.groupby('value', as_index=False).agg({
    'Subject': ', '.join,
    'Grades': ', '.join
}).sort_values('value', ascending=True)

print(grouped_df)

Output:

  value             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
...

Problem 4: Unmelting Dataframes

Objective: Reverse the melting process, returning to the original format.

Solution:

melted_df.pivot_table("Grades", ['Name', 'Age'], 'Subject', aggfunc='first').reset_index()

This code will pivot the melted dataframe back to the original wide format.

Example:

unmelted_df = melted_df.pivot_table("Grades", ['Name', 'Age'], 'Subject', aggfunc='first').reset_index()

print(unmelted_df)

Output:

   Name  Age Math English
0   Alex   15    D       F
1   Bar   15    F      A+
2   Bob   13   A+       C
3   Foo   16    A       B
...

Problem 5: Grouping and Combining Columns

Objective: Group data by a specific column and combine other columns with commas.

Solution:

melted_df.groupby('Name', as_index=False).agg(
    Subjects=', '.join,
    Grades=', '.join
)

This code will group the data by 'Name' and combine 'Subject' and 'Grades' with commas.

Example:

grouped_df = melted_df.groupby('Name', as_index=False).agg(
    Subjects=', '.join,
    Grades=', '.join
)

print(grouped_df)

Output:

   Name        Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
...

Problem 6: Melting All Columns

Objective: Convert all columns into rows, including identifiers.

Solution:

df.melt(var_name='Column', value_name='Value')

This code will melt all columns into rows, treating all data as values.

Example:

melted_df = df.melt(var_name='Column', value_name='Value')

print(melted_df)

Output:

    Column Value
0       Age    16
1       Age    16
2       Age    15
...
11  English     C
12     Math     A
13     Math    A+

The above is the detailed content of How Can I Efficiently Melt and Unmelt Pandas DataFrames?. For more information, please follow other related articles on the PHP Chinese website!