Include a character in an excel python values

Asked

Viewed 41 times

1

I have a dataframe whose column has 12 characters. However most on the left are zeros and at the end of it I have the values. Examples:

inserir a descrição da imagem aqui

I’m trying to include a comma or dot before the last 2 characters but I can’t.

df ['Vlor_Correto'] = df ['Vlor_Correto'].astype(int) #I’ve used it to make it whole

df ['Vlor_Correto'] = df ['Vlor_Correto'].apply(lambda x: "{:_.2f}".format(x).replace('.', ',').replace('_', '.')) # And then this to format including 2 characters after the comma. But this just adds ,00. and in my case it doesn’t work.

I want the values to come out this way. Obs. I have values like thousand, so in some cases it would be necessary to understand that this 000000150259 can be a 1.502,59

inserir a descrição da imagem aqui

python lambda-expressions formatting
  • 1

    Try to make a left strip zero, convert to integer and split by 100.

  • The . astype(int) already does this by changing to integer it already removes the zeros on the left. The bigger question is the value does not contain "." or "," to be amended.

1 answer

3


To transform from the string, before doing the as_type(int):

df ['Vlor_Correto'] = df ['Vlor_Correto'].apply(lambda x: float(x[:-2] + "." + [-2:]))

This form uses Python’s "slicing" syntax to take the whole number from the beginning to the "-2" position" [:-2] : that is, the point where there are still two characters left at the end of the string, concatenate a "." and then every two-character number before the end until the end [-2:]. The result of this concatenation is passed to the call float that turns the string into a number.

Another way is, after having the value as number, divide it by 100:

df ['Vlor_Correto'] = df ['Vlor_Correto'].astype(float)
df['Vlor_Correto'] /= 100

(The operator /= is the inplace division, x /= 10 is the same as x = x / 10. Since the column is a Pandas Series, the division by a number already does the right thing which is to divide each element of the series and create a new series with the results)

A note in parallel is about the name of the column Vlor_Correto: It’s quite inconvenient to remember, to read and to type. The suggestion is either to use column names in English, with spaces and accents, so the output files are already correct: "Correct Value", or to use "Programming friendly" names in which you exchange spaces for "_", all in lowercase and without accents, and in any case abbreviating a single letter in the word "Value" only impairs readability (it requires more mental effort from those who are seeing the data and who are dealing with them). Even if you saved about 10 letters with that kind of abbreviation, it still wouldn’t be worth it.

Browser other questions tagged python lambda-expressions formatting

You are not signed in. Login or sign up in order to post.