Menu
One of the most commonly reported error in pandas is ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all() and it may sometimes be quite tricky to deal with, especially if you are new to pandas library (or even Python).
The concat() function can be used to concatenate two Dataframes by adding the rows of one to the other. The merge() function is equivalent to the SQL JOIN clause. 'left', 'right' and 'inner' joins are all possible.
When gluing together multiple DataFrames, you have a choice of how to handle the other axes (other than the one being concatenated). This can be done in the following two ways: Take the union of them all, join='outer' . This is the default option as it results in zero information loss.
We can use either pandas. merge() or DataFrame. merge() to merge multiple Dataframes. Merging multiple Dataframes is similar to SQL join and supports different types of join inner , left , right , outer , cross .
In one of your dataframes the year is a string and the other it is an int64
you can convert it first and then join (e.g. df['year']=df['year'].astype(int) or as RafaelC suggested df.year.astype(int))
Edit: Also note the comment by Anderson Zhu: Just in case you have None or missing values in one of your dataframes, you need to use Int64 instead of int. See the reference here.
I found that my dfs both had the same type column (str) but switching from join to merge solved the issue.
@Arnon Rotem-Gal-Oz answer is right for the most part. But I would like to point out the difference between df['year']=df['year'].astype(int) and df.year.astype(int). df.year.astype(int) returns a view of the dataframe and doesn't not explicitly change the type, atleast in pandas 0.24.2. df['year']=df['year'].astype(int) explicitly change the type because it's an assignment. I would argue that this is the safest way to permanently change the dtype of a column.
Example:
df = pd.DataFrame({'Weed': ['green crack', 'northern lights', 'girl scout
cookies'], 'Qty':[10,15,3]})
df.dtypes
Weed object, Qty int64
df['Qty'].astype(str)
df.dtypes
Weed object, Qty int64
Even setting the inplace arg to True doesn't help at times. I don't know why this happens though. In most cases inplace=True equals an explicit assignment.
df['Qty'].astype(str, inplace = True)
df.dtypes
Weed object, Qty int64
Now the assignment,
df['Qty'] = df['Qty'].astype(str)
df.dtypes
Weed object, Qty object
It happens when common column in both table are of different data type.
Example: In table1, you have date as string whereas in table2 you have date as datetime. so before merging,we need to change date to common data type.
Additional: when you save df to .csv format, the datetime (year in this specific case) is saved as object, so you need to convert it into integer (year in this specific case) when you do the merge. That is why when you upload both df from csv files, you can do the merge easily, while above error will show up if one df is uploaded from csv files and the other is from an existing df. This is somewhat annoying, but have an easy solution if kept in mind.
At first check the type of columns which you want to merge. You will see one of them is string where other one is int. Then convert it to int as following code:
df["something"] = df["something"].astype(int)
merged = df.merge[df1, on="something"]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With