CODEWITHSUNDEEP
pythonpandasdataframemergecollation
Afraid_Concentrate44
Afraid_Concentrate44

Reputation: 1

Merging 3 DataFrames with a Bespoke Format

I am an apprentice doing my first project using Pandas so apologies if I get any terms wrong or if my question is very obvious.

I am trying to collate the information of 3 DataFrames using UniqueID as the common key.

DataFrames:

  1. Main - contains contact information. Columns: UniqueID, Name, Address, Email, InvestmentEntryNumber (number of Investment rows), CapitalEntryNumber (number of Capital rows)
  2. Investment - contains investment detail. Columns: UniqueID, Details, Cost
  3. Capital - contains capital details: Columns: UniqueID, Description, TotalSpend

The output will be saved as an excel file. The table needs to be formatted in a certain way, where the data in 'Main' does not repeat:

(https://i.sstatic.net/6jQf9KBM.png)

I understand that this is not good data practice to miss out data/have the data laid out like this but the stakeholders want the information in this format specifically.

I have a solution for when I merge 'Main' and 'Investment', but not when I merge in the 'Capital' data too.

Investment_merged_df = Main_df.merge(Investment_df, on="UniqueID")

for each_row in range(1, len(Investment_merged_df["UniqueID"])): if Investment_merged_df["UniqueID][each_row] == Investment_merged_df["UniqueID"][each_row-1]: Investment_merged_df.loc[each_row, "True/False"] = True else: Investment_merged_df.loc[each_row, "True/False"] = False

Investment_merged_df.loc[Investment_merged_df["True/False"] == True, Investment_merged_df.columns[:5]] = ""

Investment_merged_df = Investment_merged_df.drop(columns=['True/False'])

Merging the 'Capital' data to the already merged 'Main' and 'Investment' data means that 'Investment' data becomes repeated when there are more 'Capital' rows that there are 'Investment' rows.

I just want the 'Capital' data to be added on the end of the DataFrame but it is resulting in more rows than I would like.

I was thinking maybe there was a way to use the 'InvestmentEntryNumber' and 'CapitalEntryNumber'?

Upvotes: 0

Views: 32

Answers (0)

Related Questions