I have a DataFrame like this one:
import pandas as pd
import numpy as np
df = pd.DataFrame({'Sl.No.': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6},
'Area': {0: 'AREA 1',
1: 'AREA 1',
2: 'AREA 1',
3: 'AREA 2',
4: 'AREA 2',
5: 'AREA 2'},
'Location': {0: 'Loc A',
1: 'Loc B',
2: 'Loc C',
3: 'Loc D',
4: 'Loc E',
5: 'Loc F'},
'Total Depth': {0: 5.6, 1: 6.8, 2: 2.9, 3: 4.3, 4: 6.0, 5: 5.3},
'Jan': {0: 2.3, 1: 4.2, 2: 2.1, 3: 2.9, 4: 5.2, 5: 4.3},
'Feb': {0: 1.9, 1: 3.8, 2: 2.0, 3: 2.5, 4: 4.6, 5: 3.2}})
This produces:
| Sl.No. | Area | Location | Total Depth | Jan | Feb | |
|---|---|---|---|---|---|---|
| 0 | 1 | AREA 1 | Loc A | 5.6 | 2.3 | 1.9 |
| 1 | 2 | AREA 1 | Loc B | 6.8 | 4.2 | 3.8 |
| 2 | 3 | AREA 1 | Loc C | 2.9 | 2.1 | 2 |
| 3 | 4 | AREA 2 | Loc D | 4.3 | 2.9 | 2.5 |
| 4 | 5 | AREA 2 | Loc E | 6 | 5.2 | 4.6 |
| 5 | 6 | AREA 2 | Loc F | 5.3 | 4.3 | 3.2 |
I have another DataFrame which has all the columns in the above DataFrame but the Area column, somewhat like the following DataFrame:
df_1 = {'Sl.No.': {0: 1, 1: 2, 2: 3, 3: 4},
'Location': {0: 'Loc A', 1: 'Loc C', 2: 'Loc E', 3: 'Loc F'},
'Total Depth': {0: 5.6, 1: 2.9, 2: 6.0, 3: 5.3},
'Jan': {0: 2.3, 1: 2.1, 2: 5.2, 3: 4.3},
'Feb': {0: 1.9, 1: 2.0, 2: 4.6, 3: 3.2}}
This gives:
| Sl.No. | Location | Total Depth | Jan | Feb | |
|---|---|---|---|---|---|
| 0 | 1 | Loc A | 5.6 | 2.3 | 1.9 |
| 1 | 2 | Loc C | 2.9 | 2.1 | 2 |
| 2 | 3 | Loc E | 6 | 5.2 | 4.6 |
| 3 | 4 | Loc F | 5.3 | 4.3 | 3.2 |
Now, how can I add the Area column to df_1 to get a table like this one:
| Sl.No. | Location | Total Depth | Jan | Feb | Area | |
|---|---|---|---|---|---|---|
| 0 | 1 | Loc A | 5.6 | 2.3 | 1.9 | AREA I |
| 1 | 2 | Loc C | 2.9 | 2.1 | 2 | AREA I |
| 2 | 3 | Loc E | 6 | 5.2 | 4.6 | AREA II |
| 3 | 4 | Loc F | 5.3 | 4.3 | 3.2 | AREA II |