Converting .csv to xlsx - Numbers stored as text

Python Help
1

Hi.,

import pandas as pd
import openpyxl

# Read the CSV file
df = pd.read_csv("D:\\xxxt\\xx.csv")  # Replace with the actual path of your CSV file
# Convert DataFrame to Excel
df.to_excel("D:\xx\xx.xlsx",index=False)  # This will create a new Excel file

my output is like this, the number are stored as text. How to rectify this by storing as numbers

image
image412×582 45.8 KB

Thanks

2

It looks like your csv file contains both numbers and text within the same column (row 12, “No.”). Pandas therefore stores the column as text, and exports it as text when creating the xlsx-file.

To fix it, you will need to remove the text entries from the column, and cast the column to your desired data type:

df = df[df.map(str.isnumeric)].dropna().astype(int)

Then, the values should be stored as numbers in the exported xlsx.

3

Hi.,

Thanks for the update, but it throws the attribute error,
AttributeError: 'DataFrame' object has no attribute 'map'.

but while searching online the applymap function works well. it solved the problem too,

df = df.applymap(lambda x: x if str(x).isnumeric() else None).dropna().astype(int)
but this function is deprecated.

4

DataFrame.applymap was renamed to DataFrame.map in pandas 2.1.0.

1 Like