Formatting the data which increases monotonically in Python

Question

I have formatted the data according to the need. Now my final data or dataframe is not monotonically increasing whereas the input data is increasing monotonically according to the 1st column field (freq). Here is the link for Data_input_truncated.txt. My python code is in the below:

import pandas as pd

#create DataFrame from csv with columns f and v 
df = pd.read_csv('Data_input.txt', sep="\s+", names=['freq','v'])

#boolean mask for identify columns of new df   
m = df['v'].str.endswith(')')
#new column by replace NaNs by forward filling
df['g'] = df['v'].where(m).ffill()
#get original ordering for new columns
cols = df['g'].unique()
#remove rows with same values in v and g columns
df = df[df['v'] != df['g']]
#reshape by pivoting with change ordering of columns by reindex
df = df.pivot('freq', 'g', 'v').rename_axis(None, axis=1).reindex(columns=cols).reset_index()

df.columns = [x.replace('(','').replace(')','').replace(',',':') for x in df.columns]
df.to_csv('target.txt', index=False, sep='\t')

Now the created target.txt is not monotonic. Here is the link for target.txt. How can I make it monotonic before saving as a file?

I am using Spyder 3.2.6 (Anaconda) where python 3.6.4 64-bit is embedded.

@VivekKalyanarangan, I have shortened the data and also code. Is that fine now? — aguntuk
– aguntuk, Commented Aug 16, 2018 at 13:54

Ben.T · Accepted Answer · 2018-08-16 16:21:11Z

0

The problem is that your data is str and not a float, and while pivoting, it is reorder with alphabetical order. One option could be to change the type of the freq column to float, and then if the formatting as scientific number is important, you can set the float_format parameter during to_csv:

### same code before
#remove rows with same values in v and g columns
df = df[df['v'] != df['g']]
# convert to float
df['freq']= df['freq'].astype(float)

#reshape by pivoting with change ordering of columns by reindex
df = df.pivot('freq', 'g', 'v').rename_axis(None, axis=1).reindex(columns=cols).reset_index()

df.columns = [x.replace('(','').replace(')','').replace(',',':') for x in df.columns]
df.to_csv('target.txt', index=False, sep='\t', float_format='%.17E' ) # add float_format='%.17E'

Note float_format='%.17E' means scientific notation with 17 numbers after the . as in your input, but you can change this number to anyone you want if they are not important.

EDIT: I get this result in target.txt (first 5 rows and 3 columns)

freq    R1:1    R1:2
0.00000000000000000E+00 4.07868642871600962E0   3.12094533520232087E-13
1.00000000000000000E+06 4.43516799439728793E0   4.58503433913467795E-3
2.00000000000000000E+06 4.54224931058591253E0   1.21517855438593236E-2
3.00000000000000000E+06 4.63952376349496909E0   2.10017318391844077E-2
4.00000000000000000E+06 4.74002677709486608E0   3.05258806632440871E-2

edited Aug 16, 2018 at 16:21

answered Aug 16, 2018 at 15:00

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

aguntuk Over a year ago

Thanks! It worked partially as you said. It is monotonically formatting only after the decimal point, but not as a whole number. How to change it to float for the whole number? Here is the link of my target_new.txt file for your convenience.

Ben.T Over a year ago

@aguntuk the target_new.txt seems to be the same than the target.txt. can you tell me where to look for the difference, I don't see it?

aguntuk Over a year ago

after 1.00000000000000000E6, it should be 2.00000000000000000E6 then 3.00000000000000000E6 but instead it is 1.00000000000000000E7, 1.10000000000000009E7...2.00000000000000000E6 comes after 1.89999999999999991E7

aguntuk Over a year ago

I don't know what do you mean it by it was before. The original data Data_input.txt is monotonic. After the data manipulation as it is non-monotonic when I use this data for input in my circuit in another program (Cadence) it asks for monotonic data and returns me an error as the data is not monotonic.

Ben.T Over a year ago

@aguntuk Sorry, by before I meant in the link to target.txt you provided in the question. What I don't understand is why the order is not good in your result. I tried with your data and I get the monotonic incremental value for the freq as you can see in my edit

|

Collectives™ on Stack Overflow

Formatting the data which increases monotonically in Python

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related