dimanche 28 juin 2015

Operations on Columns multiple files Pandas

I am trying to perform a some arithmetic operations in Python Pandas and merge the result in one of the file.

Path_1: File_1.csv, File_2.csv, ....

This path has several file which are supposed to be increasing in time intervals. with the following columns

    File_1.csv    |  File_2.csv
    Nos,12:00:00  |  Nos,12:30:00

    123,1451         485,5464
    656,4544         456,4865
    853,5484         658,4584

Path_2: Master_1.csv

Nos,00:00:00
123,2000
485,1500
656,1000
853,2500
456,4500
658,5000

I am trying to read the n number of .csv files from Path_1 and compare the col[1] header timeseries with col[last] timeseries of Master_1.csv.

If Master_1.csv does not have that time it should create a new column with timeseries from path_1 .csv files and update the values with respect col['Nos'] while subtracting them from col[1] of Master_1.csv.

If the col with time from path_1 file is present then look for col['Nos'] and then replace the NAN with the subtracted values respect to that col['Nos'].

i.e.

Expected Output in Master_1.csv

Nos,00:00:00,12:00:00,12:30:00,
    123,2000,549,NAN,
    485,1500,NAN,3964,
    656,1000,3544,NAN
    853,2500,2984,NAN
    456,4500,NAN,365
    658,5000,NAN,-416

I can understand the arithmetic calculations but I am not able to loop in with respect to Nos and timeseries I have tried to put some code together and trying to work around looping. Need help in that context. Thanks

import pandas as pd 
import numpy as np

path_1 = '/'
path_2 = '/'

df_1 = pd.read_csv(os.path_1('/.*csv'), Index=None, columns=['Nos', 'timeseries'] #times series is different in every file eg: 12:00, 12:30, 17:30 etc
df_2 = pd.read_csv('master_1.csv', Index=None, columns=['Nos', '00:00:00']) #00:00:00 time series

for Nos in df_1 and df_2:
    df_1['Nos'] = df_2['Nos']
    new_tseries = df_2['00:00:00'] - df_1['timeseries']

merged.concat('master_1.csv', Index=None, columns=['Nos', '00:00:00', 'new_tseries'], axis=0) # new_timeseries is the dynamic time series that every .csv file will have from path_1

Aucun commentaire:

Enregistrer un commentaire