Блог

У меня есть несколько файлов Excel, следующих ниже (вы можете скачать тестовые данные отсюда), которые имеют 3 строки в качестве заголовков, которые feature_row являются уникальными, которые могут использоваться в качестве имен столбцов фреймов данных.

Мне нужно объединить их на основе date нескольких заголовков и сохранить их.

df1:

         country           jp           jp
0  feature_name      stock_a      stock_b
1   update_date   2021-09-15   2021-09-15
2      2006-6-2     100.0000     100.0000
3      2006-6-9      99.7800     100.2300
4     2006-6-16      97.7100      99.6300
5     2006-6-23      98.1100      98.4900
6     2006-6-30      97.5800      98.5700
 

df2:

         country           us           us
0  feature_name     cpi_food     core_cpi
1   update_date   2021-09-24   2021-09-24
2    1999-12-30   1,000.0000   1,000.0000
3      2000-1-4   1,025.7899   1,020.5759
4      2000-1-5   1,032.8712   1,024.9281
5      2000-1-6   1,073.6569   1,050.7501
6      2000-1-7   1,113.7831   1,081.9492
7     2000-1-10   1,135.3109   1,102.0936
8     2000-1-11   1,102.9618   1,037.0133
 

df3:

         country           cn           cn
0  feature_name    iCPI_food    iCPI_rent
1   update_date   2021-09-27   2021-09-27
2      2016-1-1      99.9712      99.9000
3      2016-1-2      99.9295      99.7684
4      2016-1-3     100.0744     100.1282
5      2016-1-4      99.8702      99.5830
6      2016-1-5      99.9254      99.8024
 

Я могу зациклить файлы Excel и объединить их в один с приведенным ниже кодом, но первая и третья строки заголовков пропущены.

 import pandas as pd
import numpy as np
import glob

dfs = pd.DataFrame()

for file in glob.glob('./*.xlsx'):
    df = pd.read_excel(file, skiprows=[0, 2])
    df = df.rename(columns={'country': 'date'})
    df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d').astype(str)
    df = df.sort_values(by='date')
    print(df)
    if dfs.empty:
        dfs = df.copy()
    else:
        dfs = dfs.merge(df, on='date', how='outer')
    dfs = dfs.sort_values(by='date')
    dfs.to_excel('./data.xlsx')
 

Как я мог получить ожидаемый файл Excel, подобный этому:

Обновленный результат с обновленным кодом @jezrael:

Вы можете избежать удаления MultiIndex , преобразовать первый столбец в список фреймов данных DatetimeIndex и добавить его в список для объединения с помощью concat DatetimeIndex внешнего соединения по умолчанию:

 dfs, cols = [], []
for file in glob.glob('./*.xlsx'):
    df = pd.read_excel(file)

    first = df.columns[0]
    df = df.set_index(first)
    df.index = pd.to_datetime(df.index)
    df = df.sort_index()
    dfs.append(df)
    cols.extend(df.columns)

df_fin = pd.concat(dfs).reindex(cols, axis=1).reset_index()
df_fin.to_excel('./data.xlsx', merge_cells=False)
 

Другой вариант:

 dfs = []
for file in glob.glob('./*.xlsx'):
    print(file)
    df = pd.read_excel(file, header=[0, 1, 2], skipfooter=2)

    first = df.columns[0]
    df = df.set_index(first)
    df.index = pd.to_datetime(df.index, format='%Y-%m-%d').astype(str)
    df = df.sort_index()
    dfs.append(df)

df_fin = pd.concat(dfs).reset_index()
df_fin = df_fin.dropna(how='all').sort_values(df_fin.columns[0])
df_fin.to_excel('./data.xlsx')
 

 df1 = pd.read_excel('data1.xlsx',header=[0,1,2])
df2 = pd.read_excel('data2.xlsx',header=[0,1,2])
df3 = pd.read_excel('data3.xlsx',header=[0,1,2])    
pd.concat([df1,df2,df3],ignore_index = True)