pandas: Combining Tables

How to combine multiple tables into one table

so that you can analyze it using what weve learned

A lot practice questions

source: https://www.shanelynn.ie/merge-join-dataframes-python-pandas-index-1/

Thinking about your high school grades, you may recall that you Chinese teacher recorded your Chinese scores (information A), your math teacher recorded your math scores (information B), and English teacher kept track of your English scores (information C). But when your class teachers held a parent-teacher meeting, they had all your scores. This is because they combined information A, B, and C.

Suppose your teachers store your subject scores in different tables. The class teachers need to combine these tables. Thats what we are going to learn. Instead of working with multiple data tables, we will focus on two tables. Then you can easily generalize the processes to multiple tables.

We will mainly learn one pandas function, merge.

Other methods, like concat, join, and update are also useful. Please read the documents for more information.

When we combine two data frames, we need to make sure that the records match between the two. For example, if each teacher decides to put the scores in descending order, the Chinese and math scores are

We need to make sure that Alice got a Chinese score of 90 and a math score of 77. This can be guaranteed by the on parameter of the merge function.

David missed the Chinese exam but took the math exam and scored 60:

m_score.loc[len(m_score)] = {"name": "David", "math": 60}

• Both exams required: use an inner join.
• At least one exam required: use an outer join.
• Keep the Chinese roster: use a left join.
• Keep the math roster: use a right join.

The how parameter determines the rule. In this particular example the Chinese roster is a subset of the math roster, so left/inner and right/outer happen to agree.

Note that the arguments "left" and "right" refers the left and right of the first two arguments (C_score, m_score).

These are five commonly used values of how:

left: use the left as anchor, bring in information from the right

right: opposite to left

outer: only keep data that appear in left $\text{∪}$ right

inner [default]: keep all data from left $\text{∩}$ right

cross: the Cartesian product; pair every row on the left with every row on the right

Well do some exercises to enhance our understanding.

In-class exercise 1

Two companies jointly operate a zoo. Company A is responsible to measure the weights of animals. Company B is in charge of assign food to each animal.

In practice, company B has set a standard for each animal: if the animal is heavier than the standard, they will feed with it less food; otherwise more.

The two tables are stored in zooA.csv and zoob.csv.

Make a table to show the amount of food to feed each animal.

AoL 3 (M)

In-class exercise 2

Read gvkey.csv, permno.csv, and linktable.csv. The two data sources identify the same firms differently.

In-class exercise 3

University X allows students to take either exam A or exam B. To be fair, the score of the students will be adjusted, according to the following rule:

If the student take exam A, and receives $x$, the score will be $\text{90+}\frac{\text{𝑥}\text{−}{\overline{\text{𝑥}}}_{\text{𝐴}}}{{\text{𝑠}}_{\text{𝐴}}}$, where ${\overline{\text{𝑥}}}_{\text{𝐴}}$is the average score of students taking exam A and ${\text{𝑠}}_{\text{𝐴}}$ is the standard deviation. Score of students taking exam B will also be adjusted similarly.

The scores are stored in scoreA.csv and scoreB.csv. Make a table to report the score of the class.

When the left table and the right table use different column names to identify the person, we cannot use on to merge them (why?). But there are two mor parameters to help, left_on and right_on. Let's check out the following example and their usage will be self-explanatory.

In-class exercise 4

Suppose we have three countries reporting some values.

Read in the table country_self.csv, which contains the value reported by the countries themselves.

Read in the table country_UN.csv, which contains the value reported by the United Nations.

We want to merge the two tables so that we can compare the values reported from different sources.

Compute the difference between the self-reported value and UN-reported value for each country in each year.

The last situation to consider is when one table has more than one records. In such cases, we need the validate parameter.

advisor = pd.read_csv("advisors.csv")
advisee = pd.read_csv("advisees.csv")

result = pd.merge(advisor, advisee, how="outer",
                  left_on='name', right_on='advisor',
                  validate="one_to_many")

result = pd.merge(advisor, advisee, how="outer",
                  left_on='name', right_on='advisor',
                  validate="one_to_one")

The file GDP_by_province.csv contains the gross domestic product (GDP) of 31 provinces in China, obtained from the official site of the National Bureau of Statistics (NBS) (https://data.stats.gov.cn/easyquery.htm?cn=E0103).

The file pop_by_provnce.csv contains the information of total population of the same 31 provinces, also obtained from the website.

Please combine the two tables into one so that for each province, in each year, we have both its GDP and population.

Please compute for each province in each year, the average GDP per person. The file unemp_by_province.csv contains information about unemployment rate of urban population. Use it as a proxy of total unemployment rate. Compute for each year the average GDP per working people.

GDP computation might be influenced by the price index. Fortunately, NBS provides us with the price index data in CPI_by_province.csv. In that table, the numbers are the price index compared to THE previous year. For example, the index of Beijing in 2022 is 101.8, meaning that compared to 2021, the price raised by 1.8%. Now please compute the price indices of each year, using 2014 as the benchmark (i.e., all price indices must be in 2014 RMB Yuan).

Please compute the average population of each province and then find the median (np.median) of the averages. Provinces with average populations greater than that number is taken as provinces large populations. Use the region.csv file to get the geographical information. Then compute the average regional real GDP by year.

Compute the average real GDP of all provinces by region and year. Then find the percentages of how the provinces with large populations contribute to their corresponding regions. Explain the results.