I'm joining two dataframes, each from a separate csv file. These two dataframes have some common column names (headers), but different data. I want to concatenate these two dataframes and merge columns that have the same name, e.g. df1 will have a column called "Full Name", and df2 will also have a column called "Full Name". But when I merge them I get "Full Name" from the first dataframe and "Full Name.1" from the second dataframe.
df1 =
Full Name Employee ID
Jade Ralker 1243
Paul Anthony Dill 3445
Anthony Carrerass 6775
Lue Toonn 6456
df2 =
Full Name Employee ID
Adam Blakely 2123
Adam Borris 2231
Agniezka Lim 7891
df = pd.concat([d1, df2])
dfConcatenatedAndGrouped = df.groupby(level=0, axis=1).sum()
(I do this groupby line because concatenating them together only places the header of the second dataframe at the top of the axis but the actual data rows in the same column start from row 5776 which is one row after the end of the first dataframe row.)
There are plenty of other columns in my code that I did not include here for simplicity's sake
What I get:
Full Name.1 Employee ID.1 Full Name Employee ID
0 Jade Ralker 1243 NaN NaN
1 Paul Anthony Dill 3445 NaN NaN
2 Ian Hopsins 6775 NaN NaN
3 Anthony Carrerass 6543 NaN NaN
4 Lue Toon 6456 NaN NaN
5 NaN NaN Adam Blakely 2123
6 NaN NaN Adam Borris 2231
7 NaN NaN Agniezka Lim 7891
Desired Output:
Full Name Employee ID
0 Jade Ralker 1243
1 Paul Anthony Dill 3445
2 Ian Hopsins 6775
3 Anthony Carrerass 6543
4 Lue Toon 6456
5 Adam Blakely 2123
6 Adam Borris 2231
7 Agniezka Lim 7891
as requested by lanS here is the listed column names
df1:
Out[37]:
['Unnamed: 0',
'Employee ID',
'First Name',
'Last Name',
'Full Name',
'Worker Type',
'Worker Sub type',
'Management Level',
'Country',
'State Province',
'Location',
'Manager',
'Supervisory Organization',
'Supervisory Org Level 03 From The Top',
'Supervisory Org Level 04 From The Top',
'Supervisory Org Level 05 From The Top',
'Supervisory Org Level 06 From The Top',
'Supervisory Org Level 07 From The Top',
'Supervisory Org Level 08 From The Top',
'Managers Level',
'Managers Email Primary Work',
'Managers Cost Center',
'Manager Level 02',
'Manager Level 02 Management Level',
'Manager Level 03',
'Manager Level 03 Management Level',
'Company',
'Business Unit',
'Cost Center',
'Position',
'Position ID',
'Job Family Group',
'Job Family',
'Job Profile',
'Job Title',
'Email Primary Work',
'FTE ',
'Is Manager',
'Original Hire Date',
'Continuous Service Date',
'Hire Date',
'Next Birthday',
'Age Group',
'Length of Service',
'EEO Classification',
'UK Employment Subgroup',
'Hispanic or Latino',
'Gender',
'Disability Statuses',
'Race Ethnicity Locale Sensitive ',
'Marital Status',
'Age',
'Citizenship Status Locale Sensitive ',
'Military Statuses Locale Sensitive ',
'Respondent Submission Date',
'Survey Name',
'Survey Sent Date',
'Survey Close Date',
"Senior leadership listens and acts on employees' views and concerns ",
'I have a clear understanding of what is expected of me ',
'I have the training I need to do my job effectively ',
'I have a clear understanding of what this company is trying to achieve (its goals and objectives) ',
'My line manager listens to my ideas and suggestions for change and acts on them ',
'My line manager sets clear and measurable work objectives ',
'My line manager recognises my efforts and thanks me for a job well done ',
'People communicate openly here regardless of position or level ',
'The people I work with cooperate to get the job done ',
'When things go wrong at work, we concentrate on making them better, rather than on who to blame ',
'There is a good atmosphere and sense of team spirit amongst the people I work with ',
'I trust the senior leadership of Centrica ',
"I'm able to resolve customer issues on my own initiative ",
'We use customer feedback to improve our product and/or service quality ',
'And finally, do you have any other comments or feedback that you would like to share at this time, either related or unrelated to the questions asked in this survey?',
'On a scale of 0 to 10, how likely is it that you would recommend Centrica as a place to work to friends and family?',
'I feel energised at work ',
'I feel positive about myself at work ',
'I have trusting relationships at work ',
'My manager cares about my wellbeing ',
'I like being challenged in my job ',
'I usually manage difficulties one way or another at work ',
'I recover from stressful experiences quickly ',
"Overall, I'm satisfied with Centrica as a place to work ",
'I rarely think about looking for a new job with another company ',
'I am proud to work for Centrica ',
'I would recommend Centrica as a great place to work ',
'I am empowered to make decisions necessary to do my job well ',
'The balance between my private life and job is about right ',
'My career development aspirations are being met ',
'I feel like I belong here ',
'I feel positive about the direction in which this business is heading ',
'I understand what is required for me to progress my career ',
'I feel the appropriate channel for the appropriate message is used by senior leaders (e g learn / know / action something) ',
'Senior leaders are doing the right things to make this company a success ',
'I get what I need from other departments/teams to enable me to do my job effectively ',
'We think about the customer first before making strategic decisions ',
'People respect individual differences at work (e g cultures, backgrounds, religious beliefs etc ) ',
'I feel comfortable speaking up and challenging decisions without the fear of negative repercussions ',
'Everyone here has an equal opportunity to develop regardless of their background or identity ',
'I understand how my job contributes to the overall success of this company ',
'Compared to other people in my team or other departments doing a similar role, my total reward package is fair ',
"I'm given the chance to be involved in how changes are implemented ",
'I am aware of the support available to advance and progress my career if I want to ',
'I can be my true self at work without feeling judged ',
'What more could your manager do to help you be more effective in your role? Please explain how this will help improve your performance ',
'I have what I need to do my job effectively ',
'I am trusted to make decisions about things that are my responsibility ',
'My work is interesting and challenging ',
'When things go wrong at work we concentrate on making them better rather than on who to blame ',
'I feel comfortable raising or escalating health and safety issues or concerns ',
'This company is doing all it can to support sustainability ',
'My line manager keeps me up-to-date with what is happening at work ',
'I am given the chance to be involved in how changes are implemented ',
'During the past 12 months have you personally experienced a situation where you have felt unable to speak up and challenge a decision without the fear of negative repercussions?',
'I understand how my job contributes to the overall success of my business ',
'Please provide details of either the situation or the action required to allow you to feel confident challenging decisions and speaking up ',
'My opinion counts at work ',
"I'm treated fairly and with respect at work ",
'My line manager gives me clear and honest feedback that helps me to improve my performance ',
'This company is doing a good job of delivering against its strategy, goals and objectives ',
"I'm trusted to make decisions about things that are my responsibility ",
'On a scale of 1 to 5 how likely is it that you would recommend our products and/or services to friends and family? 1 being least likely and 5 being most likely ',
"My manager makes an effort to keep-in-touch with me when I'm working from home ",
'What one change would make the biggest positive difference to you being able to perform your role effectively?',
'What is the best thing about working for this company?',
"Overall, I'm satisfied with LRECS as a place to work ",
'I am proud to work for LRECS ',
'I would recommend LRECS as a great place to work ']
df2:
Out[38]:
['Unnamed: 0',
'Recorded Date',
"Senior leadership listens and acts on employees' views and concerns.",
'I trust the senior leadership of Centrica.',
'On a scale of 0 to10, how likely is it that you would recommend Centrica as place to work to friends and family? (0=not at all – 10 definitely).',
"Overall, I'm satisfied with Centrica as a place to work.",
'I rarely think about looking for a new job with another company.',
'I am proud to work for Centrica.',
'I would recommend Centrica as a great place to work.',
'I feel energised at work.',
'I feel positive about myself at work.',
'I have trusting relationships at work.',
'My manager cares about my wellbeing.',
'I like being challenged in my job.',
'I usually manage difficulties one way or another at work.',
'I recover from stressful experiences quickly.',
'I am empowered to make decisions necessary to do my job well.',
'I have a clear understanding of what is expected of me.',
'I have the training I need to do my job effectively.',
'The balance between my private life and job is about right.',
'If I needed to talk about my mental health, I feel I would be listened to and offered support.',
'I feel comfortable raising or escalating health and safety issues or concerns.',
'We use customer feedback to improve our product and/or service quality.',
'Senior leaders keep me up-to-date with any important things that are happening across this company.',
'Senior leaders are committed to delivering high quality products and services to external customers.',
'I have a clear understanding of what my business is trying to achieve (its goals and objectives).',
'I understand how my job contributes to the overall success of my business.',
'Where I am currently working, I have what I need to perform my job effectively.',
'If you could change one thing that would have the biggest positive impact on your experience at work, what would that be?',
'Any finally, do you have any other comments or feedback that you would like to share at this time, either related or unrelated to the questions asked in this survey?',
'Is Manager',
'Manager',
'Age',
'Business Title',
'Business Unit',
'Company',
'Continuous Service Date',
'Cost Center',
'Cost Center - ID',
'Employee ID',
'FTE',
'FTE %',
'Full Name',
'Gender',
'Hire Date',
'Job Family',
'Job Family Group',
'Job Profile',
'Job Title',
'Last Login',
'Length of Service',
'Level',
'Manager ID',
'N-1 Organization',
'N-2 Organization',
'N-3 Organization',
'N-4 Organization',
'N-5 Organization',
'N-6 Organization',
'Original Hire Date',
'Position',
'Position ID',
'Status',
'Supervisory Organization',
'Tenure',
'Work Address - Country',
'Work Address - Location',
'Work Address - State/Province',
'Worker Sub-Type',
'Worker Type',
'Email Primary',
'Full Name.1',
'Employee ID.1',
'Timestamp Of Last Metadata Update',
'Org Hierarchy Direct Report Units',
'Parent Unit: CBS Hierarchy',
'Org Hierarchy Direct Report Units.1',
'Parent Unit: CBS 2']
CodePudding user response：
Your column names must be mismatched, or you're passing additional arguments to pandas.concat (axis?) that you're not showing us:
from io import StringIO
import pandas
csv1 = StringIO("""\
Full Name Employee ID
Jade Ralker 1243
Paul Anthony Dill 3445
Anthony Carrerass 6775
Lue Toonn 6456
""")
csv2 = StringIO("""\
Full Name Employee ID
Adam Blakely 2123
Adam Borris 2231
Agniezka Lim 7891
""")
df1 = pandas.read_csv(csv1, sep="\s\s ", engine="python")
df2 = pandas.read_csv(csv2, sep="\s\s ", engine="python")
pandas.concat([df1, df2], ignore_index=True)
And I get:
Full Name Employee ID
0 Jade Ralker 1243
1 Paul Anthony Dill 3445
2 Anthony Carrerass 6775
3 Lue Toonn 6456
4 Adam Blakely 2123
5 Adam Borris 2231
6 Agniezka Lim 7891
CodePudding user response：
pd.concat should work in case. May be you need to change d1 to df1 like this df = pd.concat([df1, df2],ignore_index=True). If it was not worked then go with append.
import pandas as pd
# First DataFrame
df1 = pd.DataFrame({'id': ['A01', 'A02', 'A03', 'A04'],
'Name': ['ABC', 'PQR', 'DEF', 'GHI']})
# Second DataFrame
df2 = pd.DataFrame({'id': ['B05', 'B06', 'B07', 'B08'],
'Name': ['XYZ', 'TUV', 'MNO', 'JKL']})
df3 = pd.DataFrame({'City': ['MUMBAI', 'PUNE', 'MUMBAI', 'DELHI'],
'Age': ['12', '13', '14', '12']})
# appending multiple DataFrame
result = df1.append([df2, df3])
display(result)
I hope this will helpful for you.