Pandas pivot_table dropped a column, although no aggregation is done, only reshape

Question

Im trying to use Pandas pivot_table function to reshape my data-frame. But it fails on specific rows and I get the message:

FutureWarning: pivot_table dropped a column because it failed to aggregate. This behavior is deprecated and will raise in a future version of pandas. Select only the columns that can be aggregated.
  pivoted = df_first_1000.pivot_table(index=['time', 'motor'], columns='key', values='value')

I have data-frame with the culomns: ['time', 'motor','key','value'] and I want to reshape it that the variables in the 'key' column will be columns and the values under 'value' column will be coordinated.

like this, example of the origin df:

                        time    motor               key      value
1125 2023-01-02 11:41:10.229  MOTOR_5             state          1
1126 2023-01-02 11:41:10.313  MOTOR_5             right  -8.832798
1127 2023-01-02 11:41:10.313  MOTOR_5              left  -9.093866
1128 2023-01-02 11:41:10.313  MOTOR_5               pos   0.213856
1129 2023-01-02 11:41:10.313  MOTOR_5               vel   0.024477
1130 2023-01-02 11:41:10.314  MOTOR_5              temp  27.969526
1131 2023-01-02 11:41:10.314  MOTOR_5          measured   0.000000

after the commands:

pivoted = df.pivot_table(index=['time', 'motor'], columns='key', values='value')
pivoted.reset_index(inplace=True)
pivoted.columns.name = None

I get:

                     time    motor     state      measured     pos            left     right       temp       vel
0 2023-01-02 11:41:10.229  MOTOR_5       1.0           NaN       NaN          NaN       NaN        NaN       NaN
1 2023-01-02 11:41:10.313  MOTOR_5       NaN           NaN  0.213856      -9.093866    -8.832798   NaN     0.024477
2 2023-01-02 11:41:10.314  MOTOR_5       NaN           0.0       NaN          NaN       NaN       27.969526  NaN

I do the same commands in a loop to handle similar data-frames like the origin but its fails and I cannot understand why. I don't even use the aggfunc argument of the pivot_table.

example of rows in data-frame id doesn't work:

                        time    motor               key      value
1129 2023-01-02 11:41:10.832  MOTOR_6             state          1
1130 2023-01-02 11:41:10.849  MOTOR_6               vol  32.202164
1131 2023-01-02 11:41:10.849  MOTOR_6               vol   32.20553

I get:

FutureWarning: pivot_table dropped a column because it failed to aggregate. This behavior is deprecated and will raise in a future version of pandas. Select only the columns that can be aggregated.
  pivoted = df_first_1000.pivot_table(index=['time', 'motor'], columns='key', values='value')
                     time    motor
0 2023-01-02 11:41:10.832  MOTOR_6
1 2023-01-02 11:41:10.849  MOTOR_6

So I would like to know:

1. Why im getting this warning and it drops the relevant columns?
2. Is there a better way to debug on what rows it fails? (I have a big dataframe and write now im looking for the relevant rows by cutting raws thats work)
3. All I want is to reshape the df's and than compute a few things ' is there a better way?

Answer 1

Why im getting this warning and it drops the relevant columns?

Because pivot_table by default aggregate mean, so if column value is not numeric it raise warning.

Solutions should be:

df.pivot(index=['time', 'motor'], columns='key', values='value')

df['value'] = pd.to_numeric(df['value'], errors='coerce')
df.pivot_table(index=['time', 'motor'], columns='key', values='value')

Is there a better way to debug on what rows it fails? (I have a big dataframe and write now im looking for the relevant rows by cutting raws thats work)

You can test which values are not numeric:

df[pd.to_numeric(df['value'], errors='coerce').isna() & df['value'].notna()]

All I want is to reshape the df's and than compute a few things ' is there a better way?

If need pivoting seems not. Depends what is few things.