[Python] Take columns from two dfs in a specific order and create a new df

I have two large dataframes. One contains a set of information from jan. 2020 (f2020). The other dataframe (f2021) contains the same information, but for jan. 2021. The dataframes are equal, but the values differ. (Same no.of rows/cols, key names etc.)

I have used the fact that they are equal to loop over each item in f2021 and subtracting the same item from f2020. The result is added to the f2021 as a column with key = 'diff_key'.

I have created an example, this is before any calculations are done:

f2021 = pd.DataFrame({'C3456R_[Ah]': {0: 2.5,
1: 4.3, 2: 5.9},
'C8734_[Ah]': {0: 1.9,
1: 2.3, 2: 3.9},
'ts': {0: pd.Timestamp('2020-01-01 02:00:00'),
1: pd.Timestamp('2020-01-01 03:00:00'),
2: pd.Timestamp('2020-01-01 04:00:00')}})


Then I do the calculations with values from f2020 and get a resulting f2021 that looks like this:

f2021 = pd.DataFrame({'C3456R_[Ah]': {0: 2.5,
1: 4.3, 2: 5.9},
'C8734_[Ah]': {0: 1.9,
1: 2.3, 2: 3.9},
'ts': {0: pd.Timestamp('2020-01-01 02:00:00'),
1: pd.Timestamp('2020-01-01 03:00:00'),
2: pd.Timestamp('2020-01-01 04:00:00')},
'diff_C3456R_[Ah]': {0: 0.1,
1: 0.7, 2: 0.2},
'diff_C8734_[Ah]': {0: 0.1,
1: 1.2, 2: 2.2}})


Now, I would like to create a new df that should take both original columns for the same key in f2021 and f2020, add a suffix (_2020 and _2021), and then take the 'diff' column for that key, for all keys. The columns must be sorted so the order is like:

'C3456R_[Ah]2021','C3456R[Ah]2020', 'diff_C3456R[Ah]', 'C8734_[Ah]2021, C8734[Ah]2020, diff_C8734[Ah]... etc.

and the order of the keys in the new df should follow the order of the original keys in f2021.

I tried solving this by creating a list that is in the order I want by looping over different if statements, and appending in lists etc. And thought I could then solve by a merge. First give all keys in both frames suffixes. But this seems like a heavy way to solve this, and harder than one should think.

Is it a smooth way to do this?

Continue reading...