python – Populate one dataframe based on information in another dataframe

I have two large dataframes, but am only showing a small subset of them for convenience. One is in the following form (Table 1):

| Country |   Date   | flag | M   | notes     |  V |
|--------------------------------------------------|
| UK      | 20210319 |  1   | 3.0 | No Change | C1 |
| UK      | 20210320 |  0   | 2.0 | Extension | C2 |
| USA     | 20210405 |  0   | 4.0 | New Policy| C1 |
| CAN     | 20210405 |  0   | 1.0 | Update    | C3 |

and the other is in the form (Table 2):

| Country |   Date   |  C1 | C1_flag | C1_notes | 
|-----------------------------------------------|
| UK      | 20210319 | NaN |   NaN   | NaN      | 
| USA     | 20210405 | NaN |   NaN   | NaN      |
| AUS     | 20210505 | NaN |   NaN   | NaN      |
| NZ      | 20210506 | NaN |   NaN   | NaN      |

where after the ‘C1_notes’ column there are additional columns (C2, C2_flag, and C2_notes). In fact this column structure is present for the following codes in the ‘V’ column of the first table (C1, C2, C3, C4, C5, E1, E2, H1, H2, H3), but for brevity I am only showing C1.

I would like Table 2 to be populated using the information from the first table, such that it ultimately has the form:

| Country |   Date   |  C1 | C1_flag | C1_notes  |
|------------------------------------------------|
| UK      | 20210319 | 3.0 |   1     | No Change | 
| USA     | 20210405 | 4.0 |   0     | New Policy|
| AUS     | 20210505 | NaN |   NaN   | NaN       |
| NZ      | 20210506 | NaN |   NaN   | NaN       |

where the ‘C1’ column in Table 2 uses the ‘M’ column of Table 1 as its value, and similarly ‘C1_flag’ column uses the ‘flag’ column. The population of Table 2 is on the basis that the ‘Country’ and ‘Date’ columns in Table 2 match with their counterparts in Table 1.

At first I thought that this was a Pivot/Stack/Melt type operation in Pandas but it turns out not to be so, and a more general mapping may be required. How would I perform this correspondence (both for C1, and also generalizing for the other C, E, and H codes)? Thanks.

Product of the Month September 2016

Source link

Leave a Comment

Your email address will not be published. Required fields are marked *