I am trying the merge the datetime series with a repository data while grouping by name and summing the values.
File1.csv
Timeseries,Name,count
07/03/2015 06:00:00,Paris,100
07/03/2015 06:00:00,Paris,600
07/03/2015 06:00:00,Paris,700
07/03/2015 06:00:00,London,200
07/03/2015 06:00:00,London,100
07/03/2015 06:00:00,London,500
07/03/2015 06:00:00,Dublin,300
07/03/2015 06:00:00,Dublin,400
07/03/2015 06:00:00,Dublin,400
Output
Master_file.csv (append mode)
Name,Timeseries(n-1)Timeseries(n)#put the datetime series as header and put
Paris,300,1400 #Sum of all the values with same Name
London,200,800
Dublin,400,1100
Program
import pandas as pd
import numpy as np
df = pd.read_csv('/home/lat_lon1.csv')
df1 = pd.read_csv('/home/lat_lon_master.csv')
gp = df.groupby('Name')['date timeseries'].sum().reset_index()
df1.merge(gp, on='Name')
I am having trouble in changing the date time
column to header and putting the correct values under. Those Names
not found can be given NAN and replaced in next iterations.