How to create a list of random values from a dataframe column?

Question

Goal: To create a new column of random values in df2 from an existing column in df1. df1 is from the "original" excel file that I am reading, and df2 is a new dataframe I am making in python. I ultimately want three random values from the df1 column in one data cell of the df2 column. Example below.

Given this source excel file (NBA.xlsx):

df1:

Final_Game_Day	Champion	MVP	String_Example	Average_Viewership_(millions)
10/11/2020	Lakers	LeBron James	LeBron wins 4th championship; Lakers win record tying 17th	7.45
6/13/2019	Raptors	Kawhi Leonard	Toronto wins 1st championship denying Warriors 3-peat	15.14
6/8/2018	Warriors	Kevin Durant	Warriors sweep Cavs for back-to-back championships	17.56
6/12/2017	Warriors	Kevin Durant	Durant wins first after leaving OKC	20.38
6/19/2016	Cavaliers	LeBron James	Cavs win their 1st ever title	20.28
6/16/2015	Warriors	Andre Iguodala	Iguodala first 6th man to win MVP	19.94

I am looking for something similar to the Sample Values column where there are three random (or the three most common data values from the respective column in df1).

df2: | Column_Name | Sample_Values | | -------- | ------------- | | Final_Game_Day | 10/11/2020; 6/13/2019; 6/8/2018 | | Champion | Warriors; Lakers; Raptors | | MVP | LeBron James; Kevin Durant; Kawhi Leonard | | String_Example |LeBron wins 4th championship; Lakers win record tying 17th; Toronto wins 1st championship denying Warriors 3-peat; Warriors sweep Cavs for back-to-back championships | | Average_Viewership_(millions) | 7.45; 15.14; 17.56 |

Sample code to start (I think only the bottom two portions are where I need to add code to get what I want):

### Setting up
import pandas as pd
import os
import glob

###Setting working directory
path = os.getcws()
files = os.listdir(path)

### Prep to get all files
from os import listdir
from os.path import isfile, join

### Reading only excel files in folder
FileList_xlsx = [f for f in files if f[-4:] == "xlsx"]

# Initializing empty data frame
df = pd.DataFrame()

# Initializing Sample Values List for Sample Values
SampleValues = []

# Loop over list of Excel files

for f in FileList_xlsx:
    ReadXlsx = pd.read_excel(f)
    ColumnNames = list(ReadXlsx.columns.values)

    # Sets up first column list in df2
    for a in ColumnNames:
        Agg_ColumnNames.append(a)

    for a in ColumnNames:
    ### Missing code here ###

# Create final dataframe - Need Sample Values at end
final = {'Column_Name': Agg_ColumnNames, }

user_9857631 · Answer

To get the n most common values from a column, you can use the index that's returned by value_counts:

n = 3
>>> df1["MVP"].value_counts().index[:n].to_list()
['Kevin Durant', 'LeBron James', 'Andre Iguodala']

So, to get your df2, you could get the three most common values from each column in a dictionary comprehension and do a string.join. It would look like this:

df2 = pd.Series({column: "; ".join(str(s) for s in df1[column].value_counts().index[:3]) for column in df1.columns}).reset_index()
df2.columns = ["Column_Name", "Sample_Values"]
>>> df2
                     Column_Name                                      Sample_Values
0                 Final_Game_Day                    10/11/2020; 6/8/2018; 6/12/2017
1                       Champion                       Warriors; Cavaliers; Raptors
2                            MVP         Kevin Durant; LeBron James; Andre Iguodala
3                 String_Example  Toronto wins 1st championship denying Warriors...
4  Average_Viewership_(millions)                                 17.56; 20.38; 7.45

How to create a list of random values from a dataframe column?

1 Answers