在Python中以CSV填充空点 [英] Filling Null Spots in CSV in Python

问题描述

我正在解析一个csv文件来创建图表.我能够做到这一点，没有任何问题，除了在单个情况下...每当csv文件中有空插槽时.例如:

I am parsing a csv file to create charts. I am able to do this with no problem, EXCEPT in a single case... Whenever there is a null slot in the csv file. For example:

Col1 Col2 Col3 Col4 Col5
  45    34    23    98    18
  66            25    0
  18岁          52    56    100

Col1 Col2 Col3 Col4 Col5
  45   34     23     98     18
  66            25     0
  18            52     56    100

文件的第2列和第5列中有两个空白条目.我想用0填充这些位置.我对Python还是很陌生.如果csv中有一个空点，我想插入一个0.由于我的csv文件中有时可能有空格，所以出现错误TypeError: unsupported operand type(s) for -: 'int' and 'str'.必须进入csv文件以检查是否存在空点并手动将其填充为零，这可能很烦人，因此我想在脚本中执行此操作.这是我的代码:

There are two blank entries in the file in column 2 and 5. I want to fill these spots with 0. I'm fairly new to Python. In the case where there is a null spot in the csv, I would like to insert a 0. Because I may sometimes have blanks in my csv file, I get the error TypeError: unsupported operand type(s) for -: 'int' and 'str'. It can be tiresome to have to go into the csv file to check whether there is a null spot and manually fill it with zero so I would like a way to do this in the script. Here is my code:

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


file_name = "myfile.csv"
df = pd.read_csv(file_name)
names = df['name'].values

x = np.arange(len(names))*2
w = 0.40

col2 = df.columns[1]
col3 = df.columns[2]
col4 = df.columns[3]
col5 = df.columns[4]

dif = df[col4] - df[col3]

colors = ['Red' if d < -5 else 'Blue' for d in dif]

plt.bar(x-w, df[col2].values, width=w*0.7, label=col2, color = "cyan")
plt.bar(x, df[col3].values, width=w*0.7, label=col3, color = "green")
plt.bar(x+w, df[col4].values, width=w*0.7, label=col4, color = colors)
plt.plot(x, df[col5].values, lw=2, label="Goal", color = "red")

plt.xticks(x, names, rotation='vertical')
plt.ylim([0,100])

plt.show()

注意:如上所述，我正在从csv文件读取数据帧.

Note: As I mentioned above, I'm reading the dataframe from a csv file.

我已将此行添加到我的代码中:

I have added this line to my code:

df.replace(r'^\s*$', 0, regex=True)
#For testing purposes, I also added this:
print(df.replace(r'^\s*$', 0, regex=True))

我可以看到空插槽现在填充有零，但是我仍然收到dif = df[col4] - df[col3]的错误TypeError: unsupported operand type(s) for -: 'str' and 'int'.可能会将那些插入0的字符串读取为字符串吗? 我也曾尝试将df[col3]和df[col4]包装在int()中，但是没有运气.它给出错误TypeError: cannot convert the series to <class 'int'>.然后，我尝试df[col4].astype(int) - df[col3].astype(int)并收到错误ValueError: invalid literal for int() with base 10.

I can see that the empty slots are now filled with zeros but I am still getting the error TypeError: unsupported operand type(s) for -: 'str' and 'int' for dif = df[col4] - df[col3]. Is it possibly reading those inserted 0 as strings? I have also tried to wrap df[col3] and df[col4] in int() but no luck there. It gives the error TypeError: cannot convert the series to <class 'int'>. I then tried df[col4].astype(int) - df[col3].astype(int) and got the error ValueError: invalid literal for int() with base 10.

我刚刚添加了行print(df.dtypes).由于某种原因，第四列(在这种情况下，它包含替换为0的)被视为对象，而不是其余列那样的int64.

EDIT 2: I just added the line print(df.dtypes). For some reason the fourth column (which was containing the replaced 0 in this case) is being seen as an object instead of int64 like the rest of the columns.

推荐答案

   import pandas as pd
   file_name = "myfile.csv"
   df = pd.read_csv(file_name)
   # a Pandas method that fills any NaN value with 0, you can change 0 to any value you 
   # want, you can use mean or median, etc
   df.fillna(0, inplace=True)

这篇关于在Python中以CSV填充空点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！