Python Forum
Importing a .csv file to dataframe
Thread Rating:
  • 1 Vote(s) - 4 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Importing a .csv file to dataframe
#1
Hello all,

I'm wondering if you may be able to help...
I'm trying to import this .csv file, so I can view the data in columns under each heading, however when imported the format is not coming into a nice table.
Instead of the columns being populated with the data underneath they are being populated with NaN values (not a number). The data seems to be bunched in the second column under 'Reading values'

shape is: 6949 rows, 12 columns
type is: pandas.core.frame.DataFrame

I am using
rawdata=pd.read_csv("Desktop/rawdata.csv")
print(rawdata)




Has anyone seen this before, any suggestions would be appreciated.
Thanks

sorry you cant see my image at this time as ive only just registered
Reply
#2
Please use python tags for multi-line code. icode tags are for inline code.

Also, please post a few line of the csv file and the resulting data frame, using output tags.
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply
#3
Hello Bunny Rabbit



here are the first 6 lines of the .csv
Output:
Reading values Reading values timestamp Insert time Reporting type Errorcode OBIS code Unit Factor CT factor Quality Quality Status Word Reading reason Reading values,"Reading values timestamp","Insert time","Reporting type","Errorcode","OBIS code","Unit","Factor","CT factor","Quality","Quality Status Word","Reading reason" 29041.601,"21.11.2018 14:15:00","21.11.2018 14:28:12","absolute","0","8-0:1.0.0*255","m³","1.000","1.000","Real value","No errors","Periodic" 29041.601,"21.11.2018 14:30:00","21.11.2018 14:45:06","absolute","0","8-0:1.0.0*255","m³","1.000","1.000","Real value","No errors","Periodic" 29041.601,"21.11.2018 14:45:00","21.11.2018 14:59:26","absolute","0","8-0:1.0.0*255","m³","1.000","1.000","Real value","No errors","Periodic" 29041.601,"21.11.2018 15:00:00","21.11.2018 15:15:57","absolute","0","8-0:1.0.0*255","m³","1.000","1.000","Real value","No errors","Periodic"
here is the output
Output:
Reading values \ 0 Reading values,"Reading values timestamp","Ins... 1 29041.601,"21.11.2018 14:15:00","21.11.2018 14... 2 29041.601,"21.11.2018 14:30:00","21.11.2018 14... 3 29041.601,"21.11.2018 14:45:00","21.11.2018 14... 4 29041.601,"21.11.2018 15:00:00","21.11.2018 15... 5 29041.601,"21.11.2018 15:30:00","21.11.2018 15... 6 29041.601,"21.11.2018 16:15:00","21.11.2018 16... 7 29041.601,"21.11.2018 16:30:00","21.11.2018 16... 8 29041.601,"21.11.2018 17:15:00","21.11.2018 17... 9 29041.601,"21.11.2018 18:15:00","21.11.2018 18... 10 29041.601,"21.11.2018 19:30:00","21.11.2018 19... 11 29041.601,"21.11.2018 20:15:00","21.11.2018 20... 12 29041.601,"21.11.2018 21:00:00","21.11.2018 21... 13 29041.601,"21.11.2018 22:15:00","21.11.2018 22... 14 29041.601,"21.11.2018 22:30:00","21.11.2018 22... 15 29041.601,"21.11.2018 23:00:00","21.11.2018 23... 16 29041.601,"21.11.2018 23:15:00","21.11.2018 23... 17 29041.601,"21.11.2018 23:45:00","22.11.2018 00... 18 29041.601,"22.11.2018 00:00:00","22.11.2018 00... 19 29041.601,"22.11.2018 00:30:00","22.11.2018 00... 20 29041.601,"22.11.2018 01:30:00","22.11.2018 01...
hope this helps
Reply
#4
As I said, please use output tags, not icode tags (the weird icon between the red X and the brackets([]) in the editor). I put them in for you this time.

It looks to be reading in as a data frame as you want. Most of it is coming in as strings, probably because of the quotes in the csv file. You can specify data types ahead of time with the dtypes parameter of read_csv, or afterwords with the astype method of the column. Here is a Stack Overflow post with examples on how to use them.

Edit: No, I see what you are saying about the columns all being one. I think the first line of the csv file is confusing it. It is reading it as the column names, but there are no commas, so it only sees one column. Try deleting the first row of the csv file and see if that improves things.
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply
#5
Thanks for the pointer to the Stack overflow post, I got searching around in this area and found some more useful posts but not enough to solve my problem

I know that my .csv file needs more processing before I read it in as all the data is in a single column and the headings are being ignored.

the following code forces the headings to be seen so ive manually done this for the first 3 lines,
my_string1 = 'Reading values,Reading values timestamp,Insert time,Reporting type,Errorcode,OBIS code,Unit,Factor,CT factor,Quality,Quality Status Word,Reading reason'
my_string2 = '29041.601,21.11.2018 14:30:00,21.11.2018 14:45:06,absolute,0,8-0:1.0.0*255,m³,1.000,1.000,Real value,No errors,Periodic'
my_string3 = '29041.601,"21.11.2018 14:30:00","21.11.2018 14:45:06","absolute","0","8-0:1.0.0*255","m³","1.000","1.000","Real value","No errors","Periodic"'
my_list1 = my_string1.split(",")
my_list2 = my_string2.split(",")
my_list3 = my_string3.split(",")

path = 'desktop/rawdata.csv'
rows = [my_list1,my_list2,my_list3]

with open(path, "w") as csv_file:
    writer = csv.writer(csv_file, delimiter=',')
    for row in rows:
        writer.writerow(row)
This nicely spreads my data across 12 colums with data underneath them Cool
however my file has just under 7000 lines so this isnt practical!


is there a way that I can get this process (or a similar one that does more or less the same thing) to iterate through my whole file?
Reply
#6
I just did a check. The file reads fine for me if I delete the first line. Is that not working for you?
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Converting a json file to a dataframe with rows and columns eyavuz21 13 4,176 Jan-29-2023, 03:59 PM
Last Post: eyavuz21
  New2Python: Help with Importing/Mapping Image Src to Image Code in File CluelessITguy 0 698 Nov-17-2022, 04:46 PM
Last Post: CluelessITguy
  Problem with importing Python file in Visual Studio Code DXav 7 4,937 Jun-15-2022, 12:54 PM
Last Post: snippsat
  importing functions from a separate python file in a separate directory Scordomaniac 3 1,331 May-17-2022, 07:49 AM
Last Post: Pedroski55
  Extract parts of a log-file and put it in a dataframe hasiro 4 6,132 Apr-08-2022, 01:18 PM
Last Post: hasiro
  Importing a function from another file runs the old lines also dedesssse 6 2,480 Jul-06-2021, 07:04 PM
Last Post: deanhystad
  Importing text file into excel spreadsheet with formatting david_dsmn 1 3,550 Apr-05-2021, 10:21 PM
Last Post: david_dsmn
  Json File more pages #pandas #dataframe nio74maz 0 1,782 Dec-30-2020, 05:32 AM
Last Post: nio74maz
  importing a CSV file into Python russoj5 1 2,918 Aug-02-2020, 12:03 AM
Last Post: scidam
  Importing data from a text file into an SQLite database with Python macieju1974 7 4,014 Jun-29-2020, 08:51 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020