Python Forum

HI
Do anyone know how to convert element in the list to float, and write them into excel?
My code will read a file and write it into excel, attached picture. But I wish to convert the red mark into float, else in excel it will occur exclamation mark.

I　try many methods using the index and split but still not working.
test.txt:

Quote:=========================UL=========================
datettime ingress-traffic egress-traffic
20230206.160207.082170 0.000000 0.000000
20230206.160212.099560 0.000000 0.000000
20230206.160217.116930 0.000000 0.000000
20230206.160222.135234 0.001146 0.001100
20230206.160227.152599 0.001410 0.001346

My source code:

lists = {}
current_key = None
with open ('test.txt', 'r')as myfile:  
    readline=myfile.read().splitlines()
    for line in readline:
        #print(line)
        if "=" in line:
            current_key = line.strip("=")
           
            lists[current_key] = []
        else:
            assert current_key is not None # there shouldn't be data before a header
            lists[current_key].append(line)

for i in lists["UL"]:
    i=i.split(' ')
    UL.append(i)

import pandas as pd
df1 = pd.DataFrame(UL)
df1 = df1.rename(columns=df1.iloc[0]).drop(df1.index[0])

# To Excel
with pd.ExcelWriter('out.xlsx', engine='xlsxwriter') as writer:
    df1.to_excel(writer, 'sheet1', index=False)  
    worksheet = writer.sheets['sheet1']  
    #worksheet.autofit()    
    worksheet.set_column(1, 3, 25)

Ｉtry using below seems not working. Does anyone have any ideas to convert the last two value into float and wiite into excel with out occurring exclamation mark.

UL =[]
#ULindex= lists["UL"][0] #get header title
#UL.append(ULindex)
ULrest=lists["UL"][1:]
ULindex= lists["UL"][0]
#print(ULindex)

#for i in lists["UL"]:
for i in ULrest:
    datetime=value=i.split(' ')[0]
    #print(datetime)
    
    value=i.split(' ')[1:-1]
    UL.append(datetime)
    for ii in value:
         UL.append(float(ii))

print(UL)   
 

Hi,

Pandas supports changing the dtype of a column, called "casting". See documentation for details.

Regards, noisefloor

You most chek DataFrame with dtypes(always check this) to see what types are now.
Should not loop and try to change manually when work in Pandas,as noisefloor mentiom there are functions for this.
Example.

import pandas as pd
import numpy as np

dates = pd.date_range('2023-01-01', periods=5, freq='D')
df = pd.DataFrame({
    'date': dates,
    'column_1': np.random.randn(5),
    'column_2': np.random.randint(0,5, size=(1, 5))[0]
    })

>>> df
        date  column_1  column_2
0 2023-01-01  0.151729         1
1 2023-01-02  0.201814         3
2 2023-01-03  1.167231         3
3 2023-01-04 -1.701339         1
4 2023-01-05 -0.147010         1

# Check types
>>> df.dtypes
date        datetime64[ns]
column_1           float64
column_2             int32
dtype: object

# Convert column 2 to float
>>> df['column_2'] = df['column_2'].astype(float)

# Check types
>>> df.dtypes
date        datetime64[ns]
column_1           float64
column_2           float64
dtype: object

# Write to Excel
df.to_excel('out1.xlsx', index=False)

I saw my dtypes are object. IS there any method to change column 2 and 3 to float.

---

if there're any better methods or suggestions for this code please recommend them. I know this code is not pretty efficient.

Hi,

Quote:IS there any method to change column 2 and 3 to float.

Did you read the answer we gave you? A link to the Pandas documentation AND an example were given. Now it's your turn. If you have problems understanding what to do, please explain and show your code, even if is doesn't work.

Regards, noisefloor

(Feb-11-2023, 01:48 AM)jacklee26 Wrote: [ -> ]I saw my dtypes are object. IS there any method to change column 2 and 3 to float.

I have already show you how to do this🧐
Now are all object which is a string type(but can technically contain any Python object).

df['your_column_name'] = df['your_column_name'].astype(float)

Most also change datettime column to correct type.

# Convert the "datettime" column to datetime64 dtype
df["datettime"] = pd.to_datetime(df["datettime"]

ok Thanks all,
I solve it by adding this

import pandas as pd

#uplink
df1 = pd.DataFrame(UL)
df1 = df1.rename(columns=df1.iloc[0]).drop(df1.index[0])

df1['ingress-traffic'] = df1['ingress-traffic'].astype(float)
df1['egress-traffic'] = df1['egress-traffic'].astype(float)