CODEWITHSUNDEEP
pythonpandas
ah bon
ah bon

Reputation: 10011

Combine two columns into one one by one in Python

If I have a data frame like this:

   id    year quarter
0   1  2017.0      Q1
1   2  2018.0      Q2
2   3  2019.0      Q3
3   4     NaN      Q4

How can I get a new dataframe by combining year and period together:

    id    year quarter  period
0    1  2017.0      Q1  2017Q1
1    2  2018.0      Q2  2017Q2
2    3  2019.0      Q3  2017Q3
3    4     NaN      Q4  2017Q4
4    5     NaN     NaN  2018Q1
5    6     NaN     NaN  2018Q2
6    7     NaN     NaN  2018Q3
7    8     NaN     NaN  2018Q4
8    9     NaN     NaN  2019Q1
9   10     NaN     NaN  2019Q2
10  11     NaN     NaN  2019Q3
11  12     NaN     NaN  2019Q4

Or just one column:

    period
0   2017Q1
1   2017Q2
2   2017Q3
3   2017Q4
4   2018Q1
5   2018Q2
6   2018Q3
7   2018Q4
8   2019Q1
9   2019Q2
10  2019Q3
11  2019Q4

Thank you!

Upvotes: 0

Views: 46

Answers (1)

BENY
BENY

Reputation: 323226

We can do pivot + stack 

Yourdf=df.pivot_table(*df.columns,dropna=False).stack(dropna=False).reset_index().assign(yourcol=lambda x : x['year'].astype(int).astype(str)+x['quarter'])
yourdf
      year quarter    0 yourcol
0   2017.0      Q1  1.0  2017Q1
1   2017.0      Q2  NaN  2017Q2
2   2017.0      Q3  NaN  2017Q3
3   2017.0      Q4  NaN  2017Q4
4   2018.0      Q1  NaN  2018Q1
5   2018.0      Q2  2.0  2018Q2
6   2018.0      Q3  NaN  2018Q3
7   2018.0      Q4  NaN  2018Q4
8   2019.0      Q1  NaN  2019Q1
9   2019.0      Q2  NaN  2019Q2
10  2019.0      Q3  3.0  2019Q3
11  2019.0      Q4  NaN  2019Q4

Update 

pd.MultiIndex.from_product([df.year.dropna().astype(str),df.quarter]).map(''.join)

Upvotes: 1

Related Questions