How Can I Merge Two Columns Into One (final Output) (python/sqlite)

How can i merge two columns into one (final output) (python/sqlite) import sqlite3 import pandas as pd # load data df = pd.read_csv('CurriculumAuditReport.csv') # strip whitespac

Solution 1:

You can either do it on the SQLite side or in Pandas.

Option 1 (using SQLite):

qry = """
SELECT department || ' ' || cast(count(*) as text) as col_name
FROM MyTable
WHERE CompletedTraining = 'Incomplete'
GROUP BY department
"""df = pd.read_sql(qry, con)

Option 2 (using Pandas):

assuming we have the following DataFrame:

In[79]: dfOut[79]:
   departmentcnt0AQPSD61ASD82CO23ECARS34ED65EO46ISD47MSCD58OIS19RD210TTD4

let's convert it to a single column DF:

In [80]: df['department'] = df['department'] + ' ' + df.pop('cnt').astype(str)

In [81]: df
Out[81]:
   department
0     AQPSD 6
1       ASD 8
2        CO 2
3     ECARS 3
4        ED 6
5        EO 4
6       ISD 4
7      MSCD 5
8       OIS 1
9        RD 2
10      TTD 4

PS this can easily be done without using SQLite at all, but we would need a small reproducible sample data set in the original format (which would reproduce data from CurriculumAuditReport.csv)

Solution 2:

This is a step by step solution:

Add a new column and convert the count column to string with "astype(str)

df['new_column'] = df['Department'] + " " + df['count'].astype(str)

Delete columns that you don't need

del df['Department']del df['count']

Rename new_column

df.rename(columns={'new_column': 'Department'}, inplace=True)

I know it has a lot of steps, but sometimes is better to break it down in small steps to have a better understanding.