Creating a new DataFrame from another DataFrame column

[Ilangos]

Hi All,

I'm newbie to python. I've a task to automate wherein I have to create separate excel files based on the RTO code available in an excel sheet using the main data available in another excel sheet.

import pandas as pd
import xlrd

Rto_Main_Sheet = pd.read_excel("D:\Testing_MIS\Auto_Task\FINAL GLM SHEET.xlsx",sheet_name='Jeep tata mahindra')
Rto_Main_Sheet.head(5)

Output:
     ID   Combined - TA100	RTO Code	Age	Discount
0	  1		          FICH	AN01		0	50
1	  2		          FIWR	AN01		0	50
2	  3		          FIRN	AN01		0	50
3	  4		          FICO	AN01		0	50
4	  5		          JPWR	AN01		0	70

Rto_Code = pd.read_excel("D:\Testing_MIS\Auto_Task\RTO_Code_AN01-HP46.xlsx",sheet_name='RTO_Code_AN01-HP46')
Rto_Code.head(5)

Output:
    RTO
0	AN01
1	AN02
2	AP01
3	AP02
4	AP03

[/python]

Now, for each RTO Code available have to create a separate DataFrame and write it to excel file. The output should be as follows:-
Here, only the RTO Code has to be changed and rest all the data of the column would be as is from the Rto_Main_Sheet dataframe.

Output:
     ID   Combined - TA100	RTO Code	Age	Discount
0	  1		          FICH	AN02		0	50
1	  2		          FIWR	AN02		0	50
2	  3		          FIRN	AN02		0	50
3	  4		          FICO	AN02		0	50
4	  5		          JPWR	AN02		0	70

Any sort of assistance or help is much appreciated.

Thank you in advance.

[Ilangos]

Hi All,

Was able to figure it out using the stackoverflow. This what I came up with...

import pandas as pd
import openpyxl
import xlrd

writer = ExcelWriter('D:\Testing_MIS\Auto_Task\Data.xlsx')

for idx,row in Rto_Code.iterrows():
    code=row["RTO"]
    for idx,row in Rto_Main_Sheet.iterrows():
        Rto_Main_Sheet.at[idx,'RTO Code'] = code
    Rto_Main_Sheet.to_excel(writer,sheet_name=code)
    writer.save()

I'm sure there is much better way to do this.. If anyone can think of one please post.

Thank you in advance.