Combine columns in DataFrame

BarbG 0
Viewed 41

I want to combine eight columns into two columns in a DataFrame. The two columns would be Date and Moon Phase, and date should be an index and datetime. It should be in order of phases - New Moon, First Q, Full Moon, Third Q. Concatenate and merge don't work because it is one DataFrame.

Example:

     Date      Moon Phase   Date     Moon Phase    Date    Moon Phase   Date      Moon Phase
0 2020-08-18 New Moon 2020-08-25 First Quarter 2020-09-01 Full Moon 2020-09-10 Third Quarter
1 2020-09-17 New Moon 2020-09-23 First Quarter 2020-10-01 Full Moon 2020-10-09 Third Quarter
2 2020-10-16 New Moon 2020-10-23 First Quarter 2020-10-31 Full Moon 2020-11-08 Third Quarter
lambda pandas loops
1 Answers
import pandas as pd

data = [
        ['2020-08-18', 'New Moon', '2020-08-25', 'First Quarter', '2020-09-01', 'Full Moon', '2020-09-10', 'Third Quarter'],
        ['2020-09-17', 'New Moon', '2020-09-23', 'First Quarter', '2020-10-01', 'Full Moon', '2020-10-09', 'Third Quarter'],
        ['2020-10-16', 'New Moon', '2020-10-23', 'First Quarter', '2020-10-31', 'Full Moon', '2020-11-08', 'Third Quarter']
       ]

columns = ['Date', 'Moon Phase', 'Date', 'Moon Phase', 'Date', 'Moon Phase', 'Date', 'Moon Phase']

df = pd.DataFrame(data, columns=columns)
create a column with a list of lists with the columns in pairs [[date, phase], ..., ]
df['tmp'] = df.apply(lambda row: [[row[i], row[i+1]] for i in range(0, 8, 2)], axis=1)
separates the list of lists into rows
df = df.iloc[:, 8:].explode('tmp')
create the desired columns
df['Date'] = df.tmp.str[0]
df['Moon Phase'] = df.tmp.str[1]
df = df.iloc[:, 1:]

df.Date = pd.to_datetime(df.Date)
df = df.set_index('Date')

print(df)

               Moon Phase
Date                     
2020-08-18       New Moon
2020-08-25  First Quarter
2020-09-01      Full Moon
2020-09-10  Third Quarter
2020-09-17       New Moon
2020-09-23  First Quarter
2020-10-01      Full Moon
2020-10-09  Third Quarter
2020-10-16       New Moon
2020-10-23  First Quarter
2020-10-31      Full Moon
2020-11-08  Third Quarter
josepn0
Related