Home > Back-end >  Turning loop of string inputs from Excel into Python lists
Turning loop of string inputs from Excel into Python lists

Time:10-28

I have an Excel file with three columns (Prefix, Feature, Values). All three are strings in Excel, but I need to turn the 'Values' entry from a string into a list when Prefix and Column equal the loop values.

Data looks like this:

Prefix Feature Value
Prefix_1 Feature1 Value1,Value2,Value3
Prefix_1 Feature2 Value4,Value5
Prefix_1 Feature3 Value6,Value7,Value8,Value9
Prefix_2 Feature4 Value10

It loops through all prefixes, then through all features, and I want to return the values in a list.

parametric_values = str(filtered_input.loc[filtered_input.Feature==column,'Value']).split(',')

filtered_input is a dataframe that is filtered to only the relevant prefixes and column is the value from the loop.

I would have expected parametric_values to be ['Value1','Value2','Value3'] for the first loop, but it returned as ['0 Value1', 'Value2', 'Value3\nName: Value', ' dtype: object']

I'm not sure why it returned a 0 to start with or the name and dtype object. What would I need to change with my code to get it to just return the values in the list?

CodePudding user response:

Use this instead:

filtered_input.loc[filtered_input.Feature==column,'Value'].str.split(',').squeeze()

CodePudding user response:

Alert From now on whenever you are trying to work with any pandas column values please avoid using the direct typecast ( bad practice ) and rather use astype() to change value type.

And Now For the solution to your problem please change your code as follows

parametric_values = filtered_input.loc[filtered_input.Feature==column,'Value'].astype(str).str.split(',').tolist()[0]

here,

filtered_input.loc[filtered_input.Feature==column,'Value'].astype(str).str.split(',')

This portion will only return a pandas series which is not the desired output so this series needs to be converted into a list. that's why tolist() is added. But when you make a pandas series a list that also contains a list within it. Which will eventually generate something like this.

[['Value1', 'Value2', 'Value3']]

which is also undesired output so lastly that [0] is added to get the actual list that you need. Hope you find it helpful.

  • Related