How to Group Excel Data by Column and Create a Dictionary of Lists in Python?

GroupBy Excel Results to Dictionary of Lists

You have an Excel spreadsheet with data organized into three columns: Column1, Column2, and Column3. You want to extract this data and group it by Column1 so that each unique value in Column1 corresponds to a list of values from Column3.

Code:

You've already tried using the groupby() function on Column1, but the output contains indices instead of the actual values from Column3. To correct this, you need to specify which column you want to group on and which column you want to extract:

<code class="python">df = pandas.read_excel(r"e:\test_data.xlsx", sheetname='mySheet', parse_cols=['Column1', 'Column3'])
result = df.groupby('Column1')['Column3'].apply(list).to_dict()</code>

Explanation:

• groupby() groups the data by the values in Column1.
• apply(list) applies the list function to each subgroup, converting the values in Column3 to a list.
• to_dict() converts the grouped data into a dictionary, where the keys are the values in Column1 and the values are the lists of values from Column3.

Alternative Code:

Another way to achieve the same result is using a dictionary comprehension:

<code class="python">result = {k: list(v) for k, v in df.groupby('Column1')['Column3']}</code>

Output:

Both code snippets produce the desired output:

{0: [1], 1: [2, 3, 5], 2: [1, 2], 3: [4, 5], 4: [1], 5: [1, 2, 3]}

The above is the detailed content of How to Group Excel Data by Column and Create a Dictionary of Lists in Python?. For more information, please follow other related articles on the PHP Chinese website!