Convert index in column header in python dataframe

Question

I am trying to convert python dataframe into column headers. I am using transpose function but results are not as expected. Which function can be used to accomplish the results as given below?

data is:

Year    2020
Month   SEPTEMBER
Filed Date  29-11-2020
Year    2022
Month   JULY
Filed Date  20-08-2022
Year    2022
Month   APRIL
Filed Date  20-05-2022
Year    2017
Month   AUGUST
Filed Date  21-09-2017
Year    2018
Month   JULY
Filed Date  03-02-2019
Year    2021
Month   MAY
Filed Date  22-06-2021
Year    2017
Month   DECEMBER
Filed Date  19-01-2018
Year    2018
Month   MAY
Filed Date  03-02-2019
Year    2019
Month   MARCH
Filed Date  28-09-2019

and convert it into:

Year     Month     Filed Date
2020     September 29-11-2020
2022     July      20-08-2022

Is the "Year Month Filled Date Year Month Filled Date..." in the first df a column? — SomeDude
– SomeDude, Commented Nov 21, 2022 at 13:29
We can use "Year month Filed date" in column or as an index. — Sam Wittwicky
– Sam Wittwicky, Commented Nov 21, 2022 at 13:32

SomeDude · Accepted Answer · 2022-11-21 14:36:29Z

1

You can do it like this:

df = pd.DataFrame(
    [df1.iloc[i:i+3][1].tolist() for i in range(0, len(df1), 3)],
    columns=df1.iloc[0:3][0].tolist(),
)

print(df):

   Year      Month             Filed
0  2020  SEPTEMBER  Date  29-11-2020
1  2022       JULY  Date  20-08-2022
2  2022      APRIL  Date  20-05-2022
3  2017     AUGUST  Date  21-09-2017
4  2018       JULY  Date  03-02-2019
5  2021        MAY  Date  22-06-2021
6  2017   DECEMBER  Date  19-01-2018
7  2018        MAY  Date  03-02-2019
8  2019      MARCH  Date  28-09-2019

answered Nov 21, 2022 at 14:36

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam Wittwicky · Accepted Answer · 2022-11-21 13:48:29Z

I have found a solution to my problem. Here df1 is:

Year    2020
Month   SEPTEMBER
Filed Date  29-11-2020
Year    2022
Month   JULY
Filed Date  20-08-2022
Year    2022
Month   APRIL
Filed Date  20-05-2022
Year    2017
Month   AUGUST
Filed Date  21-09-2017
Year    2018
Month   JULY
Filed Date  03-02-2019
Year    2021
Month   MAY
Filed Date  22-06-2021
Year    2017
Month   DECEMBER
Filed Date  19-01-2018
Year    2018
Month   MAY
Filed Date  03-02-2019
Year    2019
Month   MARCH
Filed Date  28-09-2019

I used pivot function and approached the problem like this:

df=pd.DataFrame()
for i in range(0,len(df1),3):
    df= df.append(df1.pivot(columns='A', values='B', index=None).bfill(axis = 0).iloc[i])
df.reset_index(drop=True, inplace=True)
print(df)

result:

A   Filed Date  Month   Year
0   29-11-2020  SEPTEMBER   2020
1   20-08-2022  JULY    2022
2   20-05-2022  APRIL   2022
3   21-09-2017  AUGUST  2017
4   03-02-2019  JULY    2018

Collectives™ on Stack Overflow

Convert index in column header in python dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related