0

I need to manipulate a csv file in a way to go into the csv file look for blank fields between c0-c5 in my example csv file. with the csv file where ever there are blanks I would like to replace the blank with any verbage i want, like "not found"

the only thing for code I have so far is dropping a column I do not need, but the manipulation I need I really can not find anything.. maybe it is not possible?

also, i am wondering how to change a column name..thanks..

#!/bin/env python


import pandas
data = pandas.read_csv('report.csv')
data = data.drop(['date',axis=1)
data.to_csv('final_report.csv')

enter image description here

Randal
  • 363
  • 5
  • 21
  • 2
    One way to accomplish is using [fillna](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html) `data["data"] = data["data"].fillna("not found")` – n1colas.m Jul 11 '20 at 01:42
  • 2
    Does this answer your question? [How can I replace all the NaN values with Zeros in a column of a pandas dataframe](https://stackoverflow.com/questions/13295735/how-can-i-replace-all-the-nan-values-with-zeros-in-a-column-of-a-pandas-datafram) – David Erickson Jul 11 '20 at 01:58
  • @n1colas.m -thank you!!! – Randal Jul 11 '20 at 01:59
  • 1
    @Randal - to change a column name, use data.rename(columns = {"old_name":"new_name"}) – Roy2012 Jul 12 '20 at 06:31

2 Answers2

1

Alternatively and taking your "comment question" into account (if you do not necessarily want to use pandas as in n1colas.m's answer) use string replacements and simply loop over your file with:

with open("modified_file.csv","w") as of:
  with open("report.csv", "r") as inf:
    for line in inf:
     if "#" not in line: # in the case your csv file has a comment marker somewhere and it is called #, the line is skipped, which means you get a clean comma separated value file as the outfile- if you do want to keep such lines simply remove the if condition
       mystring=line.replace(", ,","not_found").replace("data","input") # in case it is not only one blank space you can also use the regex for n times blank space here
       print(mystring, file=of, end=""); # prints the replaced line to outfile and writes no newline

I know this is not the most efficient way to do it, but probably the one where you easily understand what you are doing and are able to modify this to your heart's desire. For any reasonably sized csv files it sould still work nearly instantaneously. Also for testing purposes always use a separate file (of) for such replacements instead of writing to your infile as your question seems to state. Check that it did what you wanted. ONLY THEN overwrite your infile. This may seem unnecessary at first, but mistakes happen...

Arbor Chaos
  • 149
  • 6
  • Thank for the response. I landed up just doing the following for changing the column name. It seem to work.. data = data.rename({'data': 'info'}, axis='columns') – Randal Jul 12 '20 at 08:04
0

You have to perform this line

data['data'] = data['data'].fillna("not found")

Here the documentation https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.fillna.html

Here an example

import pandas
data = pandas.read_csv('final_report.csv')
data.info()
data['data'] = data['data'].fillna("Something")
print(data)

I would suggest to change the data variable to something different, because your column has the same name and can be confusing.

heinemelo
  • 28
  • 6