Hello, everyone

Recently, I saw a group of friends discussing the problem of changing a wide table to a long table. In fact, this kind of requirement is also very common in our daily data processing. Today we will take a look at how Excel and Python implement this requirement.

Directory:

  1. Excel reverse perspective skills
  2. Pandas inverse perspective skills

1. Excel reverse perspective skills

Excel uses Power Query to reverse perspective.

Step 1: Select data, then in the menu bar – Data – click From Table/Region

Select data – from table

Step 2: When creating the table, select whether to include the title (not in this case)

Create a table

Step 3: The Power Query editor appears after clicking the confirm button above

Power Query editor

Step 4: Control, select all columns that you want to manipulate (or shift takes columns continuously), then go to Transform – click Reverse Perspective columns

Inverse perspective column

Step 5: You can see the results we need

Reverse fluoroscopic result

Step 6: Click the file in the upper left corner, select Close and upload

Upload the data

Step 7: We find that the table 1 TAB appears in the original table, which contains the reverse pivot result we expect. Done! (Delete irrelevant column attributes)

End result data

2. Pandas reverse perspective skills

Pandas provides a simple method for manipulating perspective in reverse order to see how it works.

Melt function method is recommended:

df.melt(
    id_vars=None,
    value_vars=None,
    var_name=None,
    value_name='value',
    col_level: 'Level | None' = None,
    ignore_index: 'bool' = True,
) -> 'DataFrame'
Docstring:
Unpivot a DataFrame from wide to long format, optionally leaving identifiers set.
Copy the code

Id_vars: tuple, list, or (optionally) nDARray, used as columns to identify variables

Value_vars: Tuple, list or Ndarray, optional, columns to deselect perspective. If not specified, all columns that are not set to ID_vars are used

Var_name: Scalar, the name used for the “variable” column. If None, use frame.columns. Name or “variable”

Value_name: Scalar, with a default value of “value”, is used for the name of the “value” column

Col_level: int or STR, optional, if the column is MultiIndex, use this level to melt

I won’t give you an example, just take the case data!

XLSX, header=None) df = pd.read_excel(r'0927 test data.xlsx ', header=NoneCopy the code

Data preview

Melt (id_vars=df.columns[:2], value_vars=df.columns[:2], Sort_values (by=[0,1]) # dropna() # drop rows with null values)Copy the code

The results of

Is it very convenient, a direct function can be done, thanks to the group member 1px to provide ideas.

This is a solution I’ve dealt with for a friend before, but when I saw this problem, I used a more complicated solution, which was really funny.

Let’s also look at this more cumbersome solution:

The core is Explode

data = df.iloc[:,:2].copy()
data
Copy the code

Initialize the data

Data [' secondary '] = list(df.loc[:,2:].values) dataCopy the code

Secondary column store store information list

Data.explode (column=' explode ').dropna()Copy the code

Explosion column completion requirements

That’s all for this time, around the conversion of a wide table to a long table, which is an inverse pivot operation. Excel and Pandas provide simple, straightforward solutions that will help you solve problems efficiently.

However, we can also exercise our logical thinking ability through some other ideas, which may strengthen our ability to deal with complex problems.