How to use Pandas to implement column-to-column statistics of data?

Efficiently implement data column-to-statistics with Pandas

In data analysis, flexible reorganization and statistical analysis of the data are often required. For example, convert a dataset containing dates and types into a statistical table of different types of counts per day. This article will demonstrate how to use the Pandas library to do this efficiently.

Suppose we have a data frame (DataFrame) containing two columns of 'date' (date) and 'type' (type), and the data example is as follows:

 <code>date type 2024-01-01 1 2024-01-01 2 2024-01-01 1 2024-01-02 3 2024-01-02 2 2024-01-02 3 2024-01-02 1 2024-01-02 1 2024-01-03 1 2024-01-03 4 2024-01-03 2 2024-01-03 5 ...</code>

The goal is to convert the data into the following format, showing the count of each type on each day:

 <code>date type1 type2 type3 type4 type5 2024-01-01 2 1 0 0 0 2024-01-02 2 1 2 0 0 2024-01-03 1 1 0 1 1 ...</code>

We can use Pandas' pd.get_dummies() and groupby() functions to achieve this. Here is the Python code:

 import pandas as pd

# Sample data = {
    'date': ['2024-01-01', '2024-01-01', '2024-01-01', '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-02', '2024-01-03', '2024-01-03', '2024-01-03'],
    'type': [1, 2, 1, 3, 2, 3, 1, 1, 1, 4, 2, 5]
}

df = pd.DataFrame(data)

# Use get_dummies() for one-hot encoding df_encoded = pd.get_dummies(df, columns=['type'], prefix='type')

# Use groupby() and sum() for group statistics result = df_encoded.groupby('date').sum()

# Print result print(df_encoded)
print("-" * 60)
print(result)

The code first uses pd.get_dummies() to convert the 'type' column into a dummy variable, and then uses groupby('date').sum() to group the dates and sum each type to finally get the target statistics table.

The output result is similar to:

<code> date type_1 type_2 type_3 type_4 type_5 0 2024-01-01 1 0 0 0 0 1 2024-01-01 0 1 0 0 0 2 2024-01-01 1 0 0 0 0 3 2024-01-02 0 0 1 0 0 4 2024-01-02 0 1 0 0 0 5 2024-01-02 0 0 1 0 0 6 2024-01-02 1 0 0 0 0 7 2024-01-02 1 0 0 0 0 8 2024-01-03 1 0 0 0 0 9 2024-01-03 0 0 0 1 0 10 2024-01-03 0 1 0 0 0 11 2024-01-03 0 0 0 0 1 ------------------------------------------------------------ type_1 type_2 type_3 type_4 type_5 date 2024-01-01 2 1 0 0 0 2024-01-02 2 1 2 0 0 2024-01-03 1 1 0 1 1</code>

Through this concise code, we can easily complete Pandas data column conversion statistics to improve data analysis efficiency.

The above is the detailed content of How to use Pandas to implement column-to-column statistics of data?. For more information, please follow other related articles on the PHP Chinese website!