Conditional filtering in excel using python-CodePudding

I am currently trying to color code data in specific columns to excel within my program.

When I run this code:

import pandas
import pandas as pd
import numpy as np
import re
from openpyxl import load_workbook

#opening txt file location, reading the entire file
with open("Data at 20-5C on(18-11-21)(decoupled).txt", "r", encoding="utf8") as f:
    data = f.readlines()

#filters only ad averaged values
data = ([x.strip().split(',') for x in data if "RAW" not in x])
max_len = max([len(i) for i in data])

for row in data:
    if len(row) < max_len:
        row  = [''] * (max_len - len(row))

#adding columns
df = pd.DataFrame(data, dtype=int, columns=['', 'TH ', 'Vacm', 'Vout', 'Bat mon', 'TH-', 'Vbat2', 'Vamb', 'VambCorrY'])
#inserting index column
df.insert(1,'Index', np.nan)
#changing index to default index
df['Index'] = df.index

stage_threshold = 2

df_styled = df.style.applymap(lambda x: 'background-color: red' if x < stage_threshold else 'background-color: green', subset=['Vout'])

df_styled.to_excel('Data at 20-5C on(18-11-21)(decoupled).xlsx', engine='openpyxl', index=False)

I am greeted with '<' not supported between instances of 'str' and 'int'

Here is an example of the data within the .txt:

Run1    Tbb= 20 C    Volt=2.938   Tamb= 21.44 C   

 AD Averaged 0129(mV), 0001, 0001, 0001, 0001, 0001, 0001, 0000,
 RAW Values  0129(xx), FFFF, FFFF, FFFF, FFFF, FFFF, FFFF,
 AD Averaged 0169(mV), 2297, 1071, 0001, 2901, 1015, 2500, 0000,
 RAW Values  0169(xx), 0EB2, 06DA, FFFF, 0C60, 067E, 0FFF,
 AD Averaged 01a9(mV), 2297, 1071, 0001, 2900, 1015, 2500, 0000,
 RAW Values  01a9(xx), 0EB2, 06DA, FFFF, 0C5F, 067E, 0FFF,
 AD Averaged 01e9(mV), 2297, 1070, 0001, 2891, 1015, 2500, 0000,

I am aware that it is not possible to check a string against an int or vice versa. I am wondering how I go about converting my data set to int, or if this is even the approach I should take?

Thanks!

CodePudding user response：

Using

df['Vout'] = pd.to_numeric(df['Vout'], errors='coerce').astype('Int64')

I solved the issue.