0

I have a Excel sheet containing data as shown below where A,B,C,D,E,F,G,H represents Column name in the excel sheet. 

  A        B       C      D     E     F     G      H    
Rahul     e34   Pradeep  e44  Azhar  t54  Pavan   e24 
Venkat    r45   Akash    e14  Vipul  r15  Fairo   e45 
Akshay    e44   Vivek    e99  Kumar  e55  Asad    t14

I want the entire data in two columns A and B as shown below.

Rahul      e34   
Pradeep    e44  
Azhar      t54  
Pavan      e24 
Venkat     r45   
Akash      e14  
Vipul      r15  
Fairo      e45 
Akshay     e44   
Vivek      e99 
Kumar      e55 
Asad       t14

How do i do using Pandas, Can anyone assist me please.

taha
  • 722
  • 7
  • 15
Anand Patil
  • 15
  • 3

1 Answers1

2

Reshape the data, using numpy, to get a 2 column format : 

pd.DataFrame(np.reshape(df.to_numpy(),(-1,2)))

       0    1
0   Rahul   e34
1   Pradeep e44
2   Azhar   t54
3   Pavan   e24
4   Venkat  r45
5   Akash   e14
6   Vipul   r15
7   Fairo   e45
8   Akshay  e44
9   Vivek   e99
10  Kumar   e55
11  Asad    t14
sammywemmy
  • 27,093
  • 4
  • 17
  • 31
  • Thanks for your code. I am new to Python this is what i have tried from `openpyxl import Workbook wb = Workbook() import pandas as pd import numpy as np df = pd.read_excel (r'C:\\Users\\Admin\\Desktop\\12 june\\Column.xlsx') pd.DataFrame(np.reshape(df.to_numpy(),(-1,2))) wb.save(filename = 'C:\\Users\\Admin\\Desktop\\12 june\\Column1.xlsx')` When i run this i in the excel sheet is empty, Can you please assist me in fixing this issue. – Anand Patil Jun 12 '20 at 06:32
  • ok. what is your output when you read the data into pandas, did you pass in the sheet name? [read excel](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html) can help – sammywemmy Jun 12 '20 at 06:33
  • your code is working fine,I will get output as shown above in your code. But How do i remove Column A values (0,1,2,3,4,5,6,7,8,9,10,11) and Row 1 (0,1) – Anand Patil Jun 17 '20 at 06:20