Pandas pivot ussing column as suffix

I have a dataframe like the following example:

Id Type Value_1 Value_2
1234 A 1 2
1234 B 1 2
789 A 1 2
789 B 1 2
567 A 1 2

And I want to transform to get the following:

Id Value_1_A Value_1_B Value_2_A Value_2_B
1234 1 1 2 2
789 1 1 2 2
567 1 1

In summary: replicating the value columns using the ‘Type’ column as a suffix and convert the dataframe to a wide format.

Is there any clean and easy way to do it on pyspark dataframes or pandas?

Answer

In pandas we would pivot then collapse the Multi-Index with Index.map:

new_df = df.pivot(index='Id', columns='Type')
new_df.columns = new_df.columns.map('_'.join)
# Or with an explicit format string like:
# new_df.columns = new_df.columns.map('{0[0]}_{0[1]}'.format)
new_df = new_df.reset_index()

new_df:

     Id  Value_1_A  Value_1_B  Value_2_A  Value_2_B
0   567        1.0        NaN        2.0        NaN
1   789        1.0        1.0        2.0        2.0
2  1234        1.0        1.0        2.0        2.0

DataFrame and imports:

import pandas as pd

df = pd.DataFrame({'Id': {0: 1234, 1: 1234, 2: 789, 3: 789, 4: 567},
                   'Type': {0: 'A', 1: 'B', 2: 'A', 3: 'B', 4: 'A'},
                   'Value_1': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1},
                   'Value_2': {0: 2, 1: 2, 2: 2, 3: 2, 4: 2}})