How do I combine 2 pandas dataframes based on a common column, research site "Name"?

user_13156712 0
Viewed 80

I'm new to Python and coding in general. I am attempting to automate the processing of some groundwater model output data in python. One pandas dataframe has measured stream flow with multiple columns of various types (left), the other has modeled stream flow (right). I've attempted to use pd.merge on column "Name" in order to link the correct modeled output value to the corresponding measured site value. When I use the following script I get the corresponding error:

left = measured_df
right = modeled_df

combined_df = pd.merge(left, right, on= 'Name')

ValueError: The column label 'Name' is not unique. For a multi-index, the label must be a tuple with elements corresponding to each level.

The modeled data for each stream starts out as a numpy array (not sure about the dtype)

array(['silver_drn', '24.681524615195002'], dtype='<U18')

I then use np.concatenate to combine the 6 stream outputs into one array:

modeled = np.concatenate([[blitz_drn],[silvies_ss_drn],[silvies_drn],[bridge_drn],[krumbo_drn], [silver_drn]])

Then pd.DataFrame to create a pandas data frame with a column header:

modeled_df = pd.DataFrame(data=modeled, columns= [['Name','Modeled discharge (CFS)']])

See image links below to see how each dataframe looks (not sure the best way to share just yet).

left = table of measured stream flow

right = table of modeled stream flow

Perhaps I'm misunderstanding how pd.merge works,or maybe the datatypes are different even if they appear to be text, but figured if each column was a string, it would append the modeled output to the corresponding row where the "Name" matches within each dataframe. Any help would be greatly appreciated.

pandas python
1 Answers

When you do this:

modeled_df = pd.DataFrame(data=modeled, 
                          columns= [['Name','Modeled discharge (CFS)']])

you create a MultiIndex on the columns. And that MultiIndex is trying to be merged with a DataFrame with a normal index which doesn't work as you might expect.

You should instead do:

modeled_df = pd.DataFrame(data=modeled, 
                          columns=['Name','Modeled discharge (CFS)'])
#                                 ^                                ^

Then the merge should work as expected.

user_423460
Related