convert year, month columns to datetime in python, generate csv

I am new in python and pandas, so facing too many issues right now.Not able to create datetime using pandas and want to create csv file according to given data in dataframe. I want to convert given columns date to single date time, output should be in csv..

Here is my code below:

    # -*- coding: utf-8 -*-
import pandas as pd
df = pd.DataFrame({
        'S.No.':[1,2,3,4,5],
        'YEAR':['1967-68','1968-69','1969-70','1970-71','1971-72'],
        'JUNE':['77.19','415.48','236.71','108.38','6.19'],
        'JULY':['76.19','435.48','26.71','138.38','9.19'],
        'AUGUST':['75.19','415.48','226.71','78.38','3.19']
        })

Now from this code asserting missing month with 0 value and sample output of above code should generate cvs format like below,

datetime;year;month;day;data 
1967-01-01 00:00:00;1967;1;1;0.0
1967-02-01 00:00:00;1967;2;1;0.0 
1967-03-01 00:00:00;1967;3;1;0.0
(contd. )
........
........ 
1967-06-01 00:00:00;1967;6;1;77.19
1967-07-01 00:00:00;1967;7;1;76.19
(contd. )
......... 
1968-06-01 00:00:00;1968;6;1;415.48
1968-07-01 00:00:00;1968;7;1;435.48
......... 
1972-12-01 00:00:00;1972;12;1;0.0

2 answers

  • answered 2018-01-14 09:59 MaxU

    Try this:

    df['Date'] = pd.to_datetime(df[['year','month','day']])
    

    from pd.to_datetime() docs:

    Assembling a datetime from multiple columns of a DataFrame. The keys can be common abbreviations like [‘year’, ‘month’, ‘day’, ‘minute’, ‘second’, ‘ms’, ‘us’, ‘ns’]) or plurals of the same

  • answered 2018-01-14 09:59 Madan Adhikari

    Though I am not a Python developer, I tend to help you on logic. Guess this will be helpful for you.

    $yrs = ['1967-68','1968-69','1969-70','1970-71','1971-72'];
    $new_yrs = [];
    foreach ($yrs as $yr) {
        // get first two character of string
        $prefix = substr($yr, 0,2);
        // remove first two character of string
        $postfix = substr($yr, 2);
        // break the string to 2 pcs
        $pcs = explode('-', $postfix);
        // get unique dates
        $new_yrs[$prefix.$pcs[0]] = $prefix.$pcs[0];
        $new_yrs[$prefix.$pcs[1]] = $prefix.$pcs[1];
    }
    

    Output: [1967 => "1967", 1968 => "1968", 1969 => "1969", 1970 => "1970", 1971 => "1971", 1972 => "1972"]