Search code examples
pythonpandasdatetimepandas-groupbymulti-index

How to chance DateTime multiindex back into normal DateTime index?

I have two dataframes which their indexes different as shown below;

df1:

                 C1
   Y     M  D
    2020  5  1   5
             2   7
             3   34
             4   4
             5   98

df2

                C1 
    Date
    2020-5-6   2
    2020-5-7   11
    2020-5-8   15
    2020-5-9   3
    2020-5-10  8

Due to the way they were cleaned and grouped etc the index is in different formats. I need to merge these dataframes together.

Is there anyway of simply converting the multiindex back to a single index? Or can I merge them the way they are? I can't seem to figure it out.

Any help much appreciated!

Dataframe 1

Dataframe 2

Solution

  • Here's another approach which is cleaner than the linked question:

    df.index = pd.to_datetime([f'{y}-{m}-{d}' for y,m,d in df.index],
                          format='%Y-%m')

    Output:

                C1
2020-05-01   5
2020-05-02   7
2020-05-03  34
2020-05-04   4
2020-05-05  98

    Note: for Python 2.7, instead of f'{y}-{m}-{d}', do

    '{}-{}-{}'.format(y,m,d)