Search code examples
pythondatetimetime-seriesdatestamp

Python: Concatenate Hour into dd/mm/yyyy column in data file

I have the following file:

01/03/2019,23,24.0
01/03/2019,22,24.5
01/03/2019,21,25.9
01/03/2019,20,26.4
01/03/2019,19,26.6
01/03/2019,18,25.9

Where column 1 is date (dd/mm/yyyy), column 2 is hour and column 3 is temperature.

How can I merge columns 1 and 2 in order to make a new column dd/mm/yyyy hh:00, by using pyhton?

I am using a .csv file and pandas module.

Solution

  • Using csv OR pandas library (tested using python 3.7)

    import csv
import pandas as pd

use_pandas = True

if use_pandas:
    df = pd.read_csv('55089401.csv', header=None)
    df[3] = df[0].map(str) + ' ' + df[1].map(str) + ':00'
    del df[0]
    del df[1]
    new_order = [1, 0]
    df = df[df.columns[new_order]]
    print(df)
else:
    out_data = []
    with open('55089401.csv', 'r') as f_in:
        reader = csv.reader(f_in)
        for row in reader:
            out_data.append(['{} {}:00'.format(row[0], row[1]), row[2]])
    with open('55089401_out.csv', 'w', newline='') as f_out:
        writer = csv.writer(f_out)
        writer.writerows(out_data)

    Pandas output

    0  01/03/2019 23:00  24.0
1  01/03/2019 22:00  24.5
2  01/03/2019 21:00  25.9
3  01/03/2019 20:00  26.4
4  01/03/2019 19:00  26.6
5  01/03/2019 18:00  25.9

    Input file

    01/03/2019,23,24.0
01/03/2019,22,24.5
01/03/2019,21,25.9
01/03/2019,20,26.4
01/03/2019,19,26.6
01/03/2019,18,25.9

    Output file (csv)

    01/03/2019 23:00,24.0
01/03/2019 22:00,24.5
01/03/2019 21:00,25.9
01/03/2019 20:00,26.4
01/03/2019 19:00,26.6
01/03/2019 18:00,25.9