Convert csv File format with pandas?

Question

I am not familar with pandas, now. I have a csv file, which has the format and some other columns, simplified here:

USER; so; ROLE
hugo; a; role_x
hugo; a; role_y
hugo; b; role_x
hugo; b; role_y
otto; role_x

and I need the output in this form, where the role category should be listed only once:

USER; ROLES
hugo; role_x, role_y
otto; role_x

I do this with iteration and dictionary. Is this easier by pandas?

With the referenced solution I get something like:

USER; ROLES
hugo; role_x, role_y, role_x, role_y
otto; role_x

But the result should show the same role only once.

strongly related https://stackoverflow.com/q/27298178/12846804 — OCa, Sep 01 '23 at 14:25

score 2 · Answer 1 · answered Sep 01 '23 at 14:15

2

Pandas is great for data aggregation and manipulation, you can group by user very easily:

import pandas as pd

# Read the CSV file into a pandas DataFrame
df = pd.read_csv('your_file.csv', sep=';')

# Do your grouping
result_df = df.groupby('USER')['ROLE'].agg(', '.join).reset_index()

# Change the 'ROLE' column to 'ROLES'
result_df.rename(columns={'ROLE': 'ROLES'}, inplace=True)

# Save the result
result_df.to_csv('output_file.csv', sep=';', index=False)

Here are some nice examples for how to use the groupby function: https://www.geeksforgeeks.org/python-pandas-dataframe-groupby/

answered Sep 01 '23 at 14:15

Ada

427
2
13

Close the my solution, but not 100%. My failure in the question, sorry. – Paul-ET Sep 01 '23 at 15:48
@Paul-ET this solution gives your desired output. I just double checked it. – stefan_aus_hannover Sep 01 '23 at 16:00
@stefan_aus_hannover I edited my question to make it more clear. Thanks for your replay! – Paul-ET Sep 01 '23 at 16:04
This maybe what you are actually looking for `df.groupby('USER')['ROLE'].apply(set).agg(', '.join).reset_index()` – stefan_aus_hannover Sep 01 '23 at 16:26

Convert csv File format with pandas?

1 Answers1