ValueError: count not convert string to float

Question

I'm trying to use a Python script to plot with a rolling window using pandas and seaborn. This code worked for the longest time but now it's giving me an error that I don't know how to fix.

Here is the code I'm using:

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns

heterozygosity_df = pd.read_csv("file.tsv", sep="\t", header=None).set_axis(['chr', 'pos', 'het'], axis=1, copy=False)

heterozygosity_df['het'] = heterozygosity_df['het'].transform(lambda x: -1*(x-1))

windows = [25, 50, 100, 200]
steps = [5]

for window in windows:
    for step in steps:
        heterozygosity_ratio = {}
        for chr_heterozygosity_df in heterozygosity_df.groupby('chr'):
            heterozygosity_ratio[chr_heterozygosity_df[0]] = chr_heterozygosity_df[1].set_index('pos').rolling(window=window, step=step, center=True).mean()
...

There is more after this but that's just to configure the resulting plot with matplotlib. The error I get when I use the above code is this:

Traceback (most recent call last):
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/window/rolling.py", line 368, in _prep_values
    values = ensure_float64(values)
             ^^^^^^^^^^^^^^^^^^^^^^
  File "pandas/_libs/algos_common_helper.pxi", line 42, in pandas._libs.algos.ensure_float64
ValueError: could not convert string to float: 'chrI'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/window/rolling.py", line 485, in _apply_blockwise
    arr = self._prep_values(arr)
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/window/rolling.py", line 370, in _prep_values
    raise TypeError(f"cannot handle this type -> {values.dtype}") from err
TypeError: cannot handle this type -> object

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "plot_heterozygosity_window.py", line 29, in <module>
    heterozygosity_ratio[chr_heterozygosity_df[0]] = chr_heterozygosity_df[1].set_index('pos').rolling(window=window, step=step, center=True).mean()
                                                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/window/rolling.py", line 2111, in mean
    return super().mean(
           ^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/window/rolling.py", line 1507, in mean
    return self._apply(window_func, name="mean", numeric_only=numeric_only)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/window/rolling.py", line 615, in _apply
    return self._apply_blockwise(homogeneous_func, name, numeric_only)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/window/rolling.py", line 487, in _apply_blockwise
    raise DataError(
pandas.errors.DataError: Cannot aggregate non-numeric type: object

I have tried converting the 'chr' column directly from a string to a float: heterozygosity_df['chr'] = heterozygosity_df['chr'].astype(float)

I have tried converting the entire dataframe to a float: heterozygosity_df = heterozygosity_df.astype(float, errors = 'raise')

But everything keeps giving me the ValueError: could not convert string to float: 'chrI'

Does someone know an easy way to fix this? Like I said, I have been using this code for months, and it's always worked - have they deprecated something in the pandas package to make it so you can't read in columns with strings and integers?

EDIT:

For reference the dataframe is structured like this:

chrI  123  1
chrI  234  0
chrI  237  1
chrII 456  1 
chrII 567  1 
chrII 678  1 
...
chrXVI 123 1

Where the first column designates the chromosome, the second column is the position, and the third column is the heterozygosity indicator.

Answer 1

The following would be useful,

print (heterozygosity_df.columns)

This looks like static typing issue within pandas. What I suspect is heterozygosity_df.['chrI'] is a column in the dataframe. What I think has happened is there's a mix of strings and floats within this column. pandas has set this as a "string" but you are wanting to perform numerical operations. Thus the solution is simply

print(heterozygosity_df.dtypes) # this should state 'chrI' is a "category" or "string"
heterozygosity_df['chrI'] = heterozygosity_df['chrI'].astype(float)
print(heterozygosity_df.dtypes)

If you have multiple changes the syntax is

heterozygosity_df = heterozygosity_df.astype({'chrI':'float', 'egColumn':'category'})

I suspect there will be other errors, e.g. the header is the first row of the data column. This is because again for pandas to automatically assign a column to a "string" means there must be a string value within the column.

---

From the comments. I see whats happening. The easy solution is ...

replacement = {
    "chrI": 1,
    "chrII": 2,
    "chrIII": 3,
    "chrIV": 4,
    ....
}

heterozygosity_df['chr'] = heterozygosity_df['chr'].str.replace(replacement, regex=True)

---

From the comments ... good it works ... this is what I would have personally done ..

replacement = {
    "chrI": 1,
    "chrII": 2,
    "chrIII": 3,
    "chrIV": 4, # continue for all chromosomes
}

heterozygosity_df = pd.read_csv("file.tsv", sep="\t", header=None).set_axis(['chr', 'pos', 'het'], axis=1, copy=False)
heterozygosity_df['chr'] = heterozygosity_df['chr'].str.replace(replacement, regex=True).astype('int')

Normally str should be in place because when 'chr' is imported its an object. However, if it works thats the only thing that counts. 🎉

Answer 2

Ah! I finally got it to work! I used your command but I had to change it up a little bit to work with my data. The final command I used is this:

replacement = {
    "chrI": 1,
    "chrII": 2,
    "chrIII": 3,
    "chrIV": 4,
    "chrV": 5,
    "chrVI": 6,
    "chrVII": 7,
    "chrVIII": 8,
    "chrIX": 9,
    "chrX": 10,
    "chrXI": 11,
    "chrXII": 12,
    "chrXIII": 13,
    "chrXIV": 14,
    "chrXV": 15,
    "chrXVI": 16,
    "chrmt": 17
}

heterozygosity_df['chr'] = heterozygosity_df['chr'].replace(replacement, regex=False)

And with that I was able to generate plots showing all of the chromosomes! (I have to figure out how to change the axis labels from 1,2,3,4...10 back to the chromosome but that's a future problem). Thank you so much for all your help!!