I have two dataframes containing meteorological data for different dates at different locations within the same dataframe, here's a simpler version of my data which reproduces the issue:
df = pd.DataFrame(np.random.randint(0,30,size=(10, 4)), columns=(['Temp', 'Precip', 'Wind', 'Pressure']))
df1 = pd.DataFrame(np.random.randint(0,30,size=(10, 4)), columns=(['Temp', 'Precip', 'Wind', 'Pressure']))
df['Location'] =[2,2,3,3,4,4,5,5,6,6]
df1['Location'] =[2,2,3,3,4,4,5,5,6,6]
These are indexed by the 18th and 19th of May 2020 for df, and the 19th and 20th of May for df1 like so:
df.index = ["2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00"]
df1.index = ["2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00"]
df.index = pd.to_datetime(df.index)
df1.index = pd.to_datetime(df1.index)
The way the dataframes are structured means each Location point has 2 days of data in each dataframe. The 18th and 19th in df, and the 19th and 20th in df1. The look like this:
I want to combine these two dataframes into df3 where I have values for the 18th, 19th and 20th for each location point, where 18th comes from df, and 19th and 20th comes from df1. i.e. df1 overwrites df for each location on the same date, then appends the data for all following dates, to produce something that looks like this:
In reality I have hundreds of locations over many days, so this will need to work based on the index (I think).
I have tried the pd.combine_first method like so:
df.combine_first(df1)
but (because of the repeated dates in the index) this produces a dataframe with many more cells than I want - there should be 15 in total and there are many more.
I think this is due to the index because when I tried an example with simpler dates for just one location it works fine - but I can't figure out how to do it on my data which has multiple locations within the same dataframe. I'd really appreciate some help!
EDIT: the answer marked below does solve this issue, but now when I want to add new data which doesn't match the index length like so:
import pandas as pd
import numpy as np
df = pd.DataFrame(np.random.randint(0,30,size=(10, 4)), columns=(['Temp', 'Precip', 'Wind', 'Pressure']))
df1 = pd.DataFrame(np.random.randint(0,30,size=(11, 4)), columns=(['Temp', 'Precip', 'Wind', 'Pressure']))
df['Location'] =[2,2,3,3,4,4,5,5,6,6]
df1['Location'] =[1,2,2,3,3,4,4,5,5,6,6]
df.index = ["2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00","2020-05-18 12:00:00","2020-05-19 12:00:00"]
df1.index = ["2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00", "2020-05-20 12:00:00", "2020-05-19 12:00:00"]
df.index = pd.to_datetime(df.index)
df1.index = pd.to_datetime(df1.index)
df1
So I now have another location with the value 1, I want to add this location to df along with updating the values with those of df1. When I use the following code:
df = df.set_index(df.groupby(level=0).cumcount(), append=True)
df1 = df1.set_index(df1.groupby(level=0).cumcount(), append=True)
df = df.combine_first(df1).sort_index(level=[1,0]).reset_index(level=1, drop=True)
print (df)
It updates df with the values from df2, but it removes the new location. Is there any way around this?