Python Dataset loses a column randomly

[Rodriguez]

When I run a script normally on Python, it runs smoothly, however, when running the same script on Power Bi, it does not work as expected.

Hi, I have the following python script that runs perfectly on VS Code:

# 'dataset' holds the input data for this script

import pandas as pd
import numpy as np

dataset = pd.read_csv('PBI_Test.csv')

used = dataset[dataset.PPSNU_1D_Qty > 0].sort_values(by='luci_price',ascending=False)[['service_order_id','item_product_id','Part_Type']].groupby(by='service_order_id')[['item_product_id','Part_Type']].agg(' | '.join).reset_index()
used.columns = ['service_order_id','used_item_id','used_part_type']

req = dataset[dataset.PPSNR_1D_Qty > 0].sort_values(by='luci_price',ascending=False)[['service_order_id','item_product_id','Part_Type']].groupby(by='service_order_id')[['item_product_id','Part_Type']].agg(' | '.join).reset_index()
req.columns = ['service_order_id','req_item_id','req_part_type']

final = pd.merge(left=used,right=req,on='service_order_id')
final

however, when I use the same exact script on Power Bi to manipulate the dataset, it gives me the following error:

 ValueError: Length mismatch: Expected axis has 2 elements, new values have 3 elements

I know the error occurs on this line because "req" does not have a Part_Type column but I don't understand why it doesn't, it should.

req.columns = ['service_order_id','req_item_id','req_part_type']

Any help would be greatly appreciated.

[Anonymous]

Hi @Rodriguez ,

Because the original data frame has only 2 columns, but you are renaming it with 3 new column names. The mismatch results in the error.

Since the error comes from the fact that the actual number of columns in the data frame doesn’t match the column names to be assigned, you need to double check and make sure they are equal. i.e. Do some print like len(df.columns) and len(newColumnNames) and see if they are the same. If they are not, adjust your new column names so that every column in your data frame gets exactly one name.

I have also found a similar post, please refer to it to see if it helps you.

ValueError: Length mismatch: Expected axis has 7 elements, new values have 5 elements 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rodriguez]

The real issue is it should have three columns has I am grouping by 1, and conducting a join on two columns:

req = dataset[dataset.PPSNR_1D_Qty > 0].sort_values(by='luci_price',ascending=False)[['service_order_id','item_product_id','Part_Type']].groupby(by='service_order_id')[['item_product_id','Part_Type']].agg(' | '.join).reset_index()

It's the exact same process for the "used" dataframe, which does not get that error, but the "req" does. Not sure how or why the "req" dataframe ends up with only two columns when it should actually have 3. 

[lbendlin]

Would you mind sharing the test CSV?

Also, why filter the file twice?

used = dataset[dataset.PPSNU_1D_Qty > 0].sort_values(by='luci_price',ascending=False)[['service_order_id','item_product_id','Part_Type']].groupby(by='service_order_id')[['item_product_id','Part_Type']].agg(' | '.join).reset_index()
req = used
used.columns = ['service_order_id','used_item_id','used_part_type']
req.columns = ['service_order_id','req_item_id','req_part_type']

[Rodriguez]

They are being filtered on different columns, so one is for Parts Used the other for Parts Requested. 

I am unable to attach CSV, Excel or txt files to this reply, any idea on how I can share my test file?

[lbendlin]

Do you have the option to post the file to one of the internet file share services?

[Rodriguez]

I actually ran the same exact Python Code on an import of the csv file and it worked as expected. I am at a loss.