Python Pandas DF Pivot and Groupby

user_14167354 0
Viewed 339

I need to iterate through my dataframe rows and pivot the single column bounding_box_y into 8 columns each time the value in text_y column changes.

original data frame

desired data frame

Can anyone help with some code that does NOT hardcode values into the code? The entire dataframe is over 6000 rows. I need to pivot the one column into 8 each time the value in another column changes.

Thanks!

pandas-groupby pandas pivot-table dataframe python-3.x
1 Answers

Please try to include your data as callable code, so others can easily copy/paste and experiment. In your case you can get it with df.head(16).to_dict('list'). I used the following

df = pd.DataFrame({
    'boundingBox_y': [183, 120, 305, 120, 305, 161, 182, 161, 318, 120, 381, 120, 382, 162, 318, 161],
    'text_y': (['FORM'] * 8) + (['ABC'] * 8),
    'confidence': ([0.987] * 8) + ([0.976] * 8)
    })

Then you can pivot your dataframe but you need to add a new column to hold the pivoted column names.

# rename the current values column
df.rename({'boundingBox_y': 'value'}, axis=1, inplace=True)

# create a column that contains the columns headers and can be pivoted
df['boundingBox_y'] = df.groupby(['confidence', 'text_y']).transform('cumcount')

# pivot your df
df = df.pivot(index=['confidence', 'text_y'],
    columns='boundingBox_y', values='value')

Output

boundingBox_y        0    1    2    3    4    5    6    7
confidence text_y
0.976      ABC     318  120  381  120  382  162  318  161
0.987      FORM    183  120  305  120  305  161  182  161
user_66928980
Related