Concatenating list of data frames

Question

I needed a simple pandas support. I am trying to concatenate a list of dataframe. I have a list of dataframe that looks like below.

Month   First   Second  Third
Jan     4535    34534   54662
Feb     6768    846     2545
Mar     978     23      1255
Apr     356     9766    7855
            
            
Quarter First   Second    Third
Q1      85456     3256     6455
Q2      41256     125432   1356
Q3      45546     13565    15646
Q4      746       13456    16545
            
            
Calendar    First   Second      Third
1/01/2021   1452    56853       45698635
1/02/2021   16565   5653463     565746
1/02/2021   566534  465464665   485634
1/03/2021   4653    126689      465874

My desired output:

Date        First   Second  Third   Type
Jan         4535    34534   54662   Month
Feb         6768    846     2545    Month
Mar         978     23      1255    Month
Apr         356     9766    7855    Month
Q1          85456   3256    6455    Quarter
Q2          41256   125432  1356    Quarter
Q3          45546   13565   15646   Quarter
Q4          746     13456   16545   Quarter
1/01/2021   1452    56853   45698635    Calendar
1/02/2021   16565   5653463 565746  Calendar
1/02/2021   566534  465464665   485634  Calendar
1/03/2021   4653    126689  465874  Calendar

Any help would be appreciated.

Answer 1

Use pd.concat

out = pd.concat([df.assign(Type=df.columns[0]).rename(columns={df.columns[0]: 'Date'})
                    for df in [df1, df2, df3]], ignore_index=True)
print(out)

# Output
         Date   First     Second     Third      Type
0         Jan    4535      34534     54662     Month
1         Feb    6768        846      2545     Month
2         Mar     978         23      1255     Month
3         Apr     356       9766      7855     Month
4          Q1   85456       3256      6455   Quarter
5          Q2   41256     125432      1356   Quarter
6          Q3   45546      13565     15646   Quarter
7          Q4     746      13456     16545   Quarter
8   1/01/2021    1452      56853  45698635  Calendar
9   1/02/2021   16565    5653463    565746  Calendar
10  1/02/2021  566534  465464665    485634  Calendar
11  1/03/2021    4653     126689    465874  Calendar

Answer 2

Use pd.concat to join them vertically, bfill horizontally (axis=1) to combine the non-same-named columns, then insert and drop:

dataframes = {
    'Month': df1,
    'Quarter': df2,
    'Calendar': df3,
}

df = pd.concat([d.assign(Type=col) for col, d in dataframes.items()])
df.insert(0, 'Date', df[list(dataframes)].bfill(axis=1).iloc[:, 0])
df = df.drop(list(dataframes), axis=1).reset_index(drop=True)

Output:

>>> df
         Date   First     Second     Third      Type
0         Jan    4535      34534     54662     Month
1         Feb    6768        846      2545     Month
2         Mar     978         23      1255     Month
3         Apr     356       9766      7855     Month
4          Q1   85456       3256      6455   Quarter
5          Q2   41256     125432      1356   Quarter
6          Q3   45546      13565     15646   Quarter
7          Q4     746      13456     16545   Quarter
8   1/01/2021    1452      56853  45698635  Calendar
9   1/02/2021   16565    5653463    565746  Calendar
10  1/02/2021  566534  465464665    485634  Calendar
11  1/03/2021    4653     126689    465874  Calendar

Answer 3

import pandas as pd
import numpy as np

#import csv

a = pd.read_csv("a.csv").dropna()
b = pd.read_csv("b.csv").dropna()
c = pd.read_csv("c.csv").dropna()

a['Type'] = 'Month'
b['Type'] = 'Quarter'
c['Type'] = 'Calendar'
a = a.rename(columns={"Month": "Date"})
b = b.rename(columns={"Quarter": "Date"})
c = c.rename(columns={"Calendar": "Date"})

frames = [a, b, c]
result = pd.concat(frames)

result


Date    First   Second  Third   Type