CODEWITHSUNDEEP
python
Sam Wittwicky
Sam Wittwicky

Reputation: 3

Convert index in column header in python dataframe

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

Upvotes: 0

Views: 42

Answers (2)

SomeDude
SomeDude

Reputation: 14238

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

Upvotes: 1

Sam Wittwicky
Sam Wittwicky

Reputation: 3

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

Upvotes: 0

Related Questions