How Can Pandas' `melt` Function Reshape Data with Date Columns into Rows?

Convert Columns into Rows with Pandas

When tabular data contains dates as column headers, converting those columns into rows with corresponding values can be a valuable task. To achieve this transformation, Python's Pandas library offers a convenient solution.

Problem

The provided dataset exhibits a structure where information is grouped by location for various dates, each represented by a distinct column header. The goal is to reshape this data into a format where each row represents a location, date, and associated value.

Solution

To convert the columns into rows, Pandas provides the melt function. This function allows us to specify which columns should serve as column headers and which should serve as row headers. In this context, name and location are set as row headers, and the date headers are melted into a single Date column, while their values become the Value column.

df.melt(id_vars=["location", "name"],
        var_name="Date",
        value_name="Value")

The resulting DataFrame will be similar to the expected output:

  location  name        Date  Value
0        A  "test"    Jan-2010     12
1        B   "foo"    Jan-2010     18
2        A  "test"    Feb-2010     20
3        B   "foo"    Feb-2010     20
4        A  "test"  March-2010     30
5        B   "foo"  March-2010     25

For older versions of Pandas (<0.20), a combination of pd.melt and sorting can achieve the desired result:

df2 = pd.melt(df,
                  id_vars=["location", "name"], 
                  var_name="Date",
                  value_name="Value")
df2 = df2.sort(["location", "name"])

The above is the detailed content of How Can Pandas' `melt` Function Reshape Data with Date Columns into Rows?. For more information, please follow other related articles on the PHP Chinese website!