Getting index from a Pandas dataframe using a string from another dataframe

Question

I have to dataframes (df), df1 contains countries with the number infections over time (2000+ rows) and df2 contains countries with population numbers (200 rows).

I have been trying to get the population number from df2 to df1 in order to transform the infections to infection density (?) over time.

In my mind I have to iterate over the rows of df1 and check the Country column per index to df2. If the result is True I can copy the the population from df2 to df1. I have tried multiple approaches (just one below) but am at a loss right now :(...could someone give me a push in the right direction?

for index, row in df2.iterrows():
   df_test = df1['Country'].str.contains(row[0])

Edit update with df1, df2 and preferred outcome: df1

   ObservationDate  Country/Region  Confirmed
0        -2.118978       Hong Kong        0.0
1        -2.118978           Japan        2.0
2        -2.118978           Macau        1.0
3        -2.118978  Mainland China      547.0
4        -2.118978     South Korea        1.0

df2

                 0             1
0             China  1.401580e+09
1             India  1.359321e+09
2  United States[c]  3.293798e+08
3         Indonesia  2.669119e+08
4            Brazil  2.111999e+08

df_preferred

   ObservationDate  Country/Region  Confirmed  Population
0        -2.118978       Hong Kong        0.0
1        -2.118978           Japan        2.0
2        -2.118978           Macau        1.0
3        -2.118978  Mainland China      547.0  1.401580e+09
4        -2.118978     South Korea        1.0

You do not give enough element for me to give any code, but this looks like a use case for merge. — Serge Ballesta
– Serge Ballesta, Commented Mar 9, 2020 at 9:05
can you update your question with two data frames and the result you are expecting to get? — Yasi Klingler
– Yasi Klingler, Commented Mar 9, 2020 at 9:06

Valdi_Bo · Accepted Answer · 2020-03-09 09:57:23Z

1

Assume that your both DataFrames are as follows:

  Country        Date  Infection
0   Aaaaa  2020-03-02         10
1   Aaaaa  2020-03-04         20
2   Bbbbb  2020-03-02         15
3   Bbbbb  2020-03-04         20
4   Ccccc  2020-03-02         12
5   Ccccc  2020-03-04         40

  Country  Population
0   Aaaaa    10000000
1   Bbbbb    35200000
2   Ccccc    48700000

Then, to merge them and save the result in another DataFrame you can run:

df3 = df1.merge(df2, on='Country')

getting:

  Country        Date  Infection  Population
0   Aaaaa  2020-03-02         10    10000000
1   Aaaaa  2020-03-04         20    10000000
2   Bbbbb  2020-03-02         15    35200000
3   Bbbbb  2020-03-04         20    35200000
4   Ccccc  2020-03-02         12    48700000
5   Ccccc  2020-03-04         40    48700000

And to compute the infection rate you can execute:

df3['InfectionRate'] = df3.Infection / df3.Population

answered Mar 9, 2020 at 9:57

Valdi_Bo

31.1k4 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sumerechny Over a year ago

Amazing, this tackles most of my problems, some countries are not merges but this is because their name differs between df's. Thank you!

coco18 · Accepted Answer · 2020-03-09 09:57:33Z

0

I think this will do the work:

data1 = {'Country':['Germany', 'USA',"Canada", "UK"], 'Inf':[2,5,6,8]} 
data2 = {'Country':['Germany', 'USA',"Canada", "UK"], 'popul':[80,300,30,70]} 
# Creating the dataframes
df1 = pd.DataFrame(data1) 
df2 = pd.DataFrame(data2) 
# Setting the index from the column country
df2 = df2.set_index('Country')
df1 = df1.set_index('Country')
# concating the dataframes along axis 1 without sorting
pd.concat([df1,df2], axis=1, sort=False)

answered Mar 9, 2020 at 9:57

coco18

1,1551 gold badge12 silver badges19 bronze badges

Collectives™ on Stack Overflow

Getting index from a Pandas dataframe using a string from another dataframe

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related