how to parsing csv data into tables using Python-CodePudding

I have csv file and wanted to make table with its value out of it. here's the data look like :

I want the result to be like this:

Here's the automatic parsing code that didn't work for me :

import pandas as pd
data = pd.read_csv('time series practice.csv' index_col= 'Date', parse_dates=<strong>True</strong>)

Can anyone help?

CodePudding user response：

Please try this, Have used datetime formatting using date parser

Input

Code

import pandas as pd
from datetime import datetime
dateparse = lambda x: datetime.strptime(x, '%d/%m/%Y %H:%M')
data = pd.read_csv('time_series_practice.csv',index_col= 'Date',parse_dates=['Date'], date_parser=dateparse)
display(data)

Output

                   West East
Date        
2012-03-10 00:00:00 4   9
2012-03-10 01:00:00 4   6
2012-03-10 02:00:00 1   1
2012-03-10 03:00:00 2   3
2012-03-10 04:00:00 6   1
2012-03-10 05:00:00 21  10
2012-03-10 06:00:00 105 50
2012-03-10 07:00:00 257 95
2012-03-10 08:00:00 291 146
2012-03-10 09:00:00 172 104

CodePudding user response：

littletable can read and write CSV's pretty easily, with a much smaller footprint than pandas (if you are space constrained, or just not as familiar with pandas):

import littletable as lt
from datetime import datetime

transforms={"date": lambda s: datetime.strptime(s, "%m/%d/%Y %H:%M"),
            "Fremont Bridge West Sidewalk": float,
            "Fremont Bridge East Sidewalk": float}

# data = lt.Table().csv_import("time_series_practice.csv",
#                              transforms=transforms)
data = lt.Table().csv_import("""\
date,Fremont Bridge West Sidewalk,Fremont Bridge East Sidewalk
10/3/2012 0:00,4,9
10/3/2012 1:00,4,6
10/3/2012 2:00,1,1
10/3/2012 3:00,2,3
10/3/2012 4:00,6,1
10/3/2012 5:00,21,10
10/3/2012 6:00,105,50
10/3/2012 7:00,257,95
10/3/2012 8:00,291,146
10/3/2012 9:00,172,104
""", transforms=transforms)

data.present()

prints:

  Date                  Fremont Bridge West Sidewalk   Fremont Bridge East Sidewalk 
 ───────────────────────────────────────────────────────────────────────────────────
  2012-10-03 00:00:00                            4.0                            9.0
  2012-10-03 01:00:00                            4.0                            6.0
  2012-10-03 02:00:00                            1.0                            1.0
  2012-10-03 03:00:00                            2.0                            3.0
  2012-10-03 04:00:00                            6.0                            1.0
  2012-10-03 05:00:00                           21.0                           10.0
  2012-10-03 06:00:00                          105.0                           50.0
  2012-10-03 07:00:00                          257.0                           95.0
  2012-10-03 08:00:00                          291.0                          146.0
  2012-10-03 09:00:00                          172.0                          104.0

[Disclaimer: I am the author of littletable.]