0

I have a dataframe detailing some statistics of store revenues:

#Create data
data = {'Day': [1,1,2,2,3,3],
        'Where': ['A','B','A','B','B','B'],
        'What': ['x','y','x','x','x','y'],
        'Dollars': [100,200,100,100,100,200]}

index = range(len(data['Day']))

columns = ['Day','Where','What','Dollars']


df = pd.DataFrame(data,  index=index, columns=columns)
df

ddd

and a dataframe with some informations on the stores (longitude and latitude let's say):

Create data

data = {'Where': ['A','B'],
        'Lon': [10,11],
        'Lat': [20,22]}

index = range(len(data['Where']))

columns = ['Where','Lon','Lat']


df2 = pd.DataFrame(data,  index=index, columns=columns)
df2

ccc I want to add the information of the second dataframe into the first one. My best try was:

df3=df
df3['Lon'] = df2[df2.Where == df.Where, 'Lon']

but this throws an error:

ValueError: Can only compare identically-labeled Series objects

What is the correct way of doing this?

Community
  • 1
  • 1
shamalaia
  • 2,282
  • 3
  • 23
  • 35
  • 1
    Perhaps you might find [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) useful? – cs95 Jan 25 '19 at 00:53
  • Basically, you need `df.merge(df2[['Where', 'Lon']], on='Where', how='left')` at the very least. There are alternatives with `map` for adding a single column to another DataFrame which is faster than a conventional SQL style join, but that is also covered in the link. – cs95 Jan 25 '19 at 00:54

1 Answers1

1

Try this, you just need a pd.merge

df3=pd.merge(df1,df2, on='Where')

For more details visit, https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html

Mohamed Thasin ah
  • 10,754
  • 11
  • 52
  • 111
  • 1
    you should prefer DataFrame.merge instead of the top level merge: https://stackoverflow.com/questions/53645882/pandas-merging-101 – cs95 Jan 25 '19 at 00:57